Getting this SQL Error: GROUP BY incompatible with sql_mode=only_full_group_by

Question

I am getting the following error:

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 have mysql 5.7.9 installed. Logging into the Control Panel is working fine, but the front end displays this message.

During the installation, the screen was stuck at the very end with "Ooops…" message.

I am new to terminal commands and mysql configurations so the more specific the better.

Thanks.

Answer 1

MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant (where in previous versions it was not).

There is a good overview of the changes here.

Unfortunately, this affects some core queries that Craft uses when fetching elements.

The good news is that this has been fixed already in Craft 3 since we refactored the way that elements query grabs data and it no longer uses a GROUP BY.

The bad news is that it's going to be pretty impractical to backport that fix into Craft 2.

The workaround for Craft 2, if you're running MySQL 5.7.5+ is to edit your my.cnf file and remove the ONLY_FULL_GROUP_BY option from sql_mode. That will change GROUP BY behavior back to its pre-MySQL 5.7.5 behavior.

Update:

If, for whatever reason (primarily you're on shared hosting), you aren't able to change the sql_mode of your box at the environment level, as of the next Craft release (post 2.6.2945), there is a new initSQLs database config settings you can set in your craft/config/db.php. It will accept an array of SQL statements to be executed immediately after the database connection has been established. If you're running into this MySQL 5.7 error, you can do:

'initSQLs' => array("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';")

Just keep in mind this method will add at least one extra SQL query to every request.

Answer 2

Updated 05/03/16: I had a struggle getting this to work with Homestead 5.2. The MySQL error everyone else is seeing, as well as mcrypt error, as mycrypt is not installed by default in Vagrant box laravel/homestead 0.4.1 (Fixed in vagrant box laravel/homestead 0.4.2 — See answer history for 0.4.1 how-to).

So, steps to success:

1. vagrant ssh into your box
2. sudo vim /etc/mysql/my.cnf
3. Scroll to the bottom of file and A to enter insert mode
4. Copy and paste [mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION to the bottom of the file
5. esc to exit input mode
6. :wq to save and close vim.
7. sudo service mysql restart to restart MySQL.
8. Done!

Original how-to article.

Answer 3

To fix this I have created a .my.cnf in my home-directory with the following content:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Answer 4

For UBUNTU 16.04 LTS

commands:

cd /etc/mysql

sudo nano my.cnf

add these two lines:

[mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

save file and reset your computer.

Answer 5

We have the same problem. MySQL version (Ver 14.14 Distrib 5.7.9, for osx10.11 (x86_64) using EditLine wrapper)

Had problems SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression and by adding the code at my.cnf file resolved it for me:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Answer 6

Here are some fairly specific instructions if you happen to be hosted on a server configured through ServerPilot.

https://serverpilot.io/community/articles/how-to-disable-strict-mode-in-mysql-5-7.html

Answer 7

MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant (where in previous versions it was not). Either modify the existing mysql queries to be compatible to the new mysql version or make mysql behave as in previous versions , by executing the below queries on the mysql .

SET sql_mode = ''; // current client

SET GLOBAL sql_mode = ''; // for other clients like hibernate

Source: http://www.proactiveclass.com/tutorials/java/hibernate/sql-error-group-by-incompatible-with-sql-mode-only-full-group-by

Answer 8

The proper fixes have already been answered here; but I'd like to note that another fix is to use MariaDB instead of MySQL. It's a drop-in replacement for it, it's more performant for a number of metrics, and it's created by the original author of MySQL.

Give it a go; you won't regret it.

Answer 9

I faced this problem when I upgraded MySQL to version >= 5.7.5 and I'v fixed it by following these steps:

1. SSH into your server.

2. Add sql-mode="" to your MySQL config:

   echo -e '[mysqld]\nsql-mode=""' | tee --append /etc/mysql/conf.d/craft.cnf > /dev/null
   

3. Restart MySQL service:

   service mysql restart
   

---

Note: you might need to add sudo to those commands.

Answer 10

Following on from Rob's answer, for anyone running into this issue on Laravel Homestead, we can use Bash to script the change. That way, the edit to the my.cnf file is done automatically for any future VMs created using vagrant up.

To do this, simply add the following to your ~/.homestead/after.sh file:

STR="sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
if ! grep -Fxq "$STR" /etc/mysql/my.cnf
then
    printf "\n# Craft 2 compatibility\n[mysqld]\n$STR" >> /etc/mysql/my.cnf
    service mysql restart
fi

The above code searches /etc/mysql/my.cnf for the line 'sql_mode=...', and if it cannot be found, it is automatically appended, together with the preceding [mysqld] to the end of the file. The mysql service is then restarted to enable the change.

Answer 11

If you're using MAMP, it will constantly complain about existence of a "global" my.cnf file.
So, you need to update MySQL config template file inside your MAMP application.

1. Start MAMP
2. Go File > Edit Template > MySQL (my.cnf) > "choose whatever version you're using"
3. Dump these two lines in the end of the opened file:

[mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Cheers!

Answer 12

Just in case somebody has problems with 'initSQLs' on ddev: I had to remove 'NO_AUTO_CREATE_USER'.

<?php
/**
 * Database Configuration
 */

return array(
    '*' => array(
        'server' => 'db',
        'user' => 'db',
        'password' => 'db',
        'database' => 'db',
        'initSQLs' => array("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';")
    ),

);

Answer 13

open terminal :

"sudo nano /etc/mysql/my.cnf"

Scroll to the bottom of file and A to enter insert mode Copy and paste

[mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

save file then restart mysql service by the below command :

"sudo service mysql restart" to restart MySQL.