Single connection on mysql "Waiting for table metadata lock" to DROP INDEX

Question

My MySQL server went on a unexpected state. I had one single connection executing a query and it stayed in "Waiting for table metadata lock" for a long time (over 1643 secs). The query was a DROP INDEX on a very busy table within my system.

Actually I've tried the command many times. Firstly the database was busy and there were other connections performing multiple operations (both read an write). I thought this could be the reason, so I've tried in sequence:

1. kill any process running Query on the same table and on the same "Waiting for table metadata lock"
2. Kill even processes with Sleep command
3. Remove every process and any grants from remote hosts (after this only root could connect and no other user was connected, the application it self was disconnected from the database)
4. Cancel and re-run the command (reinforcing this would be the only active command)

And even in that state the problem persisted for minutes. The only alive process query was:

     ID: 2398884
   USER: root
   HOST: localhost
     DB: zoom
COMMAND: Query
   TIME: 1643
  STATE: Waiting for table metadata lock
   INFO: DROP INDEX index_x ON tb.schema

Afterwards we decided to restart mysqld. And when the server got back the issue was gone. I was able run the drop index command.

I haven't been able to spot anyone with a similar scenario. Is this normal on some circumstances? I've tried to find which transaction is causing a “Waiting for table metadata lock”. and was not able to identify any one.

Note: Besides the drop index and my own root connection to inspect progress and status there were Binlog Dump replication queries running

Answer 1

No, this is not normal, and I'm sure you just haven't killed the right thread. A restart of MySQL should not be necessary. If it were, me and the company I work for would be the first to abandon it.

A metadata lock happens, when one transaction touches a table and another transaction (your drop index statement) wants to have a lock, but the first transaction isn't committed yet. Sounds too common, but play it through:

session1 > start transaction;
session1 > select * from foo;

That's what I mean with "touching". A simple select is enough and it can happen anywhere in the transaction. It doesn't matter, if you run no more statements after that or if you run another statement (as long as it's not a commit; or rollback;), this transaction prevents other transactions to get the lock for metadata.

session2 > alter table foo add column bar int;

Now session2 is waiting for the metadata lock.

Regarding what you tried:

1. What you have to kill must not necessarily be a transaction that is currently running statements on the same table. Killing other statements that are also waiting for metadata lock doesn't help, they are also just victims. But it doesn't hurt either.
2. Not a bad idea.
3. Not sure what you mean about that. But removing grants surely doesn't help. New grants or removed grants don't apply to transactions that are still running. A session has to be reopened for changed grants to take effect.
4. This doesn't help at all.

That being said, I surely don't understand why the accepted answer in your linked question has more than 100 upvotes. Those queries do indeed not show the locks at all. The second answer is right, though. Kill the transactions that are running the longest time first.
Note though, you have to check the ACTIVE x seconds parts in the output of SHOW ENGINE INNODB STATUS\G in the TRANSACTIONS section. Do not use the time value in the processlist. This only indicates the time since the last status change of this thread.

• read more about metadata locks here

Oh, and also make sure to read this if you're using MySQL 5.7 or newer.