MySQL migration safety: the checklist

Most MySQL migration outages come from a handful of statement classes. The checklist below is what to verify for every schema migration, with the manual reference for each item, and where a CI check can do the work for you.

1. Know the online-DDL class of every statement

For each ALTER TABLE clause, find its row in the online DDL operations matrix and answer three questions: is it instant, does it rebuild the table, does it permit concurrent DML? The dangerous answers are rebuild (I/O, replica lag, long metadata lock) and concurrent DML: No (writes blocked). Cheat sheet: which ALTERs lock or rebuild.

2. Assert ALGORITHM and LOCK

Append , ALGORITHM=INPLACE, LOCK=NONE (or ALGORITHM=INSTANT) to every ALTER you expect to be online. MySQL then errors immediately if it cannot comply, instead of silently copying the table (ALTER TABLE).

3. Respect metadata locks

Every DDL statement takes an exclusive metadata lock at some point. If a long transaction holds the table, the DDL waits, and all new queries queue behind it. Set a short lock_wait_timeout for migrations and never run them while long reports or batch jobs hold the table (Metadata Locking).

4. Treat these as special cases

• Character set / collation conversion — rebuild, writes blocked.
• Column data-type changes — COPY only, writes blocked; nullability changes rebuild but allow writes.
• Primary-key changes — always a rebuild; standalone DROP PRIMARY KEY blocks writes.
• ADD FOREIGN KEY with foreign_key_checks=1 — COPY; run with checks disabled if the data is already consistent.
• STORED generated columns, FULLTEXT and SPATIAL indexes, partitioning changes — blocking classes.
• ADD COLUMN … FIRST/AFTER — instant only from 8.0.29.

5. Protect data, not just uptime

DROP TABLE, DROP COLUMN, TRUNCATE cannot be rolled back. Use expand/contract and drop last, with a backup.

6. Automate the check in the pull request

All of the above is mechanical, and mechanical checks belong in CI. Presift applies these rules to every .sql migration in a PR and annotates the offending line with the rule, the reason and the manual reference — offline, with no access to your database.

name: migration-safety
on: [pull_request]
permissions:
  contents: read
  id-token: write          # lets Presift start your 30-day evaluation
jobs:
  presift:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: nishu-sde/presift@v1
        with:
          paths: db/migrations
          fail-on: error