故障案例--mysql5.5 myisam引擎出现Waiting for table metadata lock

故障现象

mysql版本为5.5，表是myisam引擎，做ddl时出现了Waiting for table metadata lock

故障原因

理论上myisam表不该出现事务未提交的情况，因为myisam都不支持事务，实际上这应该算是一个bug

https://bugs.mysql.com/bug.php?id=63144

复现方法

session 1

mysql> create table tb3(a int) engine =myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb3;
Empty set (0.00 sec)

session 2 一直卡住

 drop table tb3;

session1查看

mysql> show processlist;
+------+-----------------+------------------+------+---------+--------+---------------------------------+------------------+
| Id   | User            | Host             | db   | Command | Time   | State                           | Info             |
+------+-----------------+------------------+------+---------+--------+---------------------------------+------------------+
|    1 | event_scheduler | localhost        | NULL | Daemon  | 155831 | Waiting on empty queue          | NULL             |
| 5789 | ucloudbackup    | 10.9.79.81:45575 | test | Query   |      0 | NULL                            | show processlist |
| 5794 | ucloudbackup    | 10.9.79.81:45585 | test | Query   |      9 | Waiting for table metadata lock | drop table tb3   |
+------+-----------------+------------------+------+---------+--------+---------------------------------+------------------+
3 rows in set (0.00 sec)

处理方法

session 1上commit或者rollback。或者另外再开一个session3 ，kill掉可疑连接