Skip to main content

MY.L6.EXPLICIT_DEFAULTS_FOR_TIMESTAMP_OFF — legacy TIMESTAMP behavior the migration never asked for

  • 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.

With explicit_defaults_for_timestamp off, the server applies a set of legacy behaviors to TIMESTAMP columns that nothing in your migration asked for.

Two of them matter:

  • The first TIMESTAMP column in a table silently acquires DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. A column declared as a plain timestamp then rewrites itself on every UPDATE.
  • A TIMESTAMP column is NOT NULL unless declared nullable, and assigning NULL stores the current time rather than raising.

Why the schema does not show it​

The server applies this at table-creation time, so it is in none of the migrations anybody reviews. Two servers configured differently produce different tables from the same file — and neither table looks wrong on its own.

That is what makes this a server finding rather than a schema one. Reviewing the migration cannot catch it, and reviewing the resulting table only catches it if you already suspect it.

The expensive half​

The NULL-becomes-now behavior is invisible in the data. A column holding now() because the application wrote null looks exactly like one the application filled deliberately. Nothing is malformed, no query fails, and the question only surfaces when somebody asks why a record nobody touched has a fresh timestamp.

MySQL 8.4 ships with the flag on. A server carrying OFF today is either a deliberate opt-out or a configuration inherited from a 5.x-era deployment, where OFF was the default.

Flagged​

SET GLOBAL explicit_defaults_for_timestamp = OFF;

Preferred​

SET GLOBAL explicit_defaults_for_timestamp = ON;

What is read, and what is not​

Only the server's value is judged. The variable is settable per session, so a connection that turned the flag off for itself is not a statement about the server and is never reported as one.

Both spellings are accepted, because MySQL answers differently depending on how the value is asked for. Measured on 8.4.10: performance_schema.global_variables and SHOW GLOBAL VARIABLES both answer ON, while @@global.explicit_defaults_for_timestamp answers 1. SQLens only ever takes the first two, so the word is what it reads — but a my.cnf, an operator's own check or a comparison written by hand may all carry the digit, and treating the two as different values would report a correctly configured server as broken.

A value the server withholds is reported as undetermined with a reason, never as a pass. The vendor default is ON, which is exactly what makes "unreadable, so presumably fine" tempting — and the assumption would be right often enough that nobody would notice it was an assumption.

When this is expected​

A deployment that deliberately keeps the legacy behavior to support code written against it. That is the case for the ignore list, with the reason recorded there.

Sources​