MySQL Online Schema Change skill for Codex

Done well means: the cheapest safe mechanism was chosen and forced explicitly (so MySQL errors instead of silently copying the table), no statement ever waits in the metadata lock queue long enough to stall traffic, replicas stay within their lag budget, and the cut-over happens at a moment the user chose.

Inspect first

1. Read AGENTS.md and the migration tool (Rails, Laravel, Flyway, Liquibase, Atlas, plain SQL) to see how DDL reaches production and whether it runs inside deploys.
2. Gather facts about the table and server (read-only):

SELECT VERSION(), @@binlog_format, @@binlog_row_image, @@innodb_online_alter_log_max_size;
SELECT table_rows, round((data_length + index_length) / 1024 / 1024 / 1024, 1) AS size_gb
FROM information_schema.tables WHERE table_schema = 'app' AND table_name = 'orders';
SHOW CREATE TABLE app.orders\G                    -- foreign keys? triggers? partitions?
SELECT name, total_row_versions FROM information_schema.innodb_tables
WHERE name = 'app/orders';                        -- instant-change budget (8.0.29+)
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 5;

3. Note hosting: self-managed, RDS/Aurora, Cloud SQL, PlanetScale/Vitess. It limits tools.
4. Check free disk: anything that copies the table needs at least its size plus indexes free, plus binlog growth.

Choose the mechanism

Change	First choice	Notes
Add nullable column or column with constant default (8.0.29+: any position)	ALGORITHM=INSTANT	Metadata only; seconds regardless of size
Drop column (8.0.29+), rename column, change default, add ENUM/SET values at end	ALGORITHM=INSTANT	Max 64 row versions per table, then a rebuild is required
Add or drop secondary index, rename index	ALGORITHM=INPLACE, LOCK=NONE	Online, but replicas replay it serially afterwards
Extend VARCHAR within the same length-byte range (for example 100 to 200)	ALGORITHM=INPLACE, LOCK=NONE	Crossing 255 bytes forces a copy
Change column type, charset, primary key on a big table	gh-ost	Copy-based; online with throttling
Table has foreign keys that gh-ost cannot handle	pt-online-schema-change	Trigger-based; --alter-foreign-keys-method
PlanetScale or Vitess	Deploy request / Vitess online DDL	Never raw DDL on production branch
Small table (under about 1 GB, quiet)	Plain ALTER with explicit algorithm and lock_wait_timeout	Still guard the MDL

Always state the algorithm and lock. Without them, MySQL picks the best available, which may be COPY with writes blocked:

ALTER TABLE orders ADD COLUMN gift_note VARCHAR(255) NULL, ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

If MySQL answers “ALGORITHM=INSTANT is not supported for this operation”, stop and move down the table; do not drop the clause to make it pass.

The metadata lock trap

Every ALTER, even INSTANT, needs an exclusive metadata lock briefly. If any transaction has touched the table and not committed, the ALTER waits, and every new query on the table queues behind the waiting ALTER. A long-running report can take the site down through a one-second DDL.

SET SESSION lock_wait_timeout = 5;    -- give up fast instead of stalling traffic
ALTER TABLE orders ADD COLUMN gift_note VARCHAR(255) NULL, ALGORITHM=INSTANT;

• Check information_schema.innodb_trx and sys.schema_table_lock_waits before running.
• Retry with backoff when it times out; do not raise the timeout.
• Migration frameworks: set lock_wait_timeout in the migration’s session (Rails: execute "SET SESSION lock_wait_timeout = 5" at the top of the migration).

gh-ost

Triggerless: copies rows in chunks and tails the binlog for changes. Requires binlog_format=ROW and binlog_row_image=FULL. Does not support tables with foreign keys. Downloading the binary needs network access; on a server it is usually already installed.

gh-ost \
  --host=replica-1.internal --database=app --table=orders \
  --user=ghost --password="$GHOST_PASS" \
  --alter="MODIFY COLUMN total_cents BIGINT NOT NULL" \
  --chunk-size=1000 \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=200 \
  --max-lag-millis=1500 \
  --throttle-control-replicas=replica-2.internal \
  --postpone-cut-over-flag-file=/tmp/ghost-orders.postpone \
  --serve-socket-file=/tmp/ghost-orders.sock \
  --exact-rowcount --concurrent-rowcount \
  --initially-drop-ghost-table \
  --verbose
  # no --execute: dry run (noop). Add --execute only after the user approves.

• It connects to a replica by default and detects the primary from it. On RDS and Aurora it usually runs against the writer: add --allow-on-master, plus --assume-rbr because it cannot change binlog_format itself there (set it in the parameter group).
• touch the postpone flag file before starting; the copy runs and the cut-over waits.
• Control a running migration through the socket:

