The Best SQL Mode Configuration for MySQL 8.4

MySQL's SQL mode is not a collection of optional style preferences. It decides whether invalid values are rejected, whether grouped queries have deterministic meaning, and whether an unavailable storage engine causes an error or a silent substitution. For a new application on MySQL 8.4 LTS, the best general-purpose configuration is the strict default shipped by MySQL itself:

[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"

This is a deliberately conservative baseline. It rejects common data-quality mistakes without changing fundamental SQL syntax. It also matches the MySQL 8.4 default, which reduces surprises between development, CI, and production. Pin the complete value in version-controlled server configuration when reproducibility matters; re-evaluate it as part of every major MySQL upgrade.

Why These Six Modes Belong Together

STRICT_TRANS_TABLES

Strict mode turns many lossy conversions and missing-value warnings into errors. A string such as 'not a number' is no longer silently stored as 0 in an integer column, an oversized value is not clipped to the column range, and a missing value for a NOT NULL column without a default is rejected. That is the central data-integrity guarantee in the configuration.

STRICT_TRANS_TABLES gives transactional tables such as InnoDB statement-level atomicity: an invalid row aborts the statement and the transaction can roll back. STRICT_ALL_TABLES sounds stronger, but a multi-row write to a nontransactional table can still leave earlier rows committed before a later row fails. The better modern answer is to use InnoDB for application data, not to combine both strict modes and assume that a nontransactional engine has become transactional.

ONLY_FULL_GROUP_BY

This mode rejects an aggregate query that selects a nonaggregated column when that column is neither grouped nor functionally dependent on the grouped columns. Without it, MySQL may choose an arbitrary value from each group, and adding ORDER BY cannot control that choice because sorting happens after grouping.

-- Ambiguous: which customer name belongs to each country?
SELECT country, customer_name, MAX(total)
FROM orders
GROUP BY country;

The right repair is to express the intended query, often with a window function, a subquery, an additional grouping column, or an explicit ANY_VALUE() when any member really is acceptable. Disabling ONLY_FULL_GROUP_BY hides ambiguity rather than resolving it.

NO_ENGINE_SUBSTITUTION

If a requested storage engine is unavailable, table creation should fail. Silently replacing it with the server's default engine can change transaction, locking, foreign-key, and durability behavior. This mode turns that semantic change into a visible deployment error.

Date and Arithmetic Validation

NO_ZERO_DATE rejects '0000-00-00', while NO_ZERO_IN_DATE rejects values such as '2026-00-15' when strict mode is active. Use NULL for an unknown date and a separate status column when the application must distinguish why a date is unavailable.

ERROR_FOR_DIVISION_BY_ZERO makes division by zero in data-changing statements an error together with strict mode. For a plain SELECT, division by zero still returns NULL and reports a warning. Applications should validate divisors, but the database must not quietly persist the result of invalid arithmetic.

These three mode names are deprecated in MySQL 8.4 because their behavior is expected to become part of strict mode itself. They remain in the 8.4 default and therefore belong in an explicitly pinned 8.4 configuration. Do not copy this exact string blindly into a later major release: remove a mode only when the target release has incorporated its behavior or no longer recognizes its name.

Modes Not Enabled by Default

The best baseline does not enable every mode that sounds strict. The remaining modes mostly alter syntax, preserve legacy behavior, or solve a specialized import problem:

• ANSI_QUOTES makes double quotes delimit identifiers instead of strings. It can improve portability in a new codebase, but it breaks SQL that uses double-quoted strings. Adopt it only as an explicit project-wide syntax convention.
• PIPES_AS_CONCAT, REAL_AS_FLOAT, and IGNORE_SPACE also change parsing. They do not improve stored-data integrity.
• NO_AUTO_VALUE_ON_ZERO is useful while restoring dumps that contain a literal zero in an AUTO_INCREMENT column. mysqldump enables it where needed; normal application inserts should use NULL to request the next value.
• NO_BACKSLASH_ESCAPES changes string-literal parsing. Parameter binding remains mandatory either way; this mode is not a substitute for prepared statements.
• HIGH_NOT_PRECEDENCE restores obsolete operator precedence. New queries should use the current precedence and parentheses where intent is not obvious.
• NO_UNSIGNED_SUBTRACTION changes expression result types. Choose it only when the data model intentionally depends on signed subtraction from unsigned operands.
• TRADITIONAL is a combination mode, not a more future-proof policy. Listing the required modes explicitly makes configuration review and version changes clearer.

Several names from older MySQL releases no longer belong in a current configuration. In particular, NO_AUTO_CREATE_USER was removed in MySQL 8.0, and the old NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS modes are not MySQL 8.4 server modes. A startup configuration should contain only names supported by the exact server version being deployed.

Apply and Verify the Configuration

Put the server policy in the managed my.cnf or my.ini used by every environment. Runtime changes are useful for experiments, but a global change affects only connections opened afterward. Existing sessions keep their current value.

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

Every connection inherits the global value when it opens, but a client can change its own session mode. SQL mode is therefore a consistency policy, not a security boundary. Restrict application database privileges, assert the expected session value when a connection pool is initialized, and monitor configuration drift rather than relying on the mode to resist a hostile client.

Source servers and replicas should use the same mode. This is especially important for partitioned tables: MySQL warns that changing SQL mode after partitioned data has been created can alter partition behavior and risk data loss or corruption.

Introducing Strict Mode to an Existing System

Do not flip the production setting first and wait for errors. Treat the change like a schema migration:

1. Record @@GLOBAL.sql_mode and @@SESSION.sql_mode in every environment.
2. Enable the target configuration in development and CI, then run the complete application and migration suite.
3. Audit warnings, invalid dates, truncated strings, out-of-range numbers, missing defaults, and ambiguous grouped queries.
4. Repair existing rows and application statements instead of adding IGNORE broadly. INSERT IGNORE and UPDATE IGNORE deliberately downgrade several errors back to warnings.
5. Roll out to a production-like environment, including replicas and background workers, before production.
6. After deployment, verify newly opened sessions and watch database warnings and application error handling.

The Practical Answer

For MySQL 8.4, keep all six default modes. The configuration is strict where silent coercion would corrupt meaning, conservative where syntax compatibility matters, and explicit enough to reproduce across environments. Add a nondefault mode only for a documented application requirement, and never disable a default merely to make a broken query or invalid write pass.

References

• [MySQL-SQL-Mode]MySQL 8.4 Reference Manual: Server SQL Modes.
• [MySQL-Group-By]MySQL 8.4 Reference Manual: MySQL Handling of GROUP BY.