2

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_c2enter image description here

  • Waiting for metadata lock is entirely expected while an ALTER TABLE is in progress that is doing a copy to temp table... the table is locked while the copy operation is happening. So the only question here is why this change isn't happening INPLACE. – Michael - sqlbot May 27 '16 at 13:53
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.

  • After using algorithm=inplace to force INPLACE method, the warnings: | Level | Code | Message | | Note | 1880 | TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format. | | Error | 1846 | ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. | My mysql version is 5.6.16-log. This chapter seems to explain clearly. – seanlook May 29 '16 at 10:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.