上周在搞INSERT INTO … SELECT測試的時候，偶然發現一個奇怪的情況：
在insert into t select * from share 運行時， 同時執行alter table t add index(play_count)，
alter table語句會Waiting for table metadata lock， 直到insert into … select 語句結束。
mysql [localhost] {msandbox} (spc) > SHOW processlist;
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+
| Id | USER | Host | db | Command | TIME | State | Info |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+
| 5 | msandbox | localhost | spc | Query | 8 | Waiting FOR TABLE metadata LOCK | ALTER TABLE t ADD INDEX(play_count) |
| 8 | msandbox | localhost | spc | Query | 9 | Sending DATA | INSERT INTO t SELECT * FROM share |
| 10 | msandbox | localhost | spc | Query | 0 | init | SHOW processlist |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+
第一反應：
不是傳說5.6支持online DDL麼？ 怎麼還會Waiting for table metadata lock?
後來想想， online DDL應該是指在alter table進行的時候， 插入/修改/刪除數據的sql語句不會Waiting for table metadata lock.
MySQL 5.6 enhances many other types OF ALTER TABLE operations TO avoid copying the TABLE.
Another enhancement allows SELECT queries AND INSERT, UPDATE, 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 |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+
3 ROWS IN SET (0.00 sec)
這就比較無語了…
好吧，有可能是在這個環境裡面做測試太多….重啟後再試一次：
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 |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------+
3 ROWS IN SET (0.00 sec)
這個sleep的時間。。。已經超過100秒了…
結論：
在准備alter table tbl 的時候，先觀察一下，有沒有正在運行的，且在短時間內無法結束的sql語句在操作tbl表。
