Error 1055: MySQL 5.7 Rejected the GROUP BY Query That Worked for Years

A client moved a PHP application off a dying server onto a fresh Ubuntu box, which meant MySQL 5.7 instead of 5.6. The application loaded, people logged in, orders saved. Then the sales manager opened the monthly report and got a white page, and the log had this in it:

ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'shop.o.status' which is not functionally dependent on columns in
GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Everyone's first reaction is that MySQL 5.7 broke a working query. I want to argue the opposite, because it changes how you fix it: the query was always wrong, and 5.6 was answering it with a value picked at random. Nobody noticed because for years the random value happened to be plausible. The upgrade did not introduce a bug, it revealed one, and the report you have been emailing to management every month has been subtly fiction.

What the error is telling you

Take the query behind that report:

SELECT o.customer_id, MAX(o.created_at) AS last_order, o.status, COUNT(*) AS orders
FROM orders o
GROUP BY o.customer_id;

One row comes back per customer. COUNT(*) and MAX(created_at) are well defined for a group. o.status is not: a customer with forty orders has forty statuses, and the query does not say which one it wants. MySQL 5.6 with a permissive sql_mode returned one of them, chosen by whatever the execution plan happened to touch. Change an index and the number changes, silently.

MySQL 5.7 turned ONLY_FULL_GROUP_BY on in the default sql_mode, along with STRICT_TRANS_TABLES, NO_ZERO_DATE and a few others. Check what you are running:

SELECT @@GLOBAL.sql_mode\G

@@GLOBAL.sql_mode: 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

One useful subtlety: 5.7 also detects functional dependency. If you group by a primary key, the server knows every other column of that table is determined by it, so this is accepted without complaint:

SELECT o.id, o.status, o.total, COUNT(i.id) AS lines
FROM orders o JOIN order_items i ON i.order_id = o.id
GROUP BY o.id;

So the rule is not "list every column in GROUP BY". It is "make the value unambiguous", and grouping by a key is one way of doing that.

Fix 1: say what you actually meant

Nine times out of ten the intent is "the status of that customer's most recent order". That is a different query, and it was never expressible as a plain GROUP BY:

SELECT o.customer_id, o.created_at AS last_order, o.status, agg.orders
FROM orders o
JOIN (
    SELECT customer_id, MAX(created_at) AS last_order, COUNT(*) AS orders
    FROM orders
    GROUP BY customer_id
) agg ON agg.customer_id = o.customer_id AND agg.last_order = o.created_at;

Now the status comes from a specific row that you named. On MySQL 8.0, which arrived this year, window functions express the same thing more directly with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC). On 5.7 the derived table is the way.

Fix 2: ANY_VALUE, where it is honest

Sometimes the column really cannot vary inside the group. Joining orders to customers and grouping by c.id makes c.name constant per group, but if the optimiser cannot prove that, 5.7 still refuses. Tell it you accept any value:

SELECT c.id, ANY_VALUE(c.name) AS name, COUNT(o.id) AS orders
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

ANY_VALUE() is a promise you are making to the server. Use it where the promise is true. Wrapping every column in it to make errors go away is ONLY_FULL_GROUP_BY disabled with extra typing, and worse, it hides which columns were the doubtful ones.

Fix 3: relax the mode for one session, with a date attached

When you have inherited four hundred reports and a deadline on Friday, scope the relaxation to the connection that runs the legacy reports and leave the rest of the application strict:

SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

Building the new value from @@sql_mode rather than typing a literal matters. Assigning a hand written list is how people accidentally drop STRICT_TRANS_TABLES as well and start silently truncating data, which is a much more expensive bug than a broken report.

Fix 4: the global switch, and its price

On Ubuntu, in /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

sudo systemctl restart mysql

Note what that line does: it removes exactly one mode and keeps the rest. Every "solution" on the internet that sets sql_mode = "" also disables strict mode, which turns an over-long string into a truncated string instead of an error, and a bad date into 0000-00-00. That is not a trade you want in exchange for a report.

If you do this, write the date you did it in a comment above the line, and the date you intend to remove it. Otherwise it is permanent, and the next engineer will inherit a database that quietly answers ambiguous questions.

Finding the rest of them before your users do

One report failed on the day of the upgrade. There are almost certainly more, sitting in month end code that nobody runs until the last Friday. Do not wait for them.

Turn ONLY_FULL_GROUP_BY back on globally on a copy of production, then exercise the application against it and collect the errors in one pass:

sudo grep -c '1055' /var/log/mysql/error.log
sudo tail -f /var/log/mysql/error.log | grep --line-buffered 'only_full_group_by'

If your application logs its own SQL failures, that log is better still because it names the code path. Where the queries live in files rather than in a framework, a crude grep finds most of the candidates quickly:

grep -rniE 'group by' --include='*.php' --include='*.sql' . | wc -l
grep -rniE 'select .*,.* from .* group by' --include='*.php' . | head -n 40

That will over-report, and it is still the fastest way to build a list. Sort the results by how much money the query touches and start at the top. In the codebase behind this article there were thirty-one GROUP BY queries, nine of them ambiguous, and two of the nine were on invoices.

Verification

SELECT @@SESSION.sql_mode\G

Then run the report and compare the numbers against the old server, not just against an absence of errors. This is the step people skip, and it is the whole point: on any query that was ambiguous, the old output could have been wrong. I have found a monthly commission calculation off by one status category this way, going back two years.

What people get wrong

Treating it as a MySQL bug. The behaviour matches the SQL standard, and every other serious database has always rejected these queries. PostgreSQL users reading this have never seen the error because they were never allowed to write it.

Setting sql_mode to an empty string. Covered above. It disables strict mode too, and silent data truncation is far more damaging than a report that errors.

Developing on MariaDB and deploying on MySQL. MariaDB does not enable ONLY_FULL_GROUP_BY by default, so the query runs on your laptop and fails in production. If your stack is MariaDB, as most of the Frappe work I do is, that is fine, but make the mode part of the environment you match rather than a difference you discover at deploy time.

Fixing the error without checking the numbers. Adding ANY_VALUE() makes the report run again with the same arbitrary value it had before. That is a legitimate choice only once you have confirmed the value cannot vary.

When it is still broken

• The error names a column you cannot find in the SELECT. Read the expression number in the message. ORDER BY and HAVING clauses trigger the same rule, and the offending column is often in an ORDER BY nobody has looked at in years.
• Your framework builds the query. Turn on the query log briefly, capture the generated SQL, and fix it at the level that generates it, not in the database configuration.
• You changed my.cnf and nothing changed. You edited a file MySQL does not read, or the setting is overridden later. SELECT @@GLOBAL.sql_mode is the only authority, and connections opened before the restart keep their old session value.
• New errors appeared after you fixed this one. Expect them. Strict mode and the zero date modes come from the same upgrade, and they surface bad inserts that 5.6 accepted. Fix the data, not the mode.