Fixing the MySQL 'only_full_group_by' Error in GROUP BY Queries

The 2 AM Production Headache

It usually hits right after a routine database migration or a server upgrade. One minute your application is running smoothly; the next, your reporting dashboards are spitting out cryptic SQL errors. You're likely staring at a wall of text claiming your GROUP BY query is illegal. This happens because modern MySQL versions enable strict mode by default—specifically the ONLY_FULL_GROUP_BY setting.

The Exact Error Message

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

Why This Error Occurs

Back in MySQL 5.6 and earlier, the engine was far more relaxed. You could select any column, even if it wasn't in your GROUP BY clause. MySQL would simply grab a value from the first row it encountered in that group. While convenient, this was non-deterministic. It technically violated standard SQL rules because the database was essentially guessing which value you wanted.

Starting with MySQL 5.7 and continuing through 8.0, ONLY_FULL_GROUP_BY is active by default. Now, the rules are strict: every column in your SELECT list must either be in the GROUP BY clause or wrapped in an aggregate function like COUNT(), MAX(), SUM(), or AVG().

Solution 1: Rewrite the Query (The "Correct" Way)

Fixing the code is the most stable long-term strategy. It ensures your queries remain predictable and compliant with SQL standards. If you need a column that isn't part of the grouping logic, you have two primary options.

Option A: Add columns to GROUP BY

If you select id and name but only group by name, include id in the group. This tells MySQL explicitly how to handle the data.

-- This fails:
SELECT id, name, COUNT(*) FROM products GROUP BY name;

-- This works:
SELECT id, name, COUNT(*) FROM products GROUP BY name, id;

Option B: Use ANY_VALUE()

Sometimes you don't actually care which id gets returned. Perhaps you know they are identical for that group, or any representative value will do. Use the ANY_VALUE() function to signal your intent to MySQL.

SELECT ANY_VALUE(id), name, COUNT(*) 
FROM products 
GROUP BY name;

Solution 2: Disable ONLY_FULL_GROUP_BY for the Session

Maybe you need an immediate fix to restore service and can't refactor dozens of queries instantly. You can disable the mode for just your current connection. This is perfect for testing or running legacy scripts without changing global settings.

-- Check your current modes first
SELECT @@sql_mode;

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

Solution 3: Disable ONLY_FULL_GROUP_BY Globally (Permanent Fix)

Managing a massive legacy codebase? You might need to disable this check at the server level. Warning: This change affects every database on the instance, which could hide legitimate query bugs elsewhere.

Step 1: Locate your MySQL configuration file

On most Linux distributions, you'll find the config here:

/etc/mysql/my.cnf (General)
/etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu/Debian)
/etc/my.cnf (CentOS/RHEL)

Step 2: Edit the configuration

Open the file with root privileges: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf. Find the [mysqld] section and add or modify the sql_mode line. You want to include the default modes except the one causing trouble.

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

Step 3: Restart MySQL

Reload the service to apply your changes:

sudo systemctl restart mysql

Verification: How to Confirm the Fix

Never assume a config change worked. Run this command in your MySQL terminal to verify the global state:

SELECT @@GLOBAL.sql_mode;

Confirm that ONLY_FULL_GROUP_BY is missing from the list. If it's gone, your legacy queries will execute without the 1055 error.

Practical Tips for Production

Query Refactoring is Future-Proof: Disabling the mode is a band-aid. Using ANY_VALUE() or proper aggregation makes your app compatible with Postgres, SQL Server, and future MySQL versions.
Laravel Users: Laravel's Eloquent ORM often sets its own session modes. Check config/database.php for 'strict' => true. Setting this to false often resolves the issue without touching server configs.
Safety First: Always backup my.cnf before editing. A single stray character can prevent your database from booting up, turning a minor error into a major outage.