MySQL 5.6 Online DDL lead to to much table-metadata-locks when I just add index

Question

It's normal that table-metadata-lock shows up for a short time when ALTER TABLE t add index ... starts and ends. But I have encountered all DML operations blocked in Waiting for table metadata lock, and I'm sure there is no long sql statement running before adding index.

What's more confused is that I saw the ALTER statement copying to tmp table in processlist. According to official docs innodb-create-index-overview, INPLACE method should be taken by default, right? ( I just add one secondary index and delete one).

Also I checked information_schema.innodb_trx to see whether uncommitted transactions exist. No.

Can someone could explain why this happens for me?

By the way, my (alter) table t1 has triggers on it. And there's trigger on other table t2 to update t1. Thought I know it's ugly ·_·

show processlist after alter table t1 add key idx_c1_c2(c1,c2), drop key idx_c2：

Answer 1

There are good reasons ALGORITHM=INPLACE may not be used and switches to ALGORITHM=COPY.

REASON #1

In the link you gave in the question, there is a chart that shows the following:

• Adding a FULLTEXT index trigger a table copy is not supported for online operation
• Many operations can revert to copy because it says in the notes Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.

Some online DDL may still lock and copy because of an index's interaction. Don't forget that each BTREE entry in a secondary index has a hook back to the primary key. Adding a new secondary index may induce row level locks and index locks.

REASON #2

According the limitations page : InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY.

I am sure there are other reasons that can be researched an discussed along these lines.