Dreaded GROUP_BY issue on mySQL 5.7+

Question

I'm having that dreaded GROUP_BY issue on a website. I suspect the server auto-updated itself or something as the site was fine before.

Now it's running on mySQL 5.7.10. The site wont load. I get the usual:

Internal Server Error CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #18 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'creighton_mma_craft.structureelements.root' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I followed the instructions I have used before (adding the settings into my.cnf to return the group by settings to previous behavior).

It works for a few mins, then the whole of mySQL dies. I can no longer access it at all. I can only bring it back up by removing the line and restarting mySQL. I have tried several times and get the same result each time.

WHAT ON EARTH IS HAPPENING??

Answer 1

Looking at other issues, there are a few options to handle the GROUP BY issue on MySQL 5.6 > 5.7

• Alter the query by using GROUP_CONCAT
• Alter the SQL modes

Changing the SQL modes gives you a timeout, but these modes were given in a configuration file. Have you tried it by using an "extra" query?

I.E. session/global:

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

This resets the SQL_MODE to ignore ONLY_FULL_GROUP_BY for every session, in a different way.

Let me know if this changes anything :)