mysql出现Waiting for table metadata lock的原因及解决方案(二)
AND DELETE (DML) statements TO proceed while the TABLE IS being altered.
This combination OF features IS now known AS online DDL.
那么就让alter table wait去吧。
后来又发现另外一个神奇的事：
mysql [localhost] {msandbox} (spc) > SHOW processlist;
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+
| Id | USER | Host | db | Command | TIME | State | Info |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+
| 5 | msandbox | localhost | spc | Query | 1 | Waiting FOR TABLE metadata LOCK | ALTER TABLE t ADD INDEX(play_count) |
| 8 | msandbox | localhost | spc | Query | 3 | USER sleep | SELECT sleep(100) FROM t |
| 10 | msandbox | localhost | spc | Query | 0 | init | SHOW processlist |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+
重启后再试一次：
mysql [localhost] {msandbox} (spc) > SHOW processlist;
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+
| Id | USER | Host | db | Command | TIME | State | Info |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+
| 1 | msandbox | localhost | spc | Query | 129 | USER sleep | SELECT sleep(100) FROM t |
| 2 | msandbox | localhost | spc | Query | 102 | Waiting FOR TABLE metadata LOCK | ALTER TABLE t DROP INDEX play_count |
| 3 | msandbox | localhost | spc | Query | 0 | init | SHOW processlist |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+
这个sleep的时间。。。已经超过100秒了…
结论：
在准备alter table tbl 的时候，先观察一下，有没有正在运行的，且在短时间内无法结束的sql语句在操作tbl表