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 clauseoccurs in MySQL 5.7 and 8.0+ when aSELECTquery withGROUP BYreferences a column that is neither grouped nor wrapped in an aggregate function (such asMAX(),MIN(), orANY_VALUE()). This is enforced by theONLY_FULL_GROUP_BYSQL 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_atorusernamewhile grouping only bydepartment_idwithout 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