MySQL：永久获取“等待表元数据锁定”

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数据库提供三个webapps作为存储后端。但是我最近遇到了错误“等待表元数据锁”。它几乎一直发生，我不明白为什么。

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.

当然可以杀死相应的进程。但是，如果我重新启动尝试创建数据库“bookmaker3”的表结构的程序，则新创建的进程将再次在metalock中结束。

I even cannot drop the database:

我甚至无法删除数据库：

mysql> drop database bookmaker3;

This yields also a metalock.

这也产生了金属。

How can this be repaired?

怎么修这个呢？

2 个解决方案

#1

Kill the connection with lock

使用锁定杀死连接

Kill 1398

Then check if you have autocommit set to 0 by

然后检查是否已将自动提交设置为0

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.

在您的情况下：如果您对LogEntries（存在whitch）进行了一些查询并且没有提交它，那么您尝试执行CREATE TABLE IF NOT EXISTS来自另一个连接 - 发生元数据锁定。

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

编辑对我来说，错误是在你的应用程序的某个地方。检查那里，或者如果您未在申请中使用交易，请将自动提交设置为1。

ps also check this posts:

ps也查看这篇文章：

not a bug on bugs.mysql
不是bugs.mysql上的错误
replication of lock on MyISAM and INNOdb tables
在MyISAM和INNOdb表上复制锁

#2

In case if you have HS plugin and trying to CREATE or ALTER table which was already attempted to assess via HS you will face with similar problem and you have to restart HS plugin in this way to release table metadata lock:

如果您有HS插件并尝试CREATE或ALTER表已经尝试通过HS评估，您将面临类似的问题，您必须以这种方式重新启动HS插件以释放表元数据锁：

UNINSTALL PLUGIN HANDLERSOCKET;
INSTALL PLUGIN HANDLERSOCKET SONAME 'handlersocket.so';

#1