1

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?

Sachin
  • 1,646
  • 3
  • 22
  • 59

1 Answers1

1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    I fixed the problem by editing configuration file at `C:\wamp\bin\mysql\mysql5.7.36\my.ini`. I added `ONLY_FULL_GROUP_BY` to `sql-mode` variable. The changes are now persistent even after server reboot. – Sachin Feb 23 '23 at 14:56
  • I misunderstood your original question, I thought you were trying to remove ONLY_FULL_GROUP_BY, but you were trying to add it back in. Does WampServer come preconfigured with that mode disabled?!? That's terrible! – Bill Karwin Feb 23 '23 at 15:26
  • 1
    Oh ok! no worries Bill, its alright. Yes absolutely WampServer (even latest) comes with this specific `sql mode` disabled and that's why sometimes my `GROUP BY` queries don't run at all due to `ERROR 1055 – Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column`. But I made it working as I told you in my first comment. Your very first line helped me for thinking for the solution - `To change a configuration variable and make it persist after you restart the MySQL service, you need to edit a configuration file.` – Sachin Feb 23 '23 at 18:14