MySQL ENUM Migration in Three Steps: No Table Copy Required

Six statuses in the spec, four in the tables

The client's new spec asked for a six-value submission flow: pengajuan (submitted), verifikasi (verification), diproses (processing), disetujui (approved), ditolak (rejected), selesai (done). The database of KotaPortal, a public-service portal I maintain, still spoke the old four-value English dialect on both submission tables, from pending to rejected. Those two worlds can't be reconciled with a single ALTER TABLE line.

My first thought was as simple as it gets: run MODIFY COLUMN with the new value list and move on. The official MySQL 5.7 manual changed my mind with one sentence: inserting ENUM members in the middle of the list renumbers the existing values and forces a full table copy [1]. A table copy means a heavy operation, and when the value order shifts, old data stored as internal indexes can silently jump to a different meaning.

For other column types the failure is even more explicit. MySQL refuses with Cannot change column type INPLACE and suggests ALGORITHM=COPY [1]. Status columns are actually allowed to change in place, but only in one shape: new values appended at the end of the list, as long as the storage size doesn't grow [1]. That's where I decided the migration had to happen in three steps, not one.

The expand, migrate, contract choreography

The order goes like this. Step one: MODIFY COLUMN with a combined list, old and new values side by side, the old ones staying in front. Step two: UPDATE maps the data, pending becomes pengajuan, reviewed becomes verifikasi, approved becomes disetujui, rejected becomes ditolak. Step three: MODIFY COLUMN again, now with only the six Indonesian values. The same pattern runs twice, once for the registration table, once for the complaints table.

The trick lives in step one. A combined ENUM of four old plus six new values still counts as a storage size that doesn't change, so the ALTER qualifies as in-place [1]. One small detail makes it safe: a NOT NULL ENUM defaults to the first element of its list [2]. I put pengajuan first in the mid-state list, so a row that happens to land mid-migration gets a default that's actually correct, not garbage.

Links without foreign keys, a lossy down path

The same migration also added link columns pointing at target entities: entity_id, a rental package id, start and end dates. All plain BIGINT, no foreign keys. That's a deliberate call. The linked entities use soft delete, and a strict foreign key means removing a parent row can break in-flight submissions. Link consistency is the application's job here, not the database's.

Then there's rollback. golang-migrate works in up/down file pairs, applied in increasing version order and reversed on the way down [3]. My down file isn't clean, and the header comment says so: diproses maps back to reviewed, selesai falls back to approved. Statuses born after the migration have no one-to-one match in the old vocabulary. Kubernetes' API doctrine says objects must round-trip between versions without information loss [4], and this down path clearly breaks that. Database migrations on MySQL 5.7 often force exactly this choice: step back with a mapping that loses detail, or don't step back at all. I took the first option, with an application_status_history table recording every status change as the recovery trail.

Proving both directions before production

Before it touched the dev database, the migration ran against a disposable test one: up, down, up again. That cycle proves both directions at once: the up file runs without the in-place error, and the down file genuinely restores the previous schema. One easily missed detail: the new history table has to join the test utility's cleanup list, otherwise data leaks between test cases.

If you ever see that in-place error in your own migrations, check the value order first. Most of the time the cause is a new value inserted mid-list instead of appended at the end. The rest are column types that simply don't support in-place changes, and the answer isn't to force it — split it into steps that are each legal on their own.

Sources

[1] MySQL 5.7 Reference Manual, Online DDL Operations (snapshot 2025-12-16)

[2] MySQL 5.7 Reference Manual, The ENUM Type (snapshot 2026-01-03)

[3] golang-migrate, MIGRATIONS.md

[4] Kubernetes API deprecation policy