MySQL: Solve sql_mode=only_full_group_by Error by Turning off Strict SQL Mode Tips, Tricks and Tutorials 26 OCT 2017

When moving an older MySQL-based project to a new server instance, one of the SQL query errors bound to immediately strike is that of triggering the sql_mode=only_full_group error, caused by your SQL select statements referring to columns that are not functionally dependent on the GROUP BY column. (Best explained here in the MySQL documentation)

This new, “Strict SQL Mode” (which is actually a collection of enabled “Server SQL Modes”) is now switched on by default for any MySQL Server 5.7 and up installation, and because in the real world refactoring an entire project just to suit these new settings isn’t really an option, to resolve the issue you need to “disable” this new default on your server instance.

The two newly enabled SQL mode setting that we want to target (because they are the main culprits here) are STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY.

To do this, you first need to create an additional configuration file for your MySQL server instance:

sudo nano /etc/mysql/conf.d/disable_strict_mode.cnf

Add these two lines to the blank file and then save your changes.

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

As you can see, essentially what we are doing is just declaring a new SQL mode with our two culprit modes omitted.

Finally, restart the MySQL server with:

sudo service mysql restart

Try running your fault trigger SQL statement again, and you should now no longer get this error message staring back at you any more:

Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yourdbname.tblname.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Related Link: MySQL Group By Handling | Server SQL Modes

Related Posts:

About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don’t have time for myself any more.