Skip to main content

MY.L2.COPY_ALTER_TYPE — Column redefinition that rebuilds the table

  • Category: safety
  • Level: 2
  • Confidence: heuristic
  • Downtime class: derived per statement from the online-DDL matrix
  • 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.

$table->…->change() compiles to ALTER TABLE … MODIFY, and MySQL's MODIFY takes the column's whole definition rather than a delta. The same statement is what you write to change the data type, to flip the nullability, to move the column — or to change nothing at all. Which of those it turns out to be depends on the column's current definition, and no migration contains that.

That matters because the cheapest and the most expensive outcome look identical in the SQL:

The redefinition differs from the current column in…MySQL 8.4 runs it
the data type (INT → BIGINT)ALGORITHM=COPY — full table rebuild
a VARCHAR shrink (100 → 60)ALGORITHM=COPY — full table rebuild
a VARCHAR extension across the length-prefix byte class (63 → 64, utf8mb4)ALGORITHM=COPY — full table rebuild
a VARCHAR extension within it (100 → 200, utf8mb4)ALGORITHM=INPLACE, no rebuild
the nullability, either wayALGORITHM=INPLACE, with rebuild
the column position (AFTER x)ALGORITHM=INPLACE, with rebuild
the DEFAULT onlyALGORITHM=INSTANT

A COPY rebuild writes every row into a new table while a shared lock blocks concurrent writes, so its cost grows with the table rather than with the size of the change. On a laptop it is instant; on a live table with millions of rows it is an outage with a deploy attached to it.

Why this rule is heuristic​

The rule reports the shape of the risk, not a certainty, and the reason is one sentence: the statement carries the column's target definition but not its current one. A redefinition whose only delta is the DEFAULT is instant, and this rule flags it anyway, because nothing in the SQL tells the two apart. That is a deliberate asymmetry — a false positive costs you one look at the column, a false negative costs an outage — and the finding says so in its own text rather than leaving you to discover it.

VARCHAR is reported as undetermined​

A VARCHAR redefinition is the one case MySQL may run without rebuilding, and whether it does turns on the current length: extending is in place while the length prefix keeps its byte size (one byte below 256 bytes of storage, two at or above), while shrinking — or crossing that boundary — copies the table. SQLens reports undetermined there rather than guessing in either direction. Compare the target length against the column's current length and character set before deploying.

ENUM and SET columns are left to their own rule: appending a member is instant, inserting one in the middle is a copy, and that distinction needs the current member list.

Flagged​

Schema::table('orders', fn (Blueprint $table) => $table->unsignedBigInteger('total_cents')->change());

Preferred​

// Add the widened column, backfill it in batches, then cut over in a later migration —
// the table stays writable the whole time:
Schema::table('orders', fn (Blueprint $table) => $table->unsignedBigInteger('total_cents_new')->nullable());

Making it certain instead of likely​

If the change has to run in place, say so to the server. A statement that names ALGORITHM=INPLACE fails outright when MySQL would have to copy the table, instead of silently doing it:

DB::statement('ALTER TABLE orders MODIFY total_cents BIGINT UNSIGNED NOT NULL, ALGORITHM=INPLACE, LOCK=NONE');

That turns the uncertainty this rule reports into a deploy that either runs online or refuses to run at all — which is the outcome you actually want from a release gate.

Sources​

The fix material this rule carries​

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

  • expand_contract — Add the new shape, move readers and writers across, remove the old one — over three deploys.

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.