Fix 'Waiting for table metadata lock' When ALTER TABLE or DROP TABLE Hangs in MySQL

The situation

You fire off an ALTER TABLE or DROP TABLE and it just... sits there. No error, no progress. SHOW PROCESSLIST confirms what you suspected:

Waiting for table metadata lock

Every query touching that table starts stacking up behind it. The table is effectively offline until whatever holds the lock lets go.

Why this happens

MySQL protects table structure with a metadata lock (MDL). An ALTER TABLE needs an exclusive MDL — but it won't get one until every open transaction on that table finishes. That includes idle transactions sitting half-open that were never committed or rolled back.

Common culprits:

A forgotten SELECT left open in a MySQL client tab — idle for hours, never committed
An app bug where a transaction starts but never commits (common with connection pools configured with autocommit off)
An explicit LOCK TABLES still open in another session
A FLUSH TABLES in progress
In MySQL 5.7+, an idle transaction that merely touched the table still holds the MDL

Step 1 — Find what's blocking

Start with a quick snapshot:

SHOW FULL PROCESSLIST;

Scan for threads in Sleep state with large Time values. Often that's enough to spot the culprit. When it isn't, dig into performance_schema:

-- MySQL 5.6 / 5.7 only — innodb_lock_waits was removed in MySQL 8.0
SELECT
  r.trx_id              AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query           AS waiting_query,
  b.trx_id              AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query           AS blocking_query
FROM
  information_schema.innodb_lock_waits w
  JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
  JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

MDL isn't an InnoDB row lock, so that query often returns nothing. The reliable approach — and the only option on MySQL 8.0+ — is querying performance_schema directly:

-- MySQL 5.7+ / 8.0 — finds who holds the MDL on your table
SELECT
  waiting.THREAD_ID        AS waiting_thread_id,
  waiting.PROCESSLIST_ID   AS waiting_pid,
  waiting.PROCESSLIST_INFO AS waiting_query,
  blocking.THREAD_ID       AS blocking_thread_id,
  blocking.PROCESSLIST_ID  AS blocking_pid,
  blocking.PROCESSLIST_INFO AS blocking_query,
  ml.LOCK_STATUS
FROM
  performance_schema.metadata_locks ml
  JOIN performance_schema.threads blocking
    ON ml.OWNER_THREAD_ID = blocking.THREAD_ID
  JOIN performance_schema.threads waiting
    ON waiting.PROCESSLIST_INFO IS NOT NULL
WHERE
  ml.LOCK_STATUS = 'GRANTED'
  AND ml.OBJECT_SCHEMA = 'your_database'
  AND ml.OBJECT_NAME   = 'your_table';

Swap in your actual database and table names.

Step 2 — Identify the blocking connection

The query above gives you a blocking_pid. Match it in SHOW FULL PROCESSLIST. The offending row usually looks like this:

Command: Sleep
Time: something embarrassingly large — 300, 1800, or even 7200 seconds
Info: NULL — the query finished long ago, but the transaction never closed

That sleeping connection grabbed a metadata lock with its last query and just... stayed open. It has no idea it's blocking your schema change.

Step 3 — Kill the blocker

KILL <blocking_pid>;

For example:

KILL 42;

Your ALTER TABLE should kick in almost immediately. Check the state:

SHOW PROCESSLIST;

Watch for the status to shift from Waiting for table metadata lock to copy to tmp table or altering table. That means you're through.

Step 4 — Verify the fix

-- Confirm no more MDL waiters on this table
SELECT *
FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'your_database'
  AND OBJECT_NAME   = 'your_table';

After the ALTER finishes, this returns zero rows. During execution you'll see one GRANTED row for your own session — that's expected.

Double-check the schema change landed correctly:

DESCRIBE your_table;
SHOW CREATE TABLE your_table\G

What if the blocker isn't in PROCESSLIST?

The connection may have already dropped — crashed client, network cut, whatever — but MySQL never rolled back its transaction. wait_timeout and interactive_timeout will clean it up eventually. On MySQL's default 28800-second (8-hour) setting, though, "eventually" could mean a very long wait.

Force the cleanup by temporarily dropping the timeout:

-- Check current timeout settings
SHOW VARIABLES LIKE '%timeout%';

-- Temporarily shorten to flush stale connections (restore immediately after)
SET GLOBAL wait_timeout = 30;
SET GLOBAL interactive_timeout = 30;

Restore sane defaults once the dead connections clear:

SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

Don't leave this at 30 seconds — it will disconnect legitimate long-running queries.

Prevent it from happening again

Set sane timeouts. A wait_timeout of 60–300 seconds covers most app connections. Eight hours is almost never the right value.
Use autocommit with connection pools. Most ORMs (Laravel, Django, Spring) default to autocommit on. If you've turned it off, every transaction needs an explicit COMMIT or ROLLBACK — no exceptions.
Run DDL during low-traffic windows. On large tables (50M+ rows), use gh-ost or pt-online-schema-change. Both avoid holding an exclusive MDL for the full migration duration.
Add a DDL timeout. Set lock_wait_timeout per session so your ALTER fails fast rather than blocking everything behind it:

SET SESSION lock_wait_timeout = 10;
ALTER TABLE your_table ADD COLUMN new_col INT DEFAULT 0;

Alert on long-running transactions. Wire this into your monitoring. Anything open longer than 60 seconds is worth a look:

SELECT *
FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60
ORDER BY trx_started ASC;

Quick reference

-- 1. Find who's blocking
SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_TIME, PROCESSLIST_STATE, PROCESSLIST_INFO
FROM performance_schema.threads
WHERE PROCESSLIST_STATE IS NOT NULL
ORDER BY PROCESSLIST_TIME DESC
LIMIT 20;

-- 2. Kill the blocker
KILL <pid>;

-- 3. Confirm your ALTER is running
SHOW PROCESSLIST;