Skip to main content

MY.L6.SQL_MODE_ENGINE_SUBSTITUTION — a table that quietly got a different storage engine

  • Category: safety
  • Level: 6
  • Confidence: deterministic
  • Downtime class: none
  • Stability: stable
  • Suites: audit
  • Applies to: MySQL 8.4
  • Not transferable to MariaDB: this page describes MySQL 8.4 behavior. MariaDB answers the same driver and does not share these semantics, so SQLens refuses it outright rather than reasoning about it — see drivers/unsupported.

Without NO_ENGINE_SUBSTITUTION, a table asking for an engine the server cannot provide silently gets the default one instead.

The migration succeeds. The table exists. It may have neither transactions nor foreign keys — and nothing in the schema file says so.

Flagged​

SET GLOBAL sql_mode = 'NO_ZERO_DATE';

Preferred​

SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Why four rules and not one​

Four independent guards live in the single sql_mode variable, and a project may legitimately accept one while wanting the others.

Under a single id, one ignore-list entry would silence all four — and whoever added it could not say afterwards which one they had accepted. Each flag therefore gets its own rule, its own id and its own consequence, because a shared "a flag is missing" message would tell a reader what is wrong without telling them why it matters.

What is read, and what is not​

Only the server's value. Laravel sets sql_mode per connection from the connection's strict flag, so an application connection can be strict while the server hands something laxer to everything else that connects — a queue worker with different config, a migration run by hand, a reporting tool.

A flag is matched exactly, never as a substring: NO_ZERO_DATE is contained in NO_ZERO_IN_DATE, and a substring check would report a server carrying only the second as if it carried both.

An empty sql_mode is called out on its own. It is a legal value and a deliberate-looking one, and reporting it as "one flag missing" would understate a server running with every guard off at once.

A value the server withholds is reported as undetermined with a reason, never as a pass.

Sources​