Guard MySQL Online DDL Changes

Check whether an ALTER can use INPLACE or INSTANT, set lock and timeout limits, and use a shadow-table tool for changes that would otherwise block writes.

~40 minmysqlmigrationszero-downtime

Task

Make MySQL schema changes safe by choosing algorithms that avoid long table locks.

When to use this

An ALTER runs with the wrong algorithm and locks the table for minutes, stalling every write and piling up connections.

Prerequisites

MySQL 5.7+ with online DDL support
Access to run DDL in a maintenance window

Steps

1. Inspect whether the change supports INSTANT or INPLACE with the current version.

Expected output: You know the expected locking behavior before running.

2. Specify ALGORITHM and LOCK explicitly so the server fails rather than silently locking.

Expected output: A plan that cannot run online is rejected early.

3. Set a lock_wait_timeout so DDL does not queue behind a long transaction forever.

Expected output: The DDL gives up instead of piling up.

4. For non-online changes, use a shadow-table tool that copies and swaps.

mysql -e "ALTER TABLE t ADD COLUMN c int, ALGORITHM=INSTANT, LOCK=NONE;"

Expected output: Writes continue on the original table during the copy.

Troubleshooting

Problem

ALGORITHM=INPLACE fails unexpectedly

Solution

Not every change supports online DDL; fall back to a shadow-table tool rather than dropping the guard.

Problem

DDL stalls behind a transaction

Solution

Check for long-running transactions first and set lock_wait_timeout to a small value.

Limitations

Online DDL still consumes CPU and IO and can affect replication lag even when it does not lock writes.
Some changes, such as changing the primary key type, are never fully online and need a tool.

Context

Make MySQL schema changes safe by choosing algorithms that avoid long table locks. An ALTER runs with the wrong algorithm and locks the table for minutes, stalling every write and piling up connections.

Steps

Inspect whether the change supports INSTANT or INPLACE with the current version. Expected: You know the expected locking behavior before running.
Specify ALGORITHM and LOCK explicitly so the server fails rather than silently locking. Expected: A plan that cannot run online is rejected early.
Set a lock_wait_timeout so DDL does not queue behind a long transaction forever. Expected: The DDL gives up instead of piling up.
For non-online changes, use a shadow-table tool that copies and swaps. Expected: Writes continue on the original table during the copy.

ALTER TABLE orders
  ADD COLUMN channel varchar(32) NULL,
  ALGORITHM=INSTANT, LOCK=NONE;
-- if this errors, use an online schema-change tool

Verify

You know the expected locking behavior before running.
A plan that cannot run online is rejected early.
The DDL gives up instead of piling up.
Writes continue on the original table during the copy.

Limits

Online DDL still consumes CPU and IO and can affect replication lag even when it does not lock writes.
Some changes, such as changing the primary key type, are never fully online and need a tool.