Turn off SQL mode ONLY_FULL_GROUP_BY in MySQL 5.7

Scroll this

If you receive error like this:

You can fix it buy removing the ONLY_FULL_GROUP_BY option or by rewriting your queries. From MySQL 5.7. only_full_group_by comes as default. In short, with this option, columns in your select part of sql query must also be in you group by part of sql query. If in doubt check MySQL manual which has comprehensive explanation how MySQL is handling GROUP BY: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Turn ONLY_FULL_GROUP_BY off

To turn off ONLY_FULL_GROUP_BY check to value of the sql_mode variable.
Execute this query in your favorite MySQL client:

Edit MySQL config file my.conf . Usually in /etc/my.cnf or /etc/mysql/my.conf . If not sure, execute this command from your shell:

copy sql_mode values, remove ONLY_FULL_GROUP_BY and put this line in my.conf under [mysqld] section.

In my case it looks like this:

Restart the mysql server

CentOS:

Ubuntu:

References

 

6 Comments

  1. I saw a lot of solutions to ONLY_FULL_GROUP_BY but your it’s the best!
    Thank you!

  2. Thanks! Many solutions out there, but this is the only one that worked for me. Much appreciated!

Submit a comment