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

Restarting MySQL might not be the prettiest solution but it worked for me:

sudo /etc/init.d/mysql restart
mysqladmin drop YOURDATABASE

Answer 2

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.