MySQL ALTER TABLE hangs
I got to this question by searching "mysql alter table hanging" and think the answer (in the comments) should be documented.
If you're running a MySQL command (in this case to add a column) and it is unexpectedly hanging, e.g. not due to the size of the data in the table, then check the other processes running against the database. I left the query running, connected to the server with another client, and typed
SHOW PROCESSLIST;
My query was hanging, and this message was in the state
column:
Waiting for table metadata lock
I noticed I had several zombie connections from my machine, killed them (KILL xxx
where xxx
is the number in the Id
column), and then the process finished immediately.
I had the same problem on a Galera Cluster (MariaDB) of a production srver, the freeze was due to an alter table add column with a default value, I could see withing SHOW PROCESSLIST
I shut down the servers to force kill the process, but I had much work to put the cluster on again.
It happened the second time, all I did is waited for the query to finish it took about 20 minutes because it had to deal with a 1.5M lines table. then the tables lock is open.