MySQL sql_mode: Strict Mode, ONLY_FULL_GROUP

The SQL mode is a server setting that changes how MySQL treats questionable SQL and bad data. It is a list of flags. This is a MySQL-specific feature, and it explains many “it worked on my host but not on this one” problems.

Look at the current mode

query.sql

SQL

SELECT @@sql_mode AS mode;

Output

OUTPUT

+-----------------------------------------------------------------------------------------------------------------------+
| mode                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set

In MySQL 8, the default mode has these flags:

Flag	What it does
`STRICT_TRANS_TABLES`	Rejects invalid values instead of silently changing them
`ONLY_FULL_GROUP_BY`	Rejects a `GROUP BY` query that selects a non-grouped column in an ambiguous way
`NO_ZERO_IN_DATE`, `NO_ZERO_DATE`	Rejects dates such as `2026-00-10` or `0000-00-00`
`ERROR_FOR_DIVISION_BY_ZERO`	Makes division by zero in data changes an error
`NO_ENGINE_SUBSTITUTION`	Errors if the table engine you asked for is not available

Strict mode in action

query.sql

SQL

1
2

CREATE TABLE codes (code VARCHAR(3));
INSERT INTO codes VALUES ('ABCDEF');

Output

OUTPUT

ERROR 1406 (22001): Data too long for column 'code' at row 1

The value does not fit into three characters, so strict mode refuses it. Nothing is stored.

Now turn strictness off for this session and try again:

query.sql

SQL

SET SESSION sql_mode = '';
INSERT INTO codes VALUES ('ABCDEF');
SHOW WARNINGS;
SELECT code FROM codes;

Output

OUTPUT

+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'code' at row 1 |
+---------+------+-------------------------------------------+
1 row in set

+------+
| code |
+------+
| ABC  |
+------+
1 row in set

MySQL cut the text to ABC and only gave a warning. Silent truncation like this corrupts data, which is why strict mode is the safe default. A forgiving mode hides bugs.

ONLY_FULL_GROUP_BY

This mode refuses a query where MySQL cannot tell which value to show for a column that is not in GROUP BY:

query.sql

SQL

SET SESSION sql_mode = DEFAULT;
CREATE TABLE sales (region VARCHAR(10), seller VARCHAR(10), amount INT);
INSERT INTO sales VALUES ('north', 'Asha', 100), ('north', 'Ben', 200), ('south', 'Chitra', 50);
SELECT region, seller, SUM(amount) FROM sales GROUP BY region;

Output

OUTPUT

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'learn.sales.seller' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Which seller should the north row show, Asha or Ben? MySQL refuses to guess. Either add seller to the GROUP BY, aggregate it, or remove it. The GROUP BY chapter covers this in detail.

How to set the mode

For one session: SET SESSION sql_mode = '...';
For the whole server, until restart: SET GLOBAL sql_mode = '...';
Permanently: in the configuration file (my.cnf or my.ini):

my.cnf

INI

1
2

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

Advice

Keep the default strict mode in development and production.
If an older application breaks under strict mode, fix the application, not the mode. Switching strict mode off to make an error go away is hiding a bug.
Develop with the same sql_mode your production server uses. Different modes between environments cause surprise errors on release day.
A WordPress site on old hosting often runs with a loose mode. Test before you move to a stricter host.

info

MySQL-specific

sql_mode exists only in MySQL and MariaDB. The names of the flags and the defaults differ between MySQL and MariaDB.