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
renaming a member in placeINSTANTno

The last 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 rename 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, which is the one enum change MySQL applies instantly and 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.