Introduction

ONLY_FULL_GROUP_BY is a MySQL SQL mode that rejects ambiguous GROUP BY queries. You can disable it, but in most cases the better fix is to rewrite the query so every selected column is either grouped, aggregated, or functionally dependent on the grouped columns.

Why This Mode Exists

Consider a table of employees with many rows per department. This query is ambiguous:

SELECT department_id, employee_name
FROM employee
GROUP BY department_id;

MySQL has to return one employee_name for each department, but the query never told it which one. Older MySQL behavior often returned an arbitrary row value, which is exactly what ONLY_FULL_GROUP_BY tries to prevent.

With the mode enabled, MySQL forces you to make the query explicit.

The Better Fix: Rewrite the Query

Most of the time, you should fix the query instead of disabling the mode.

If you wanted a count per department:

SELECT department_id, COUNT(*) AS employee_count
FROM employee
GROUP BY department_id;

If you wanted the maximum salary per department:

SELECT department_id, MAX(salary) AS max_salary
FROM employee
GROUP BY department_id;

If you truly need one representative non-grouped value, you usually need a more explicit query pattern such as a join, a subquery, or ANY_VALUE with care.

Using ANY_VALUE

MySQL provides ANY_VALUE() for cases where you genuinely do not care which value is chosen:

SELECT department_id, ANY_VALUE(employee_name) AS sample_employee
FROM employee
GROUP BY department_id;

This is valid SQL in MySQL, but it should be used carefully. It does not make the result meaningful; it only makes the ambiguity explicit.

How to Disable It Temporarily

If you still need to disable the mode for a session, you can remove it from sql_mode:

SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY', '');

This affects only the current connection.

If you want to disable it globally until the server restarts:

SET GLOBAL sql_mode = REPLACE(@@GLOBAL.sql_mode, 'ONLY_FULL_GROUP_BY', '');

Be careful with GLOBAL. Existing sessions may keep their current modes, and future connections will inherit the changed global setting.

Making It Persistent

For a persistent change, update the MySQL configuration file and restart MySQL. The exact file path depends on the installation, but the setting looks like this:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

The important part is that ONLY_FULL_GROUP_BY is omitted from the final mode list.

Before editing configuration, inspect the current mode:

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

That prevents accidental removal of other modes you still want.

Why Disabling It Is Usually a Tradeoff

Turning the mode off may help legacy applications start working again, especially when they were written against older MySQL behavior. But it also reintroduces nondeterministic query behavior, which can produce confusing reporting and hard-to-debug data issues.

If the application is under active development, fixing the SQL is generally the stronger long-term choice.

Common Pitfalls

One common mistake is removing the mode globally to fix a single broken report. That broad change can quietly weaken query correctness across the whole server.

Another mistake is rewriting GROUP BY queries by adding extra columns blindly. That may satisfy the SQL mode but change the grouping logic and produce completely different results.

A third issue is forgetting that session changes are temporary. A query may “work” in a manual SQL client session but fail again in the application because the app opens a new connection with different mode settings.

Summary

• 'ONLY_FULL_GROUP_BY prevents ambiguous grouped queries in MySQL.'
• The best fix is usually to rewrite the query, not disable the mode.
• Use SET SESSION for a temporary connection-level change.
• Use configuration changes only when you intentionally want a persistent server-wide effect.
• Disabling the mode trades short-term compatibility for weaker query determinism.