1

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??

2
  • You say you've restarted MySQL... Have you also restarted Apache? – Lindsey D Jan 11 '16 at 3:01
  • Do you have access to MySQL error logs to see why it's crashing? – Brad Bell Jan 11 '16 at 5:04
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 :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.