MySQL Alter Table DB Freezes

I’m using Ubuntu 16.04.3 and mysql Ver 14.14 Distrib 5.7.22.

When I try to alter my db table column with the following command the database appears to freeze;

mysql> alter table records modify column name varchar(150);

Table size is 2.8MiB (6,000 records).

I’m simply trying to change the varchar(150) part.

I have tried to do the same thing in GUI programs such as HeidiSQL and Workbench, same result – program crashes and I have to restart the session.

I am logged into the db and server as root. I checked the /var/log/mysql/error.log but it only displays failed login attempts, nothing relevant to this error.

Any ideas what’s going on or how I can troubleshoot this?

Answer

Do you have any apps running long-running transactions against this table? If there are any transactions holding a metadata lock, it won’t be able to start the ALTER TABLE.

If you can run SHOW PROCESSLIST in another window while the ALTER TABLE appears to hang, it might show “waiting for metadata lock”.

+----+------+-----------+------+---------+------+---------------------------------+------------------------+-----------+---------------+
| Id | User | Host      | db   | Command | Time | State                           | Info                   | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+------+---------------------------------+------------------------+-----------+---------------+
|  4 | root | localhost | test | Query   |    0 | init                            | show processlist       |         0 |             0 |
|  5 | root | localhost | test | Query   |   15 | Waiting for table metadata lock | alter table foo...     |         0 |             0 |
+----+------+-----------+------+---------+------+---------------------------------+------------------------+-----------+---------------+

Then you need to figure out which app has an open transaction that is blocking the ALTER TABLE, and get that app to finish its transaction, or else kill the transaction.