Linting MySQL Migrations for Online DDL Compatibility

PostgreSQL teams have dedicated migration linters; MySQL teams mostly have code review. That gap matters because MySQL’s online DDL behaviour is subtle: the same ALTER TABLE ... MODIFY can be instant, in-place or a full table copy depending on the column, the character set and the version, and without explicit clauses MySQL silently picks the best algorithm it can — including a blocking copy. The most effective MySQL lint rule is therefore procedural rather than clever: every ALTER TABLE must state ALGORITHM and LOCK, so the server itself refuses anything that cannot run the way the author intended. This guide builds a small MySQL lint suite around that idea, adds the other rules that prevent common MySQL migration incidents, and connects it to a dry run that lets MySQL validate the clauses. It belongs to Migration Linting & Static Analysis.

MySQL Lint Rules and What They Prevent Matrix of MySQL migration lint rules, what each checks, and the incident it prevents. MySQL Lint Rules and What They Prevent Rule Checks Prevents explicit-algorithm-lock every ALTER has ALGORITHM= and LOCK= silent table copies one-ddl-per-file at most one DDL statement per migration half-applied migrations (implicit commits) lock-wait-timeout SET SESSION lock_wait_timeout before DDL metadata-lock pile-ups large-table-routing ALTER on listed large tables must use gh-ost replica lag for hours enum-append-only ENUM changes only append members COPY rebuilds on ENUM edits
Four rules cover most MySQL migration incidents; the first turns MySQL itself into the final checker.

Symptom / Error Signatures

MySQL migrations that would have benefited from linting cause:

  • A “quick” column change that ran as copy to tmp table for an hour, blocking writes.
  • A three-statement migration that failed on its third statement, leaving the first two committed and the migration tool marked dirty.
  • A processlist full of Waiting for table metadata lock behind an ALTER waiting on a long transaction.
  • Replicas hours behind after an in-place index build on a very large table.

With the rules in place, the failures move to CI:

db/migrations/V61__widen_name.sql:1: error: explicit-algorithm-lock: ALTER TABLE customers lacks ALGORITHM= and LOCK=
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Root Cause Analysis

MySQL chooses the least restrictive algorithm it can for each ALTER TABLE unless told otherwise, and the choice depends on details a reviewer rarely checks: whether a VARCHAR change crosses the 255-byte length-prefix boundary, whether an ENUM member is appended or inserted, whether a column is indexed, the MySQL minor version. When you specify ALGORITHM=INSTANT or ALGORITHM=INPLACE, LOCK=NONE, the server validates the request and fails with a clear error if it cannot comply, instead of falling back. That makes the most reliable “linter” the server itself — provided every statement asks.

Two other properties of MySQL DDL shape the rules. Each DDL statement commits implicitly, so multi-statement migration files can be left half-applied, as discussed in avoiding implicit commits in MySQL DDL migrations. And every DDL needs an exclusive metadata lock briefly, with a default lock_wait_timeout of one year.

Two-Stage MySQL Check Four steps. Static rules check that each ALTER states ALGORITHM and LOCK, that each file has one DDL statement, and that lock_wait_timeout is set. A dry run applies the migration to a scratch MySQL of the same version with production's schema. MySQL rejects any clause it cannot honour. Large tables are routed to gh-ost. Two-Stage MySQL Check STEP 1 Static rules clauses, one DDL, timeout STEP 2 Scratch MySQL prod schema, same version STEP 3 Apply migration server validates clauses STEP 4 Route large tables gh-ost / pt-osc
Static rules make sure every statement asks for online behaviour; the dry run lets MySQL confirm it can deliver it.

Immediate Mitigation

1. Add the explicit-clauses rule. A simple parser-free check is acceptable here because the rule is about the presence of clauses in ALTER TABLE statements; strip comments first.

# Python 3.11 · scripts/mysql_migration_lint.py · CI step over changed .sql files
# WARNING: heuristic statement splitting; keep migrations simple (one DDL per file) for accuracy.
import re, sys

def strip_comments(sql):
    sql = re.sub(r"/\*.*?\*/", " ", sql, flags=re.S)
    return re.sub(r"(--|#)[^\n]*", " ", sql)

