MySQL Error 1055: ONLY_FULL_GROUP_BY: something is not in GROUP BY clause ...

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

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?

  1. fix the offending SQL queries, or get their authors to do that.
  2. roll back to a version of MySQL compatible out-of-the-box with the application software you use.
  3. change your server's sql_mode to get rid of the newly set ONLY_FULL_GROUP_BY mode.

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.



Got any MySQL Question?