echo status | nc -U /tmp/ghost-orders.sock
echo throttle | nc -U /tmp/ghost-orders.sock         # pause during a traffic spike
echo no-throttle | nc -U /tmp/ghost-orders.sock
echo "chunk-size=500" | nc -U /tmp/ghost-orders.sock

• Cut over in a quiet window, with the user’s go-ahead: rm /tmp/ghost-orders.postpone.
• Afterwards _orders_del holds the old table. Keep it until verified; dropping it is irreversible and on large tables can stall I/O, so confirm with the user and drop during low traffic.

pt-online-schema-change

Trigger-based: creates _orders_new, adds triggers to mirror writes, copies in chunks, then swaps with RENAME TABLE. Triggers add write overhead on the source table.

pt-online-schema-change \
  --alter "ADD INDEX idx_status_created (status, created_at)" \
  D=app,t=orders,h=primary.internal,u=osc \
  --ask-pass \
  --chunk-time 0.5 \
  --max-load Threads_running=25 --critical-load Threads_running=200 \
  --max-lag 2 --check-interval 1 \
  --alter-foreign-keys-method auto \
  --no-drop-old-table \
  --dry-run          # then the same command with --execute after approval

• --alter-foreign-keys-method: rebuild_constraints rewrites child tables (safe, slow for big children); drop_swap briefly leaves the table missing; auto chooses. Read the tool docs before using anything other than auto or rebuild_constraints.
• Replica discovery uses SHOW PROCESSLIST by default; when replicas are not visible, give --recursion-method=dsn=... so lag throttling works.
• Tables that already have triggers need --preserve-triggers.

PlanetScale and Vitess

On PlanetScale, production branches with safe migrations enabled reject direct DDL; changes go through branches and deploy requests that Vitess applies online.

pscale branch create app add-gift-note
pscale shell app add-gift-note                    # apply DDL on the dev branch
pscale deploy-request create app add-gift-note
pscale deploy-request diff app 42                 # review the schema diff
pscale deploy-request deploy app 42               # needs user approval: changes production

• Deploy requests can be configured to wait for a manual apply before the cut-over; use that for large tables so the swap happens in a chosen window.
• After deploy there is a limited revert window that restores the old schema while keeping rows written meanwhile: pscale deploy-request revert app 42. Check the current window length in PlanetScale’s docs.
• Self-managed Vitess: SET @@ddl_strategy = 'vitess --postpone-completion'; then the ALTER, monitor with SHOW VITESS_MIGRATIONS, finish with ALTER VITESS_MIGRATION '<uuid>' COMPLETE.

Workflow

- [ ] 1. Inspect table, server, FKs, triggers, disk, long transactions
- [ ] 2. Pick mechanism from the table; write the exact command
- [ ] 3. Dry run (gh-ost without --execute, pt-osc --dry-run, deploy request diff)
- [ ] 4. Estimate duration from rows and chunk rate; agree a window with the user
- [ ] 5. Get explicit approval for the production run
- [ ] 6. Run with postponed cut-over; watch lag, Threads_running, p95 latency
- [ ] 7. Cut over in the agreed window after a second confirmation
- [ ] 8. Verify; keep the old table until the user approves dropping it

Verification

• SHOW CREATE TABLE shows the intended definition; the migration framework’s schema file matches it.
• Row counts of old and new tables match at cut-over (gh-ost logs this; otherwise SELECT COUNT(*) on both during a quiet moment).
• No metadata lock waits in sys.schema_table_lock_waits during the run.
• Replica lag stayed under --max-lag-millis / --max-lag; application error rate flat.

Gotchas

• Instant ADD COLUMN on 8.0.12-8.0.28 only appends at the end; AFTER col forces a rebuild.
• Each instant change uses a row version; at 64 the next one needs a full rebuild (a gh-ost or pt-osc run with --alter="ENGINE=InnoDB" resets the count online).
• In-place DDL on the primary is online, but the replica replays it after it finishes, so replica lag equals the DDL duration. gh-ost and pt-osc avoid this.
• innodb_online_alter_log_max_size too small aborts an INPLACE ALTER on busy tables after hours of work.
• Renaming a column the running application still reads breaks it; use expand and contract across deploys ($zero-downtime-migrations).
• MariaDB differs: its instant DDL rules and ALTER ONLINE syntax are not identical to MySQL 8; check its docs.

Output template

**Table:** app.orders, 410M rows, 186 GB, 2 FKs inbound, no triggers
**Change:** total_cents INT -> BIGINT (needs copy)
**Mechanism:** pt-online-schema-change (gh-ost excluded: foreign keys), rebuild_constraints
**Guards:** chunk-time 0.5 s, max-load Threads_running=25, max-lag 2 s, lock_wait_timeout 5 s
**Estimate:** about 9 h; window Sat 01:00-12:00 UTC (approved)
**Result:** copied 410.2M rows; max lag 1.4 s; cut-over 03:12 at 0.8 s; old table kept
**Pending approval:** drop _orders_old after 7 days