failed = False
for path in sys.argv[1:]:
    sql = strip_comments(open(path).read())
    stmts = [s.strip() for s in sql.split(";") if s.strip()]
    ddl = [s for s in stmts if re.match(r"(?i)(ALTER|CREATE|DROP|RENAME)\s", s)]
    if len(ddl) > 1:
        print(f"{path}: error: one-ddl-per-file: {len(ddl)} DDL statements"); failed = True
    for s in ddl:
        if re.match(r"(?i)ALTER\s+TABLE", s) and not (re.search(r"(?i)ALGORITHM\s*=", s) and re.search(r"(?i)LOCK\s*=", s)):
            print(f"{path}: error: explicit-algorithm-lock: {s[:60]}…"); failed = True
    if ddl and not re.search(r"(?i)SET\s+SESSION\s+lock_wait_timeout", sql):
        print(f"{path}: error: lock-wait-timeout: set lock_wait_timeout before DDL"); failed = True
sys.exit(1 if failed else 0)

2. Dry-run against a scratch MySQL of the same version with production’s schema loaded (mysqldump --no-data), so the server validates every ALGORITHM/LOCK clause.

# Shell · CI job · MySQL service container matching production's version
mysql -h 127.0.0.1 -uroot -e "CREATE DATABASE shop"
mysql -h 127.0.0.1 -uroot shop < prod_schema.sql
for f in $CHANGED_MIGRATIONS; do mysql -h 127.0.0.1 -uroot shop < "$f" || exit 1; done

3. Add a large-table routing rule. Keep a list of tables above a size threshold (exported from information_schema.TABLES on production) and fail any native ALTER TABLE against them, requiring the change to be expressed as a gh-ost job instead.

Permanent Fix / Long-Term Pattern

Make the migration template satisfy the rules by default: every new MySQL migration file starts with SET SESSION lock_wait_timeout = 5; and contains one ALTER TABLE ... , ALGORITHM=..., LOCK=...;. Run the static rules and the dry run on every pull request, and route large-table changes through an online schema change tool, configured as described in throttling gh-ost on replica lag or, for Skeema users, via alter-wrapper-min-size in managing MySQL schemas with Skeema.

-- MySQL 8.0 · migration template · one DDL, explicit online clauses
-- WARNING: if MySQL rejects the clauses, redesign or route through gh-ost; do not just remove them.
SET SESSION lock_wait_timeout = 5;
ALTER TABLE customers ADD COLUMN loyalty_tier VARCHAR(16) NULL, ALGORITHM=INSTANT;
-- ROLLBACK PATH: ALTER TABLE customers DROP COLUMN loyalty_tier, ALGORITHM=INSTANT;

Atlas’s migrate lint also supports MySQL and can complement these rules with destructive-change and data-dependent analyzers, as noted in Migration Linting & Static Analysis. Review the large-table list quarterly as tables grow.

What Happens to a MySQL ALTER in CI Decision tree. If the statement lacks ALGORITHM and LOCK, static lint fails it. If it has them and targets a listed large table, the routing rule requires gh-ost. Otherwise the dry run applies it; if MySQL rejects the clauses, the author redesigns; if it accepts, the migration passes. What Happens to a MySQL ALTER in CI ALGORITHM and LOCK stated? yes no Listed large table? yes no Route through gh-ost Dry run: MySQL validates Lint fails: add clauses
Every path ends in either an online change MySQL has confirmed or an explicit redesign.

Keep the dry-run environment faithful. The scratch MySQL must match production’s major and minor version, because instant and in-place support has expanded across 8.0 releases — ALGORITHM=INSTANT for adding columns anywhere in the table and for dropping columns arrived in 8.0.29, for example — and its innodb_* defaults, SQL mode and character set should match too. Pin the container image to the exact production version and update it with production upgrades, so a change that the dry run accepts is one production will accept as well.

Verification Checklist

Frequently Asked Questions

Why require ALGORITHM and LOCK if MySQL picks the best algorithm anyway? Because “best available” includes a blocking copy. Stating the algorithm you expect makes MySQL fail fast when it cannot deliver it, instead of silently locking the table.

Is a regex-based checker good enough for MySQL? For presence-of-clause rules on simple, one-statement migration files, usually yes. For more complex rules, use a proper MySQL parser or Atlas’s analyzers.

Why one DDL statement per file? MySQL commits each DDL statement implicitly, so a failure partway through a multi-statement file leaves it half-applied. One statement per file makes every migration all-or-nothing.

Does this replace review of MySQL migrations? No. The rules guarantee that each statement asks for online behaviour and that MySQL confirmed it can deliver it; reviewers still judge whether the change belongs in this release and whether running code tolerates it.

What threshold should the large-table rule use? Base it on measured build times: tables whose in-place ALTER would take more than a few minutes, or whose replicas serve reads that cannot tolerate that much lag. Many teams start around 5–10 GB.

Related Articles