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

<ul>
<li>Alter the query by using GROUP_CONCAT</li>
<li>Alter the SQL modes</li>
</ul>

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

<p>I.E. session/global:</p>

<p><code>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';</code></p>

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

<p>Let me know if this changes anything :)</p>

CC BY-SA 3.0
2016-01-11T15:09:03.650Z
2016-01-11T15:09:03.650Z
chris
<p>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.</p>

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

<pre><code>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
</code></pre>

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

<p>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.</p>

<p>WHAT ON EARTH IS HAPPENING??</p>

CC BY-SA 3.0
2016-01-11T03:01:58.030Z
You say you've restarted MySQL... Have you also restarted Apache?
Lindsey D
CC BY-SA 3.0
2016-01-11T05:04:50.937Z
Do you have access to MySQL error logs to see why it's crashing?
Brad Bell
<mysql>
Dreaded GROUP_BY issue on mySQL 5.7+
# Dreaded GROUP_BY issue on mySQL 5.7+

(Asked by: username_0 on 2016-01-11)

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

**Tags:** mysql
> **Comments:**
> **username_1**: You say you've restarted MySQL... Have you also restarted Apache?  
> **username_2**: Do you have access to MySQL error logs to see why it's crashing?  

---

## 1 answers

### Answer by username_3 on 2016-01-11. Score: 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 :)


---

