MySQL Bugs: #66416: DDL hangs on "Waiting for table metadata lock" even after database restart.

Bug #66416	DDL hangs on "Waiting for table metadata lock" even after database restart.
Submitted:	16 Aug 2012 8:57	Modified:	6 Sep 2012 18:55
Reporter:	Besh Mag	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.5.16	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	"Waiting for table metadata lock" DDL

Description:
For some tables, We can not do any DDL on them. The DDL command either drop,alter table, or truncate waits indefinitely on "Waiting for table metadata lock".
We attempt to restart the database several times, but that did not make any difference.

mysql> show processlist;
+----+-------------+-----------+----------+---------+---------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db       | Command | Time    | State                                                                       | Info             |
+----+-------------+-----------+----------+---------+---------+-----------------------------------------------------------------------------+------------------+
|  1 | system user |           | NULL     | Connect |     459 | Connecting to master                                                        | NULL             |
|  2 | system user |           | NULL     | Connect | 4726438 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  3 | root        | localhost | bmohareb | Query   |       0 | NULL                                                                        | show processlist |
|  4 | root        | localhost | bmohareb | Query   |       3 | Waiting for table metadata lock                                             | drop table tab1  |
+----+-------------+-----------+----------+---------+---------+-----------------------------------------------------------------------------+------------------+

mysql> show engine innodb status \G;

------------
TRANSACTIONS
------------
Trx id counter 64901
Purge done for trx's n:o < 64711 undo n:o < 0
History list length 38
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x40eae940, query id 5600 localhost root
show engine innodb status
---TRANSACTION 64900, ACTIVE 499 sec
5 lock struct(s), heap size 1248, 0 row lock(s), undo log entries 5570
MySQL thread id 2, OS thread handle 0x4a6bf940, query id 5572 Slave has read all relay log; waiting for the slave I/O thread to update it
--------
FILE I/O
--------

.

How to repeat:
We can not repeat this.

Please, check if the same problem ever happens with a newer version, 5.5.27.

are any foreign keys constraints pointing towards these tables and/or have you made any changes to foreign keys related to the table? Not to say this is the cause, but we are investigating a similar issue and we are looking at this as a possibility as we were making changes to foreign key definitions when this was noticed..

It appeared that the problem is much simpler. We had this database as a slave, and the slave seems that it failed in the middle of a transaction, so it kept locking these objects, even when we restarted, these objects' metadata kept locked, after we stopped the slave, we can drop the tables.

Thank you for the feedback.

Closed as "Not a Bug", because this is usage issue (DDL waits a lock for 1 year by default), not MySQL bug.

FLUSH TABLES is a nice work-around to eliminate the metadata lock while you troubleshoot where it is originating from.