MySQL Error 1055: ONLY_FULL_GROUP_BY: something is not in GROUP BY clause ...
Introduction
Recently, new versions of MySQL servers have begun to generate 1055 errors for queries that used to work. This topic explains those errors. The MySQL team has been working to retire the nonstandard extension to
GROUP BY, or at least to make it harder for query writing developers to be burned by it.
Remarks
For a long time now, MySQL has contained a notorious nonstandard extension to GROUP BY, which allows oddball behavior in the name of efficiency. This extension has allowed countless developers around the world to use GROUP BY in production code without completely understanding what they were doing.
In particular, it's a bad idea to use SELECT * in a GROUP BY query, because a standard GROUP BY clause requires enumerating the columns. Many developers have, unfortunately, done that.
Read this. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
The MySQL team has been trying to fix this misfeature without messing up production code. They added a sql_mode flag in 5.7.5 named ONLY_FULL_GROUP_BY to compel standard behavior. In a recent release, they turned on that flag by default. When you upgraded your local MySQL to 5.7.14, the flag got switched on and your production code, dependent on the old extension, stopped working.
If you've recently started getting 1055 errors, what are your choices?
- fix the offending SQL queries, or get their authors to do that.
- roll back to a version of MySQL compatible out-of-the-box with the application software you use.
- change your server's
sql_modeto get rid of the newly setONLY_FULL_GROUP_BYmode.
You can change the mode by doing a SET command.
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
should do the trick if you do it right after your application connects to MySQL.
Or, you can find the init file in your MySQL installation, locate the sql_mode= line, and change it to omit ONLY_FULL_GROUP_BY, and restart your server.
