[Fixed] ERROR 1055 (42000): Expression not in GROUP BY clause (ONLY_FULL_GROUP_BY) in MySQL: Step-by-Step Troubleshooting Guide

Quick Summary: The error ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ... which is not functionally dependent on columns in GROUP BY clause occurs in MySQL 5.7 and 8.0+ when a SELECT query with GROUP BY references a column that is neither grouped nor wrapped in an aggregate function (such as MAX(), MIN(), or ANY_VALUE()). This is enforced by the ONLY_FULL_GROUP_BY SQL mode, which is enabled by default in modern MySQL versions.

Understanding the Root Causes

In older versions of MySQL (5.6 and earlier), queries could select arbitrary non-grouped columns and MySQL would silently return a nondeterministic value from one of the rows in each group. Starting with MySQL 5.7.5, ONLY_FULL_GROUP_BY was added to the default sql_mode to align with the SQL-99 standard. The error occurs due to:

  • Upgrading to MySQL 5.7 / 8.0 or Docker Images: Legacy queries that worked in older local environments fail immediately when migrated to newer MySQL or Docker containers with strict defaults.
  • Non-Aggregated SELECT Columns: Selecting columns like created_at or username while grouping only by department_id without an aggregate function.
  • Missing Functional Dependency: Grouping by a non-unique column when MySQL cannot mathematically prove that the selected column has a single unique value per group.

Step 1: Quick Fix – Rewrite the Query with ANY_VALUE() or Aggregates

The cleanest and most portable fix—requiring no server-wide configuration changes—is to wrap non-grouped columns in ANY_VALUE() or a deterministic aggregate function like MAX() or MIN():

-- Failing query:
SELECT user_id, username, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

-- Fix Option A: Use ANY_VALUE() when any matching value in the group is acceptable
SELECT user_id, ANY_VALUE(username) AS username, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

-- Fix Option B: Use an explicit aggregate function like MAX() or GROUP_CONCAT()
SELECT user_id, MAX(username) AS username, COUNT(*) AS order_count
FROM orders
GROUP BY user_id;

-- Fix Option C: Include the column in the GROUP BY clause (if logically distinct)
SELECT user_id, username, COUNT(*) AS order_count
FROM orders
GROUP BY user_id, username;

Step 2: Temporarily Disable ONLY_FULL_GROUP_BY for Legacy Sessions

If you are running legacy reporting scripts or third-party tools that cannot be rewritten immediately, inspect your current sql_mode and remove ONLY_FULL_GROUP_BY at the session or global runtime level:

-- 1. Check the current active sql_mode
SELECT @@sql_mode;

-- Example output:
-- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

-- 2. Disable ONLY_FULL_GROUP_BY for the current session only
SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

-- 3. Disable globally until the next MySQL server restart
SET GLOBAL sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

Step 3: Persist the sql_mode Configuration in my.cnf or Docker

Runtime SET GLOBAL changes reset whenever MySQL restarts. To persist the modified sql_mode permanently on Linux or in Docker Compose, update your MySQL configuration file:

# On Linux (Ubuntu/Debian): edit /etc/mysql/mysql.conf.d/mysqld.cnf
# On RHEL/CentOS: edit /etc/my.cnf
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Add under the [mysqld] section (keep all other strict flags intact):
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

# Restart MySQL to apply the configuration
sudo systemctl restart mysql

# For Docker / docker-compose.yml, pass the flag via command:
# services:
#   db:
#     image: mysql:8.0
#     command: --sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Verification & Testing Steps

Verify the updated sql_mode setting and re-run your grouped query to confirm it executes without error:

-- 1. Confirm ONLY_FULL_GROUP_BY is no longer present (if disabled)
SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

-- 2. Run the grouped query to confirm clean execution
SELECT user_id, ANY_VALUE(username) AS username, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
LIMIT 10;

Troubleshooting Summary Comparison

Approach Scope Best Use Case
ANY_VALUE(col) / MAX(col) Single SQL query Modern apps; keeps full SQL-99 strictness enabled
Add column to GROUP BY Single SQL query When each unique combination should be its own row
SET SESSION sql_mode Current DB connection Quick one-off legacy script execution
sql_mode in my.cnf / Docker Server-wide permanent Migrating legacy MySQL 5.6 apps to MySQL 8.0

Leave a Reply