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.
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:
- Wait for the other session to complete its operation and then try executing the
ALTER TABLEstatement again. - Use the
SHOW FULL PROCESSLISTcommand 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 theKILLcommand. - Use the
ALTER TABLEstatement with theONLINEoption, which allows the table to remain available for read and write operations while theALTER TABLEstatement is executing. This option is available in MySQL 8.0 and later. - 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, andRENAME TABLEstatements.
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.