Skip to main content

MY.L3.MIXED_DDL_DML_NOT_ATOMIC — A migration that changes the schema and then writes data

  • Category: safety
  • Level: 3
  • Confidence: deterministic
  • Downtime class: blocking
  • Stability: stable
  • Suites: lint
  • 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.

On MySQL, a migration that changes the schema and then writes data is not one operation, whatever you assumed while writing it.

Every DDL statement causes an implicit commit. The schema change is already permanent by the time the data write runs — so if the write fails, the migration is left half applied: schema changed, data not, and nothing rolls back. down() becomes the only way out, and it has to undo a state nobody planned for.

This is a different hazard from the PostgreSQL one​

There is a driver-neutral rule about mixing schema changes and data writes, and it judges a different shape: both inside one transaction, where the ALTER's lock is held for the whole backfill.

That cannot happen on MySQL. Laravel's MySQL grammar reports supportsSchemaTransactions() === false, so the migrator never wraps a MySQL migration in a transaction at all — there is no transaction to hold a lock across, and the neutral rule correctly stays quiet.

Reusing its message here would name the wrong cause: it would tell you to worry about a lock, when what you should worry about is a migration that cannot fail cleanly.

Flagged​

Schema::table('orders', fn (Blueprint $table) => $table->string('status')->nullable());
DB::statement("UPDATE orders SET status = 'new' WHERE status IS NULL");

Preferred​

// Migration 1 — schema only. It either completes or leaves nothing behind:
Schema::table('orders', fn (Blueprint $table) => $table->string('status')->nullable());
// Migration 2 (or a queued job), after the first has run — data only:
// DB::table('orders')->whereNull('status')->limit(1000)->update(['status' => 'new']);

Split, each step either completes or leaves nothing behind — which is the most a MySQL migration can promise.

What the rule reports, and how often​

Once per migration, on the first data write that follows a schema change. Not on every write: a backfill written as three statements has one problem, not three, and three findings pointing at one cause is how a report teaches its reader to skim.

Whatever table each statement names. The implicit commit does not care, so neither does this rule: a migration that alters orders and backfills order_lines is exactly as un-rollbackable as one that does both to orders. (The driver-neutral rule does ask about the table, because a lock is held on a specific one — another place the two differ for a reason.)

Order matters. A write that runs before any schema change is not stranded by a commit that has not happened yet, and reports nothing.

A statement the classifier could not place is not treated as a schema change. It might be either, and "might" is not the basis for a finding.

Sources​

The fix material this rule carries​

A finding from this rule carries machine-readable fix material, using this sequence:

  • transaction_split — Give each strong lock its own migration, so each is released before the next is taken.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.