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.
-
Copy the default my-default.cnf to /etc/my.cnf
[php]
sudo cp $(brew –prefix mysql)/support-files/my-default.cnf /etc/my.cnf
[/php]Change sql_mode in my.cnf using your favorite editor and set it to this :-[sql]sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION[/sql]Setting 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.
Thanks Anuj
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
it’s next problem, can you help me please ?
Hi Anuj, does it possible to modify the sql request instead of changing the sql mode ?
What do you mean? You want to change the SQL query to fix the problem? I don’t know of a way to do that, sorry!