CONCEPT Cited by 1 source

MySQL `sql_mode`¶

sql_mode is a MySQL server variable that controls SQL-dialect strictness — which queries the server silently tolerates vs rejects, which implicit type coercions are allowed, which aggregation rules are enforced. Its default value changed between MySQL 5.7 and 8.0, making the 5.7 → 8.0 upgrade one of the most common sources of "this query used to work and now errors" in production.

Default value shift¶

MySQL 5.7 default (historical):

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

MySQL 8.0 default (per the post):

ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

The 8.0 default is stricter in semantics (some legacy laxity removed) but slimmer in naming — several modes that were separate flags on 5.7 are now implicit in 8.0's stricter baseline.

The modes that bite on upgrade¶

`ONLY_FULL_GROUP_BY`¶

The most-hit breakage. When on, a non-aggregated column in SELECT must appear in GROUP BY or be functionally determined by a GROUP BY column (e.g. same primary key).

-- 5.7 default (no ONLY_FULL_GROUP_BY): returns arbitrary name per dept
SELECT dept_id, name FROM employees GROUP BY dept_id;

-- 8.0 default (ONLY_FULL_GROUP_BY): ERROR 1055
-- Expression #2 of SELECT list is not in GROUP BY clause
-- and contains nonaggregated column `employees.name`

A 5.7 codebase silently relied on the "one arbitrary row per group" semantics. 8.0 requires either adding the column to the GROUP BY, wrapping in ANY_VALUE(), or using a proper aggregate (MAX(name), etc.).

`ERROR_FOR_DIVISION_BY_ZERO`¶

Division by zero returns NULL by default in 5.7 without the flag; with it (the 8.0 default), division by zero in an INSERT or UPDATE is an error.

`NO_ENGINE_SUBSTITUTION`¶

CREATE TABLE ... ENGINE=X where X is unavailable errors rather than silently substituting the default engine.

Deprecated SQL modes on 8.0¶

Per the post: "MySQL 8 has deprecated the ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, STRICT_ALL_TABLES, and TRADITIONAL SQL modes, so you should remove those from your SQL statements if you are using them."

The framing is nuanced — the behaviour of ONLY_FULL_GROUP_BY (strict GROUP BY checking) is now the default always-on behaviour. The deprecation is of the named mode as something to explicitly set — you cannot turn it off in an 8.0-forward configuration.

The upgrade audit problem¶

Unlike reserved words (finite published list, single information_schema query to audit), auditing a codebase for SQL-mode-breakage requires full static analysis of every SQL query the application emits. Specifically:

Every GROUP BY query that selects a non-aggregated non-key column.
Every INSERT / UPDATE that could divide by zero under some data state.
Every query relying on loose type-coercion.

This is typically the hardest axis of the 5.7 → 8.0 upgrade in practice. The pragmatic path is:

Upgrade a staging/replica server to 8.0 with the full strict sql_mode.
Replay representative production traffic.
Collect errors, fix them, repeat.

Session-level override¶

sql_mode is set per-session via SET SESSION sql_mode = '...'; individual queries or sessions can opt out of strict modes as a transitional measure:

SET SESSION sql_mode = '';
-- or temporarily drop ONLY_FULL_GROUP_BY:
SET SESSION sql_mode = (
    SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '')
);

This is a stopgap — relying on per-session sql_mode relaxation is technical debt, and some clients (ORM connection pools) don't expose a clean way to set session variables reliably.

Classification¶

Backward-compatibility hazard at the query-semantics altitude. The stored schema is unchanged; the stored data is unchanged; the wire protocol is unchanged. What changed is how the server evaluates queries. Sibling to caching_sha2_password (auth-protocol altitude), reserved words (DDL-parsing altitude), and utf8mb4 (storage altitude) on the 5.7 → 8.0 axis — four orthogonal classes of version-upgrade break, each demanding its own audit.

Seen in¶

sources/2026-04-21-planetscale-how-to-upgrade-from-mysql-57-to-80 — canonical wiki introduction. JD Lien's upgrade checklist names the 8.0 default as ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION and notes the deprecation of the named ONLY_FULL_GROUP_BY / STRICT_TRANS_TABLES / STRICT_ALL_TABLES / TRADITIONAL modes.

MySQL sql_mode¶