How to keep mysql 5.6 GROUP BY rules on 8.0?

Question

With mysql 8.0 the GROUP BY rules got stricter. Is there a way to upgrade to mysql 8.0 without needing to rewrite the queries?

Some ideas in [this old SO post](https://stackoverflow.com/q/23921117/4003419) — LukStorms, Jan 10 '22 at 14:09
IMO, you should rewrite your queries. The permissive behavior of GROUP BY in old MySQL versions caused invalid results. The new behavior is a good thing. — Bill Karwin, Jan 10 '22 at 15:59

score 1 · Accepted Answer · answered Jan 10 '22 at 14:11

In my.cnf in the section [mysqld] add the following:

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

And restart MySQL. It will permanently disable the ONLY_FULL_GROUP_BY feature of MySQL 8.x

1 Answers1