Dropping table makes MySQL hang
Restarting MySQL might not be the prettiest solution but it worked for me:
sudo /etc/init.d/mysql restart
mysqladmin drop YOURDATABASE
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.
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.