How to Fix MySQL SQL Mode Errors (ONLY_FULL_GROUP_BY)

In this tutorial, you'll learn about How to Fix MySQL SQL Mode Errors (ONLY_FULL_GROUP_BY). We cover key concepts, practical examples, and best practices.

The Problem

You run a SELECT query with GROUP BY and get Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column. MySQL's ONLY_FULL_GROUP_BY SQL mode (enabled by default in MySQL 5.7+) requires all non-aggregated columns in the SELECT list to appear in the GROUP BY clause. Existing code that worked on MySQL 5.6 may break after upgrading to 5.7 or 8.0.

Quick Fix

1. Check the current SQL mode

mysql -e "SELECT @@sql_mode;"

Expected output:

@@sql_mode
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

2. Fix the query to comply with ONLY_FULL_GROUP_BY

-- Bad: non-aggregated column not in GROUP BY
SELECT name, COUNT(*) FROM users GROUP BY department;

-- Good: add all selected columns to GROUP BY
SELECT name, COUNT(*) FROM users GROUP BY name, department;

-- Or use ANY_VALUE() for columns that are functionally dependent
SELECT ANY_VALUE(name) as name, department, COUNT(*)
FROM users GROUP BY department;

3. Disable ONLY_FULL_GROUP_BY for the current session

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

4. Disable ONLY_FULL_GROUP_BY globally

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

5. Disable in MySQL configuration file

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

Add this to /etc/mysql/my.cnf or /etc/my.cnf, then restart:

sudo systemctl restart mysql

6. Verify the change

mysql -e "SELECT @@sql_mode;"

7. Use GROUP_CONCAT for comma-separated results

-- Instead of selecting non-aggregated columns
SELECT department, GROUP_CONCAT(name) as names
FROM users GROUP BY department;

Common Causes

Additional Troubleshooting

# Check the error message and stack trace for more context
echo "Review the full error output to identify the root cause"

If the above steps do not resolve the issue, examine the complete error message and stack trace. Often the key detail is in the middle of the traceback rather than the final line. Search for the error message in the project documentation or issue tracker for additional solutions.

Prevention

• Always include all non-aggregated SELECT columns in your GROUP BY clause
• Use ANY_VALUE() for columns whose values are functionally dependent on the GROUP BY column
• Test queries with ONLY_FULL_GROUP_BY enabled during development, even if you disable it in production
• Review server upgrades carefully — MySQL 5.7 and 8.0 both enable stricter sql modes by default than previous versions
• Create a database migration checklist that includes reviewing sql_mode compatibility before upgrading
• Run SELECT @@sql_mode; after any MySQL configuration change to verify the active sql_mode matches your expectations