Understanding how to manage the sql_mode system variable is a critical skill for any database administrator or developer working with MySQL or MariaDB. Among the various flags available, ONLY_FULL_GROUP_BY is notorious for causing migration headaches and unexpected query failures. This article provides a full breakdown on why this mode exists, why you might need to disable it, and the specific syntax to achieve that dynamically using the REPLACE function.
What Is sql_mode and Why Does It Matter?
The sql_mode variable defines the SQL syntax rules and data validation checks the server follows. It acts as a behavioral switchboard, allowing you to tune the database to be strict, permissive, or compatible with other database systems like PostgreSQL or Oracle Less friction, more output..
This changes depending on context. Keep that in mind.
When you install MySQL 5.7 or later (including MySQL 8.0 and MariaDB 10.
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION
Each flag serves a specific purpose. But STRICT_TRANS_TABLES prevents invalid data insertion. NO_ZERO_DATE rejects '0000-00-00' dates. And ONLY_FULL_GROUP_BY? It enforces standard SQL compliance for GROUP BY clauses.
The Problem with ONLY_FULL_GROUP_BY
Standard SQL Behavior vs. MySQL Legacy Behavior
In standard ANSI SQL, any column appearing in the SELECT list of a GROUP BY query must either be:
- Day to day, listed in the
GROUP BYclause. In real terms, 2. Wrapped in an aggregate function (likeMAX(),MIN(),COUNT(),SUM()).
MySQL historically allowed a "loose" interpretation. In practice, if you grouped by customer_id but selected customer_name without aggregating it, MySQL would arbitrarily pick a value from the grouped rows. This was convenient for developers but logically dangerous—results were non-deterministic It's one of those things that adds up..
The Error Message
With ONLY_FULL_GROUP_BY enabled (the default), running a query like this throws Error 1055:
SELECT customer_id, customer_name, COUNT(*) as order_count
FROM orders
GROUP BY customer_id;
Error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.orders.customer_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.
Why Developers Want to Disable It
While the correct fix is to rewrite the query (add customer_name to GROUP BY or use ANY_VALUE(customer_name)), reality often dictates otherwise:
- Legacy Codebases: Applications written for MySQL 5.Refactoring them all carries high regression risk.
- Functional Dependency: In complex joins, MySQL sometimes fails to recognize functional dependencies (e.Day to day, 6 or earlier often contain hundreds of non-compliant queries. And * Third-Party Software: You cannot easily modify queries inside closed-source CMS platforms, ERP systems, or plugins. g., a primary key uniquely determining other columns), flagging valid logic as errors.
In these scenarios, disabling ONLY_FULL_GROUP_BY globally becomes a pragmatic operational necessity.
The Solution: Dynamic Removal Using REPLACE
You could manually copy the current sql_mode string, paste it into a config file, delete the text ONLY_FULL_GROUP_BY, and restart the server. But that requires downtime and manual string manipulation The details matter here..
A far more elegant, programmatic approach uses the REPLACE() string function directly inside a SET GLOBAL statement. This removes the specific flag from the current runtime value without affecting other modes Simple, but easy to overlook..
The Exact Syntax
Run the following command in your MySQL client (requires SYSTEM_VARIABLES_ADMIN or SUPER privilege):
SET GLOBAL sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
Deconstructing the Command
Let’s break down exactly what happens here:
@@sql_mode: This is a system variable shortcut. It returns the current global value as a comma-separated string (e.g.,'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE').REPLACE(str, from_str, to_str): This standard string function searchesstrforfrom_strand replaces it withto_str.- We search for
'ONLY_FULL_GROUP_BY'. - We replace it with an empty string
''.
- We search for
SELECT ...: The subquery executes first, returning the modified string (e.g.,'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE').SET GLOBAL sql_mode = ...: The outer command assigns this new cleaned string back to the global variable.
Result: The mode is stripped instantly. No restart required. All other modes (STRICT_TRANS_TABLES, etc.) remain perfectly intact.
Handling the "Trailing Comma" Edge Case
There is a minor cosmetic issue with the command above. If ONLY_FULL_GROUP_BY is the first item in the list, REPLACE leaves a leading comma.
- Input:
'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES' - Output:
',STRICT_TRANS_TABLES'
While MySQL’s parser is generally strong enough to handle leading/trailing commas in sql_mode, it is best practice to clean the string. A more reliable version uses TRIM to remove leading/trailing commas and spaces:
SET GLOBAL sql_mode = (
SELECT TRIM(BOTH ',' FROM REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''))
);
TRIM(BOTH ',' FROM ...) removes any comma characters from the start and end of the resulting string, ensuring a clean, valid mode list like 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE' That's the whole idea..
Making the Change Persistent (Surviving Restarts)
The SET GLOBAL command only affects the running instance. If the database server restarts, it reverts to the configuration file defaults. To make the change permanent, you must edit the configuration file.
Locate Your Configuration File
- Linux/macOS:
/etc/my.cnf,/etc/mysql/my.cnf, or/etc/mysql/mysql.conf.d/mysqld.cnf - Windows:
C:\ProgramData\MySQL\MySQL Server 8.0\my.ini(hidden folder) or installation directorymy.ini.
Edit the [mysqld] Section
Open the file with root/admin privileges. That said, if a sql_mode line exists, modify it. Day to day, find the [mysqld] section. If not, add it.
Option A: Explicit List (Recommended for Clarity)
List every mode you want explicitly, omitting ONLY_FULL_GROUP_BY.
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
Option B: Copy Current Runtime Value (Easiest Migration)
- Run
SELECT @@GLOBAL.sql_mode;in your client. - Copy the output.
- Paste it into the config file, manually deleting
ONLY_FULL_GROUP_BY,(including the trailing comma). - Save and restart the service (
sudo systemctl restart mysqldor