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.

Checking current interactive_timeout and wait_timeout values

Default value for both interactive_timeout and wait_timeout is 28800 seconds.

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.