In my WampServer 3.2.6 (64-bit)
, in phpMyadmin 4.9.7
, under Variables
tab, when I change sql mode
value from:
STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
to
STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,ONLY_FULL_GROUP_BY
The value ONLY_FULL_GROUP_BY
is lost when I restart Wamp
I need to have this value every time I write some query which requires GROUP BY
clause in MySQL
Why does it happen so and how to fix this annoying issue?
To change a configuration variable and make it persist after you restart the MySQL service, you need to edit a configuration file. The values in the configuration file are read when the MySQL service starts up.
I don't use WampServer so I can't say where it stores the MySQL configuration file. This might help: Where is the ini file for wamp server for MYSQL Workbench integration?
It's good to keep the ONLY_FULL_GROUP_BY
SQL mode enabled. They made it the default in MySQL 5.7 to enforce that mode and they were correct to do so. Without it, your queries may give arbitrary results. You can and should write queries with GROUP BY that work when that mode is in effect. Read https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html for details on this.