Fix MySQL 1055: only_full_group_by error in 8.0

If you're on MySQL 8.0 and seeing ERROR 1055 (42000): Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column ... which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, you're not alone. This hits everyone eventually, usually right after an upgrade from 5.7 or when moving to a managed service. The fix is rarely a one-liner — you need to understand why MySQL is being picky and then choose the right solution for your situation.

I've fixed this dozens of times, and the cause is almost always one of three things. Let's start with the most common, then work down.

Cause 1: Strict sql_mode is on and your query violates it

This is the default in MySQL 8.0. The server enforces ONLY_FULL_GROUP_BY, which means every column in your SELECT list that isn't inside an aggregate function (like SUM(), MAX(), etc.) must appear in the GROUP BY clause. No exceptions. If you're selecting users.id, users.name, and COUNT(orders.id) but only grouping by users.id, MySQL will scream.

Here's a typical failing query:

SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

The problem? u.name isn't in the GROUP BY and isn't functionally dependent on u.id. In most real-world schemas, name depends on id, but MySQL can't always prove that without a primary key or unique constraint. Even then, it doesn't trust it fully.

The fix: You have two options here. The first is to disable ONLY_FULL_GROUP_BY in your session or globally. That's a quick patch, but it can hide deeper issues in your queries.

-- For the current session only
SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

-- Globally (requires SUPER privilege, affects all new connections)
SET GLOBAL sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

If you want it permanent, add it to your my.cnf or my.ini under [mysqld]:

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

But honestly, I'd recommend against this unless you have a legacy app that you can't refactor. The better route is to fix the query itself.

Rewrite the query properly

The cleanest solution is to include all non-aggregated columns in the GROUP BY. In our example, just add u.name:

SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

That works, but if you have ten columns, it gets unwieldy. Another approach is to use a subquery to pre-aggregate, then join back. This keeps your SELECT list clean and is often faster because it reduces the rows being grouped.

SELECT u.id, u.name, t.order_count
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) t ON t.user_id = u.id;

This is my go-to when the query is even mildly complex. It's explicit and avoids the MySQL optimizer guessing.

Cause 2: You're grouping by a primary key but selecting other columns

MySQL 8.0 tries to be smart about functional dependency. If you group by a primary key or a unique not-null column, it should allow other columns from that table. But it fails in certain edge cases — especially when you're using JOINs or if the column isn't directly from the grouped table.

For example:

SELECT u.id, u.email, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

Here, u.email is functionally dependent on u.id, so it should be fine. But o.total isn't — it belongs to the orders table, and multiple orders per user means multiple totals. MySQL will throw 1055.

The fix: If you're sure you want an arbitrary value from the grouped rows, use ANY_VALUE(). It tells MySQL to pick any value from that group, effectively suppressing the error.

SELECT u.id, u.email, ANY_VALUE(o.total) AS sample_total
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

But be careful — ANY_VALUE() doesn't guarantee which row it picks. If you need a specific one, like the latest order, you're better off with a subquery that orders by date:

SELECT u.id, u.email, latest.total
FROM users u
LEFT JOIN (
    SELECT o.user_id, o.total
    FROM orders o
    JOIN (
        SELECT user_id, MAX(created_at) AS max_created
        FROM orders
        GROUP BY user_id
    ) m ON m.user_id = o.user_id AND m.max_created = o.created_at
) latest ON latest.user_id = u.id;

That's more work, but it gives you exactly what you want. Avoid ANY_VALUE() if the value matters — it's a trap that leads to inconsistent results.

Cause 3: The query uses DISTINCT or UNION with GROUP BY

Sometimes the error isn't in the main query but in a subquery inside a UNION or when DISTINCT is combined with aggregation. MySQL's optimizer can get confused about which columns belong to which scope.

For instance:

SELECT DISTINCT user_id, COUNT(*) AS cnt
FROM orders
GROUP BY user_id;

This is redundant — GROUP BY already makes rows unique, so DISTINCT adds nothing. But MySQL might still complain depending on the version and the rest of the query.

Another common case is a UNION where one part has a GROUP BY and the other doesn't, or where columns in the SELECT list don't match the GROUP BY of the individual SELECT.

The fix: Remove DISTINCT when you're already grouping. For UNION, make sure each SELECT inside complies with ONLY_FULL_GROUP_BY individually. If you're unioning aggregated and non-aggregated data, you might need to split them into separate queries and combine in your application layer, or use a temporary table.

-- Bad: DISTINCT with GROUP BY
SELECT DISTINCT user_id, COUNT(*) FROM orders GROUP BY user_id;

-- Good: Just GROUP BY
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

If you inherit a query like this, don't just yank the DISTINCT — verify the logic first. Sometimes people add DISTINCT because they saw duplicate rows, and the real issue is a bad JOIN.

Quick reference table

One last tip: if you're using an ORM like Hibernate or Laravel's query builder, you might not even see the raw SQL. Turn on query logging to see exactly what's being generated. The error message gives you the column and expression number, so you can pinpoint the offender fast.

Remember, disabling ONLY_FULL_GROUP_BY is like turning off a safety feature. Do it only when you understand the risk. Otherwise, take the ten minutes to rewrite the query properly — future you will thank you.