A schema change on a table nobody can lock

A VARCHAR(32) holding one of six values, on a table with four hundred writes a second and no maintenance window. Making it an enumeration is a two-word change to a migration and a four-hour lock, which are not the same thing.

The symptom

$ mysql -e "ALTER TABLE orders
    MODIFY status ENUM(...) NOT NULL,
    ALGORITHM=INPLACE, LOCK=NONE"
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
Reason: Cannot change column type INPLACE.

$ # on a copy of production:
$ time mysql staging -e "ALTER TABLE orders MODIFY status ENUM(...)"
real    3h 52m

The refusal is the good outcome: specifying LOCK=NONE turns an unpleasant surprise into an error before anything starts. Without it the statement runs, takes a shared lock and blocks writes for four hours.

Why it happens

A column type change rewrites every row, which means a copy of the table, which means the original has to be held still while it happens. Online DDL is online for operations that can be done in place and a type change is not one of them.

The fix

The three approaches

  in-place, in a window
    four hours of no writes. this system has no
    maintenance window and the business has never been
    asked for one, which is a conversation rather than
    a refusal.

  an online schema change tool
    copies the table, keeps the copy current with
    triggers, swaps. well understood, and this table
    already has a trigger, and an outbox relay polling
    it every 200ms.

  expand-and-contract in the application
    four releases, six weeks, no lock longer than a
    second at any point, and every step is an
    operation the pipeline does weekly.

The tool was rejected on a judgement about my own confidence rather than about the tool — two triggers on one table is permitted and the interaction with an outbox relay reading the same rows is not something I wanted to reason about during a four-hour operation on production.

Release one: expand

ALTER TABLE orders
  ADD COLUMN status_v2 ENUM(
    'pending','paid','shipped','cancelled','refunded','failed'
  ) NULL,
  ALGORITHM=INSTANT;

-- instant: metadata only, no rewrite, no lock beyond
-- the metadata lock at the start.

// and the write path, writing both
public function transitionTo(OrderStatus $status): void
{
    $this->status    = $status->value;   // the old column
    $this->status_v2 = $status;          // the new one
}

// reads still use $this->status.

The backfill

do {
    $affected = DB::update(
        'UPDATE orders SET status_v2 = status
         WHERE id > ? AND status_v2 IS NULL
         ORDER BY id LIMIT 50000',
        [$lastId],
    );

    $lastId += 50000;

    // proportional to replica lag, not a fixed constant
    usleep((int) ($this->replicaLagSeconds() * 500_000) + 50_000);
} while ($affected > 0);

  rows          412,884,000
  batches           8,258
  elapsed          nine days, running continuously
  replica lag p99  380ms

and the guard, which is the part that is easy to omit:
a job that alerts if the count of unbackfilled rows
stops decreasing for two hours.

it fired once, on day four, when the backfill process
had been killed by an unrelated deploy.

A nine-day job that stops silently is indistinguishable from a nine-day job that is still running, which is why the progress guard matters more than the sleep. The lag-proportional sleep is what keeps the replica within a second — a fixed constant is a guess that is wrong in both directions as load varies across the day.

The nightly agreement check

SELECT COUNT(*) FROM orders
WHERE status_v2 IS NOT NULL AND status_v2 <> status;

-- zero every night, except one:
--   1,204 rows, after a support script issued a bulk
--   UPDATE as raw SQL, touching status and not
--   status_v2.

The check is what makes a dual-write window safe and it caught exactly the failure it exists for — a writer that bypasses the model. Without it the divergence would have surfaced at cutover, with twelve hundred orders holding the wrong status and no way to tell which was right.

Releases two, three and four

  release 2   read status_v2, still write both. deployed
              after a week of clean checks.
  release 3   stop writing the old column, then MODIFY
              status_v2 ... NOT NULL, ALGORITHM=INPLACE,
              LOCK=NONE  — 41 seconds
  release 4   DROP COLUMN status, ALGORITHM=INSTANT, and
              rename status_v2, also instant.

six weeks from the first to the last.

The NOT NULL in release three requires every row populated or the statement fails after doing the work, which is why it comes after a week of clean checks rather than immediately after the backfill. The rename in release four is a fifth code change and was folded in because it is metadata only.

The caller the check found and grep did not

// found by the agreement check rather than by a search
// for 'status', because it does not contain the word
$columns = $request->input('columns', ['id', 'status']);

$rows = DB::table('orders')->select($columns)->get();

// a reporting endpoint taking a column list from a
// query parameter. it read the old column by default
// and would have returned nulls after release four.

A column name arriving from a request parameter is invisible to every static search, which is the general hazard with dynamic column selection. The check did not find it directly — it found the endpoint’s test failing during release two, which is a weaker signal that happened to be enough.

Verifying it worked

$ mysql -e "SHOW CREATE TABLE ordersG" | grep status
  `status` enum('pending','paid','shipped','cancelled',
                'refunded','failed') NOT NULL,

$ mysql -e "SELECT COUNT(*) FROM orders WHERE status IS NULL"
0

$ mysql -e "SELECT DATA_TYPE, COLUMN_TYPE
            FROM information_schema.columns
            WHERE table_name='orders' AND column_name='status_v2'"
Empty set

# and the storage
  before  status VARCHAR(32)  ~3.4 GB across the table
  after   status ENUM         ~0.4 GB

# no lock over one second at any point in six weeks

Three gigabytes reclaimed is a side effect rather than the goal — the point was that the database now enforces the six values, which had been enforced in application code at three entry points and not at the fourth. The bulk update script that broke the agreement check was writing a value that no longer exists.

What this costs

Four releases and six weeks for a column type, which is roughly forty times what the single ALTER would have cost in engineering time and does not require four hours of downtime. That trade is only correct for a system with no maintenance window, and it is worth being explicit that a system with one should take the simple option.

The dual-write window is also six weeks during which the schema is in an intermediate state that nobody would design, and a rollback partway through leaves a column that is populated and unread. Each individual step is reversible and the sequence is not, which is a property of every expand-and-contract and is not usually stated.