Skip to main content

MY.L4.ENUM_CHANGE — Changing an ENUM's member list

  • Category: safety
  • Level: 4
  • Confidence: heuristic
  • Downtime class: derived — and the matrix declines here, see below
  • 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.

MySQL stores an ENUM value as its ordinal position in the member list, not as the string. Most of this rule follows from that one fact.

Measured on MySQL 8.4 — and it corrects the received wisdom​

Each change run under ALGORITHM=INSTANT, then ALGORITHM=INPLACE, against a real 8.4:

The member list changes by…MySQL 8.4 runs itSafe for a running app?
appending a member at the endINSTANTyes
appending across the 255 → 256 member boundaryCOPYyes
inserting a member in the middleCOPYno
removing a memberCOPYno
reordering membersCOPYno
respelling a member so its collation still calls it equal ('b' → 'B')INSTANTno
renaming a member to a different word ('b' → 'x')COPY, and it abortsno

⚠️ Those last two rows used to be one row reading “renaming a member in place → INSTANT”, and that was the collation-equivalent case generalised into a rule. The engine compares member names positionally under the column's collation, so only a respelling the collation calls equal is a no-op. Measured on MySQL 8.4.10:

'b' → 'B'   ALGORITHM=INSTANT   accepted
'b' → 'x'   ALGORITHM=INSTANT   ERROR 1846  ALGORITHM=INSTANT is not supported.
                                            Reason: Need to rebuild the table to change column type.
'b' → 'x'   default algorithm   ERROR 1265  Data truncated for column 's' at row 2

A real rename is a table copy, and during the copy the old member is no longer in the definition — so every row still holding it truncates, which under STRICT_TRANS_TABLES (in the default sql_mode) makes the ALTER fail rather than run. This page previously warned only about a silent reinterpretation and said nothing about the abort.

The respelling row is the one nobody expects, and it is why this rule exists at level 4 rather than alongside the cost-oriented rules at level 2.

A respelling is the cheapest change MySQL offers here and one of the most dangerous. The stored values are ordinals, so renaming 'b' to 'B' rewrites nothing and takes no lock — and every row that read 'b' a moment ago now reads 'B', in an application that has not been redeployed. Cost and compatibility are not the same axis; a rule that reported only the cost would call this one free.

Flagged​

Schema::table('orders', fn (Blueprint $table) => $table->enum('status', ['draft', 'paid', 'void'])->change());

Preferred​

// Append at the end. It is instant, and unlike the equally instant respelling above it is the one
// no deployed code can be surprised by — every existing value keeps its meaning:
Schema::table('orders', fn (Blueprint $table) => $table->enum('status', ['draft', 'sent', 'paid', 'refunded'])->change());

For anything else, stage it: add the new member, deploy the code that accepts it, migrate the rows, then remove the old member in a later release.

Why it is heuristic, and reports the safe case too​

MySQL's MODIFY names the column's whole definition, so the statement carries the target member list and not the current one. Which of the six rows above a given statement is cannot be read from it — only from a comparison with the live column.

So an append-only change is reported as well, and the finding says so in its own text. The trade is deliberate: a false positive costs you one look at the column; a false negative ships an application reading a value the database no longer has.

Why the finding carries no downtime class​

modify_enum_definition is a conditional entry in the online-DDL matrix — instant only when the member is appended at the end and the storage size is unchanged (an ENUM of up to 255 members takes one byte, 256 or more take two). Both conditions turn on the current member list, which the migration does not contain, so the matrix declines and SQLens does not invent a value.

The measurement above shows it is right to: the real cost ranges from INSTANT to COPY, so any single class would be wrong about most of the cases.

Sources​

The fix material this rule carries​

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

  • enum_append_only — Extend the enumeration by appending, never by rewriting its existing members.

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.