TopolearnNon-strict MySQL silently truncates and invents data; many legacy bugs come from relaxed SQL modes.
The sql_mode system variable changes how MySQL validates data and SQL. The 8.x default is:
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION
The two that matter most:
- STRICT_TRANS_TABLES: invalid or out-of-range values cause an error instead of being silently adjusted. Without it, inserting
'abc'into anINTstores0, a 300-character string intoVARCHAR(255)is truncated, and'2026-02-30'becomes'0000-00-00'— each with only a warning that applications never read. - ONLY_FULL_GROUP_BY: rejects queries that select a column that is neither grouped nor aggregated (unless it is functionally dependent on the grouped key). Without it, MySQL returns a value from an arbitrary row of each group — nondeterministic results.
Old applications sometimes break on upgrade and "fix" it by clearing sql_mode. Don't: fix the queries instead. If you truly need an arbitrary value from a group, say so with ANY_VALUE(col).
Check the active mode with SELECT @@SESSION.sql_mode;. Set it globally in the config file (sql_mode = ... under [mysqld]) and avoid per-session overrides, so every client gets the same behaviour.
Example
SELECT @@SESSION.sql_mode;
-- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,...
CREATE TABLE t (n TINYINT, s VARCHAR(3));
INSERT INTO t VALUES (300, 'abcdef');
-- ERROR 1264 (22003): Out of range value for column 'n' at row 1
SET SESSION sql_mode = ''; -- what NOT to do
INSERT INTO t VALUES (300, 'abcdef'); -- Query OK, 1 row affected, 2 warnings
SELECT * FROM t; -- 127 | abc (silently mangled)
SET SESSION sql_mode = DEFAULT;
CREATE TABLE emp (dept VARCHAR(10), name VARCHAR(20));
SELECT dept, name FROM emp GROUP BY dept;
-- ERROR 1055: ... not in GROUP BY clause ... incompatible with sql_mode=only_full_group_by
SELECT dept, ANY_VALUE(name) FROM emp GROUP BY dept; -- explicit, allowedRemember
- STRICT_TRANS_TABLES turns silent truncation into errors
- ONLY_FULL_GROUP_BY prevents nondeterministic GROUP BY results
- Keep the defaults; fix queries instead of relaxing sql_mode
Watch out for
- Clearing sql_mode to make a legacy app work, then storing corrupted values unnoticed.