MySQL ALTER TABLE hangs

Question

I know this question was asked several times already but my problem is happening with a table I've just created. It has only 10 columns and only 1 row in it.

So unlike the usual hanging problem this is not the case of a large table with a lot of data. And yet it hangs.

This is the SQL I'm running:

ALTER TABLE `db`.`Search` 
ADD COLUMN `useremail` VARCHAR(256) NOT NULL COMMENT '' AFTER `id`;

Simple enough and yet it hangs (50 minutes and counting.) I tried quitting Workbench and Eclipse and deleting all java processes and the one mysqld process. But no luck.

I'm running on a Mac OSX El Capitain. How do I add this column to my table?

Answer 1

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.

Answer 2

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.