Queries incompatible with ONLY_FULL_GROUP_BY

We were getting errors from Lucee about some of our SQL queries, and I thought it would be useful to note how I fixed it.

MySQL 8.4

Error: this is incompatible with sql_mode=only_full_group_by

Solution: remove ONLY_FULL_GROUP_BY from the sql_mode setting.

I did SELECT @@sql_mode to get the existing string, copied it to Notepad and then removed ONLY_FULL_GROUP_BY from the list.

I then added a new command to the bottom of my.ini:

sql_mode=”STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION”

(This is ours, yours may be slightly different – the important thing is to remove ONLY_FULL_GROUP_BY).

Then, restart MySQL.