MySQL Error caused by sql_mode=only_full_group_by

On December 23, 2015, in SQL, by Anuj Gakhar

If you’ve recently upgraded your MySQL server version to MySQL 5.7.5+ and you notice that some of your queries that contain GROUP BY clause, suddenly stop working, then it could be because of a change that was made in MySQL 5.7.5+.

According to this page

The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5. The NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For information about all available modes and MySQL’s default behavior, see Section 5.1.7, “Server SQL Modes”.

The ONLY_FULL_GROUP_BY mode means :-

Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

Example

To give you an example of what that means, here is a simple SQL query that will fail with the above sql mode.

SELECT first_name, IF(last_name IS NULL, 'DUMMY', last_name) as last_name from users GROUP BY first_name

This query will give you the following error: –

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘db_name.users.last_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Solution

If you are like me and have used Homebrew to install/upgrade Mysql, then you can do the following steps to fix the issue.

  1. Copy the default my-default.cnf to /etc/my.cnf

     sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf
    
  2. Change sql_mode in my.cnf using your favorite editor and set it to this :-
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Setting it to blank (”) might work as well but I have not tried that.
  3. Restart MySQL server.

Now your queries will start working again. More details on MySQL GROUP BY handling can be found here.

 

5 Responses to MySQL Error caused by sql_mode=only_full_group_by

  1. Yusuf says:

    Thanks Anuj

  2. netnou says:

    sudo cp $(brew –prefix mysql)/support-files/my-default.cnf /etc/my.cnf

    remote: Counting objects: 353405, done.
    remote: Total 353405 (delta 0), reused 0 (delta 0), pack-reused 353405
    Receiving objects: 100% (353405/353405), 67.67 MiB | 1.28 MiB/s, done.
    Resolving deltas: 100% (267220/267220), done.
    From https://github.com/Homebrew/linuxbrew
    * [new branch] master -> origin/master
    cp: target ‘/etc/my.cnf’ is not a directory

  3. Bo says:

    Hi Anuj, does it possible to modify the sql request instead of changing the sql mode ?

Leave a Reply

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 445 other subscribers

© 2011 Anuj Gakhar
%d bloggers like this: