When MySQL 5.7 was released, they changed default SQL_MODE settings. This affected a lot of existing database setups, effectively breaking a myriad of SQL queries.
The default SQL_MODE in 5.7 includes:
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION.
In this article, we'll focus on the breaking changes caused by
ONLY_FULL_GROUP_BY, one of the most visible changes in MySQL 5.7's default behaviour.
This mode is one of the more common trip ups after upgrading. It can cause existing queries that use
GROUP BY to error out.
What is this mode doing?
When a SQL statement is grouped using
GROUP BY, the results are grouped by whichever columns are passed to
GROUP BY. So if a statement is grouped by, let's say
name, it may return, for example, two different groups of rows because there are only two different values for
name for all the records in the table.
i.e. There are 20 rows in a table, and say 11 have the value of 'Bob' for
name and the other 9 have the value of 'Barbara' for
Any leftover columns you wish to return in your
SELECT statement are now used to determine which record in each group qualifies to be returned. You may have 11 rows in a group where name = 'Bob', but which one of these rows is the one returned in the result set?
This is where it errors out, because it doesn't know what you want it to do, and it refuses to come up with an arbitrary solution.
These leftover columns are considered:
- Nonaggregated: Because they aren't used in the
- Nondeterministic: Because MySQL is unable to determine how to use these columns to select the one row from each group to return.
Let's take a sample query and see what happens. Here's the query:
SELECT name, address, MAX(age) FROM t GROUP BY name;
ONLY_FULL_GROUP_BY enabled, it errors out with this:
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.t.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
The possible solutions are as follows.
- Ensure all nonaggregated columns are functionally dependent on
- Refer to nonaggregated columns using
ONLY_FULL_GROUP_BYeither for the session, or globally.
Solution 1: Functional dependence on GROUP BY
MAX(age) is functionally dependent on the
GROUP_BY statement because for each group of rows — in this case grouped by
name — it selects the row with the highest (maximum)
age value. The
address column, however, is not functionally dependent on any column named in the
GROUP_BY statement. This will cause such an error.
So if you can figure out how to apply
address to one of these aggregate functions, as described below, it would solve the problem.
Other examples of functions that cause a nonaggregated column to become functionally dependent on
GROUP BY columns:
MAX(), MIN(), SUM(), AVG() etc. These functions take all the rows in each group and apply their given intent to determine which row, or which calculated value to return in the row of the result set representing each group.
Visit this page for a list of all aggregate functions that would apply to this solution.
Solution 2: Use ANY_VALUE()
ANY_VALUE() to refer to the
address column. This column is nondeterministic which causes problems when it is grouped because MySQL can't determine which of these grouped rows takes precedence if it doesn't know which row to choose based on the
address column that is in your
ANY_VALUE(), you're basically telling MySQL that you don't care which value of the passed nonaggregated column (in this case
address) is chosen for each group.
Solution 3: Disable ONLY_FULL_GROUP_BY
You can disable ONLY_FULL_GROUP_BY by resetting
SQL_MODE without it, either for the session or globally.
The following sets it only for the current session.
SET SESSION SQL_MODE='STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION';
The following sets it globally, across all future sessions, until MySQL restarts. If you want this change to persist across any MySQL restart, modify your
SET GLOBAL SQL_MODE='STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION';
What About The Other Modes?
As it turns out, in certain minor versions of 5.7 updates, some of these modes were deprecated. Then some were built into strict mode.
Well, it's a bit unclear, but in 5.7.4, they deprecated some modes, then in 5.7.8 they reintroduced them with a warning. Suffice it to say that
ONLY_FULL_GROUP_BY is the most commonly visible breaking change when going from < 5.7 to 5.7.x.