删除表使 MySQL 挂起
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10871388/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dropping table makes MySQL hang
提问by cool_cs
When I try to drop a table, MySQL hangs. I don't have any other open sessions. How to resolve this? I have waited for 10 hours and the process has not terminated.
当我尝试删除表时，MySQL 挂起。我没有任何其他公开会议。如何解决这个问题？我已经等了 10 个小时，该过程还没有终止。
采纳答案by Sebas
Waiting for table metadata lock
drop table tableA name
SELECT l1.lat, l1.lon, l2.zipcode FROM tableA l1, tableBl2 where l1.lat = l2.latitude and l1.lon = l2.longitude limit 10
If this is your table, see this link
如果这是您的桌子，请参阅此链接
you have an implicit deadlock. Kill the other transactions to release the drop, or kill the drop to release the other transactions.
你有一个隐式的死锁。杀死其他事务以释放 drop，或杀死 drop 以释放其他事务。
You can use KILLthread_id, in sql_plus.
您可以在 sql_plus 中使用KILLthread_id。
I'm adding further information since I came up with another interesting experience.
因为我想出了另一个有趣的经历，所以我正在添加更多信息。
MetadataDead locks may equally happen between a ddl operation on a given table (drop, alter...) and a selectquery on that table.
Metadata死锁可能同样发生在给定表 ( drop, alter...)上的 ddl 操作和该表上的选择查询之间。
Yes, select.
是的，select。
So, if you loop over a cursor in mysql (or php, for example with pdo::fetch), and you run a ddl statement on the same table(s), you will get a deadlock.
因此，如果您在 mysql（或 php，例如 with pdo::fetch）中循环游标，并在同一个表上运行 ddl 语句，则会出现死锁。
One solution to this atypical scenario is to release the implicit locks with a commitstatement systematically after any select statement is completely fetched.
这种非典型情况的一种解决方案是在commit完全获取任何 select 语句后系统地使用语句释放隐式锁。
回答by kqw
Restarting MySQL might not be the prettiest solution but it worked for me:
重新启动 MySQL 可能不是最漂亮的解决方案，但它对我有用：
sudo /etc/init.d/mysql restart
mysqladmin drop YOURDATABASE
回答by Golddy
I'm trying easier answer for newbies as i am:
我正在尝试为新手提供更简单的答案，因为我是：
1) run :
1）运行：
SHOW PROCESSLIST
if you get something like:
如果你得到类似的东西：
+----+-----------------+-----------------+--------+------------+-----------+---------------------------------+---------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+--------+------------+-----------+---------------------------------+---------------------------------------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 580410103 | Waiting on empty queue | NULL |
| 13 | root | localhost:50627 | airbnb | Sleep | 10344 | | NULL |
| 17 | root | localhost:50877 | NULL | Query | 2356 | Waiting for table metadata lock | SHOW FULL COLUMNS FROM `airbnb`.`characteristics` |
| 18 | root | localhost:50878 | airbnb | Query | 2366 | Waiting for table metadata lock | DROP TABLE `airbnb`.`characteristics` |
| 21 | root | localhost:51281 | airbnb | Query | 2305 | Waiting for table metadata lock | SHOW FULL COLUMNS FROM `airbnb`.`bed_type` |
| 22 | root | localhost:51282 | airbnb | Query | 2301 | Waiting for table metadata lock | SHOW INDEXES FROM `airbnb`.`characteristics` |
| 23 | root | localhost:51290 | airbnb | Query | 2270 | Waiting for table metadata lock | SHOW FULL COLUMNS FROM `airbnb`.`property_type` |
| 24 | root | localhost:51296 | airbnb | Query | 2240 | Waiting for table metadata lock | SHOW INDEXES FROM `airbnb`.`property_type` |
| 26 | root | localhost:51303 | NULL | Query | 2212 | Waiting for table metadata lock | SHOW FULL COLUMNS FROM `airbnb`.`characteristics` |
| 27 | root | localhost:51304 | NULL | Query | 2218 | Waiting for table metadata lock | SHOW FULL COLUMNS FROM `airbnb`.`bed_type` |
| 29 | root | localhost:51306 | NULL | Query | 2176 | Waiting for table metadata lock | SHOW INDEXES FROM `airbnb`.`characteristics` |
| 30 | root | localhost:51308 | NULL | Query | 2122 | Waiting for table metadata lock | DROP TABLE `airbnb`.`characteristics` |
| 34 | root | localhost:51312 | NULL | Query | 2063 | Waiting for table metadata lock | SHOW FULL COLUMNS FROM `airbnb`.`characteristics` |
| 35 | root | localhost:51313 | NULL | Query | 2066 | Waiting for table metadata lock | SHOW FULL COLUMNS FROM `airbnb`.`bed_type` |
| 39 | root | localhost:51338 | NULL | Query | 2004 | Waiting for table metadata lock | SHOW FULL COLUMNS FROM `airbnb`.`characteristics` |
| 40 | root | localhost:51339 | NULL | Query | 2008 | Waiting for table metadata lock | SHOW FULL COLUMNS FROM `airbnb`.`bed_type` |
| 45 | root | localhost | airbnb | Field List | 997 | Waiting for table metadata lock | |
| 46 | root | localhost | airbnb | Field List | 798 | Waiting for table metadata lock | |
| 53 | root | localhost | airbnb | Query | 0 | starting | SHOW PROCESSLIST |
+----+-----------------+-----------------+--------+------------+-----------+---------------------------------+---------------------------------------------------+
with State : waiting for table metadata lock (as mentioned in official answer)
with State：等待表元数据锁（如官方回答中所述）
2) KILL 13(13 coresponding to the Id).
2) KILL 13(13 对应于 Id)。
If it's indeed a deadlock, all the following processes will continue normally.
如果确实是死锁，那么后面的所有过程都会正常进行。
