Skip to main content

MY.L1.TRUNCATE — TRUNCATE in a migration, where no transaction takes it back

  • Category: safety
  • Level: 1
  • Confidence: deterministic
  • Downtime class: rewrite
  • 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.

TRUNCATE in a migration's up() empties the table with no confirmation and no predicate to limit it. On MySQL there is also no way back.

TRUNCATE is DDL here, so it causes an implicit commit: any open transaction is committed before the statement runs. The rows are gone the instant it executes — whatever fails later in the same migration — and down() is the only path back, one that cannot restore data.

Why this is a MySQL rule of its own​

The destruction is the same on PostgreSQL. The recovery is not, and the recovery is what a reader needs.

On PostgreSQL a TRUNCATE inside a transaction rolls back with it, so a migration that fails after one can still leave the table intact; the PostgreSQL rule therefore leads with the ACCESS EXCLUSIVE lock it takes. On MySQL that consolation does not exist. A rule that reused the PostgreSQL sentence here would tell a reader their transaction protects them — making them more confident and no safer, which is worse than saying nothing.

Flagged​

DB::statement('TRUNCATE TABLE sessions');

Preferred​

// Bounded batches: reversible while it runs, and no implicit commit throws away
// the transaction you thought you were in.
DB::statement('DELETE FROM sessions WHERE last_activity < UNIX_TIMESTAMP(NOW() - INTERVAL 30 DAY) LIMIT 1000');

If you genuinely need the whole table emptied and the rows recoverable, take a backup first.

When the loss is intended​

Annotate the migration with #[SqlensAllowDestructive] and a reason. That does not hide the finding: it becomes a named, visible suppression with the reason shown, so the loss is recorded rather than silenced.

When the rule stays quiet​

A table this same migration created holds no data a deploy would miss, so truncating a helper table you just built reports nothing.

Why rewrite and not online​

MySQL's TRUNCATE is fast — it drops and recreates the table rather than deleting rows one by one. The downtime axis models what happens to the table, not how long it takes, and a statement that discards every row is not one a release gate treating rewrite as "look at this" is wrong to pause on. The class comes from the package's non-DDL derivation rather than from the online-DDL matrix, which describes schema changes and has nothing to say about storage replacement.

Sources​

The fix material this rule carries​

A finding from this rule carries a payload whose strategy is none: this rule has looked, and there is no safe standard sequence. That is a conclusion rather than an omission — a finding with no payload at all says only that nobody wrote one.

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.