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.

Tags:

Mysql