Dropping table makes MySQL hang

Question

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.

Answer 1

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.

You can use KILL thread_id, in sql_plus.

---

I'm adding further information since I came up with another interesting experience.

Metadata Dead locks may equally happen between a ddl operation on a given table (drop, alter...) and a select query on that table.

Yes, 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.

One solution to this atypical scenario is to release the implicit locks with a commit statement systematically after any select statement is completely fetched.

Answer 2

I'm trying easier answer for newbies as i am:

1) run :

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)

2) KILL 13 (13 coresponding to the Id).

If it's indeed a deadlock, all the following processes will continue normally.