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.
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
If you are like me and have used Homebrew to install/upgrade Mysql, then you can do the following steps to fix the issue.
Copy the default my-default.cnf to /etc/my.cnf
sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnfChange 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_SUBSTITUTIONSetting it to blank (”) might work as well but I have not tried that.
- Restart MySQL server.
Now your queries will start working again. More details on MySQL GROUP BY handling can be found here.