gh-ost error: FATAL cannot acquire lock on table during online schema migration

TL;DR

gh-ost copies rows without locking, but the final table swap needs a brief lock, and something held the table when cutover ran. Find the blocking queries, clear or outwait them, and retry the migration in a quieter window. If cutovers keep colliding with traffic, switch to the two-step cutover mode and lower the lock timeout so attempts fail fast instead of wedging.

The query

gh-ost error: FATAL cannot acquire lock on table during online schema migration

Use this when

• gh-ost fails with "FATAL cannot acquire lock on table"
• The cutover step of an online migration keeps failing
• Long-running queries block the table swap
• An agent's gh-ost run died at the finish line after hours of copying

Not for

• Failures during the initial row copy (check binlog and lag, not locks)
• Cutover succeeding but the app seeing errors (a traffic or trigger problem)
• MySQL lock wait timeouts on ordinary DDL (different tool, different fix)

Steps

1. Find what is holding the table

SHOW FULL PROCESSLIST;

Look for long-running queries touching the target table: analytics queries, open transactions, or a stuck application connection.

Expected output: the blocking session ids and their queries. The usual culprit is a reporting query or an idle-in-transaction connection from a pool.

2. Clear the blocker or wait for a quiet window

Kill the blocking query if it is safe (a repeatable analytics query), or schedule the retry for a low-traffic window. Do not kill a session mid-write on a critical path without the owner's sign-off.

Expected output: no long-running sessions on the target table when the retry starts.

3. Retry with cutover settings that fail fast

gh-ost --cut-over=two-step --cut-over-lock-timeout-seconds=3 [other flags]

The two-step cutover reduces the locked window, and the short lock timeout makes a blocked attempt fail in seconds instead of hanging. Expected output: cutover either succeeds quickly or fails fast with a clear error, never wedges.

4. Watch the retry through cutover

Monitor gh-ost's progress output and the processlist during the final phase. Expected output: row copy completes, cutover acquires the lock, tables swap, and gh-ost exits cleanly. Keep the blocking-query check from step 1 running until the swap is done.

5. Make the next run collision-proof

Bake the quiet-window requirement and the fail-fast flags into the agent's gh-ost runbook, and add a pre-flight check that aborts when long-running queries exist on the target table.

Expected output: future migrations either start clean or refuse to start, instead of copying for hours and dying at cutover.

Tool compatibility

gh-ost 1.1.x against mysql 5.7 and 8.0. Cutover modes and lock-timeout flags are stable.

Variant phrasings

gh-ost FATAL cannot acquire lock on table

Steps 1 through 3: find the blocker, clear it, retry with two-step cutover.

gh-ost cutover lock timeout

Step 3: --cut-over-lock-timeout-seconds makes attempts fail fast; combine with a quiet window.

gh-ost table swap blocked by long query

Step 2: the fix is operational (quiet window), not a flag. Flags only control how gracefully the collision fails.

Why it happens

gh-ost's trick is doing all the heavy work lock-free and needing the lock only for the atomic table rename at the end. But atomic still means a real lock, and MySQL grants it only when no other session holds conflicting locks. A single long query at the wrong moment blocks the swap, and gh-ost treats a blocked cutover as fatal rather than waiting indefinitely.

Edge cases

• Retrying immediately without clearing the blocker just repeats the failure after another full row copy: always fix the blocking workload first.
• The two-step cutover briefly leaves both tables visible: ensure no application logic enumerates tables in a way that breaks during the swap.
• If cutovers always collide, the table may be too hot for any lock-based swap: consider whether the schema change can be done lock-free (e.g. INSTANT algorithms on mysql 8.0) instead.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_ufNyoTqJDdSMCy5iAYXWZw