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 valuesEdit

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:

wait_timeout                   = 120
interactive_timeout            = 120

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

mysql> show global variables like 'wait%';
| Variable_name | Value |
| wait_timeout  | 120   |
1 row in set (0.08 sec)

mysql> show global variables like 'interac%';
| Variable_name       | Value |
| interactive_timeout | 120   |
1 row in set (0.00 sec)

Specifying new interactive_timeout and wait_timeout valuesEdit

To change interactive_timeout and wait_timeout, use:

mysql> set global interactive_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> set global wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

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

Verifying if ALTER TABLE is still hangingEdit

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):

# echo show processlist | mysql | grep ALTER

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 valuesEdit

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