process - MySQL : Permanently getting " Waiting for table metadata lock"

Question

My MySQL database serves three webapps as the storage backend. However I recently encounter permanantly the error "Waiting for table metadata lock". It happen nearly all the time and I do not understand why.

mysql> show processlist
    -> ;
+------+-----------+-----------------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id   | User      | Host            | db         | Command | Time | State                           | Info                                                                                                 |
+------+-----------+-----------------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
|   36 | root      | localhost:33444 | bookmaker2 | Sleep   |  139 |                                 | NULL                                                                                                 |
|   37 | root      | localhost:33445 | bookmaker2 | Sleep   |  139 |                                 | NULL                                                                                                 |
|   38 | root      | localhost:33446 | bookmaker2 | Sleep   |  139 |                                 | NULL                                                                                                 |
|   39 | root      | localhost:33447 | bookmaker2 | Sleep   |   49 |                                 | NULL                                                                                                 |
|   40 | root      | localhost:33448 | bookmaker2 | Sleep   |  139 |                                 | NULL                                                                                                 |
| 1315 | bookmaker | localhost:34869 | bookmaker  | Sleep   |   58 |                                 | NULL                                                                                                 |
| 1316 | root      | localhost:34874 | bookmaker3 | Sleep   |   56 |                                 | NULL                                                                                                 |
| 1395 | bookmaker | localhost:34953 | bookmaker  | Sleep   |   58 |                                 | NULL                                                                                                 |
| 1396 | root      | localhost:34954 | bookmaker3 | Sleep   |   46 |                                 | NULL                                                                                                 |
| 1398 | root      | localhost:34956 | bookmaker3 | Query   |   28 | Waiting for table metadata lock | CREATE TABLE IF NOT EXISTS LogEntries  ( 
                    lid         INT NOT NULL AUTO_INCREMEN |
| 1399 | root      | localhost       | NULL       | Query   |    0 | NULL                            | show processlist                                                                                     |
+------+-----------+-----------------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+

Of course one can kill the corresponding process. However if I restart my program that tries to create the table structure of my database "bookmaker3" the newly created process ends up again in a metalock.

I even cannot drop the database:

mysql> drop database bookmaker3;

This yields also a metalock.

How can this be repaired?

Answer 1

Kill the connection with lock

Kill 1398

Then check if you have autocommit set to 0 by

select @@autocommit;

If yes, you propably forgot to commit transaction. Then another connection want to do something with this table, which causes the lock.

In your case: If you made some query to LogEntries (whitch exists) and did not commit it, then you try to execute CREATE TABLE IF NOT EXISTS from another connection - metadata lock happens.

edit For me the bug is somewhere at your application. Check there, or set autocommit to 1 if your not using transactions in application.

ps also check this posts:

• not a bug on bugs.mysql
• replication of lock on MyISAM and INNOdb tables