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:
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

[16 Aug 2012 8:57] Besh Mag
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;

Trx id counter 64901
Purge done for trx's n:o < 64711 undo n:o < 0
History list length 38
---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


How to repeat:
We can not repeat this.
[18 Aug 2012 20:33] Valeriy Kravchuk
Please, check if the same problem ever happens with a newer version, 5.5.27.
[30 Aug 2012 17:33] Michael Morse
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..
[30 Aug 2012 20:19] Besh Mag
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.
[6 Sep 2012 18:55] Sveta Smirnova
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.
[11 Mar 2013 19:30] Chris Calender
FLUSH TABLES is a nice work-around to eliminate the metadata lock while you troubleshoot where it is originating from.