MySQL: avoiding "Waiting for table metadata lock" when executing "ALTER TABLE"

If you're running a busy database, the chances are that a ALTER TABLE statement executed will not really work, you will end up with MySQL processlist full of "Waiting for table metadata lock" - including your original ALTER TABLE statement, MySQL processlimit will be reached, and your website/system will no longer work.

In short, this is because MySQL will not ALTER the table if it's in use. Even if you don't see any processes using your particular table (in SHOW PROCESSLIST / SHOW FULL PROCESSLIST) - MySQL may still not ALTER your table and do "Waiting for table metadata lock" - because there are some connections made to the database which didn't disconnect yet.

To increase the likelihood of executing your ALTER TABLE statement without MySQL restart or doing a schedule maintenance, you should substantially lower the values for interactive_timeout and wait_timeout. These values can be changed dynamically - no database restart is needed.

The "Waiting for table metadata lock" error occurs when a table is locked by another session and you are trying to execute an ALTER TABLE statement on it. This can happen when the table is being accessed by another thread or is being modified by another ALTER TABLE statement.

To fix this error, you can try the following:

  1. Wait for the other session to complete its operation and then try executing the ALTER TABLE statement again.
  2. Use the SHOW FULL PROCESSLIST command to see what queries are currently running on the MySQL server and determine which session is holding the lock on the table. You can then try to kill the session using the KILL command.
  3. Use the ALTER TABLE statement with the ONLINE option, which allows the table to remain available for read and write operations while the ALTER TABLE statement is executing. This option is available in MySQL 8.0 and later.
  4. If the table is heavily used and you do not want to disrupt access to it, you can create a new table with the desired structure, copy the data from the old table to the new table, and then rename the new table to the old table name. This can be done using the CREATE TABLE, INSERT INTO SELECT, and RENAME TABLE statements.

By following these steps, you should be able to successfully execute the ALTER TABLE statement without encountering the "Waiting for table metadata lock" error.

Checking current interactive_timeout and wait_timeout values

Default value for both interactive_timeout and wait_timeout is 28800 seconds.

They can be overridden in MySQL config file (my.cnf) for example with:

If you're not sure what they currently are, you can always check it with:

Specifying new interactive_timeout and wait_timeout values

To change interactive_timeout and wait_timeout, use:

After that, in most cases, your ALTER TABLE query will execute without getting "Waiting for table metadata lock".

Verifying if ALTER TABLE is still hanging

Above, we've set both interactive_timeout and wait_timeout to 10 seconds. In short, this should disconnect all clients no longer sending queries after 10 seconds.

Therefore, if you execute it in bash (we only show ALTER queries):

You should see if it has "Waiting for table metadata lock" or not. Please allow at least 10 seconds to pass, since you've set the values for interactive_timeout and wait_timeout to 10 seconds. If you no longer see "Waiting for table metadata lock" - good luck, it should finish soon (assuming that the table you're changing is not very large, i.e. gigabytes or more).

Changing back interactive_timeout and wait_timeout values

When your ALTER TABLE query is finished, remember to set interactive_timeout and wait_timeout values to what they previously were.

Great! You’ve successfully signed up.

Welcome back! You've successfully signed in.

You've successfully subscribed to

Success! Check your email for magic link to sign-in.

Success! Your billing info has been updated.

Your billing was not updated.