MySQL: Slow Drop table command

Question

I'm trying to use

DROP TABLE IF EXISTS <myTableName>;

however even after several hours it is still running. Any suggestions for a workaround?

Answer 1

A better way:

Kill all processes that are in the way of a DROP operation

Suppose you want to DROP a table table in database database

1) Login to mysql in a parallel session, preferably as root. issue:

SHOW PROCESSLIST;

you will see a list of all processes on the db^*:

+-------+-----------+-----------+----------+---------+------+---------------------------------+------------------------------+----------+
| Id    | User      | Host      | db       | Command | Time | State                           | Info                         | Progress |
+-------+-----------+-----------+----------+---------+------+---------------------------------+------------------------------+----------+
| 38239 | username  | 10.0.0.1  | database | Sleep   |  591 |                                 | NULL                         |    0.000 |
| 38240 | username  | 10.0.0.1  | database | Sleep   |  590 |                                 | NULL                         |    0.000 |
| 38245 | username  | 10.0.0.1  | database | Query   | 2636 | Waiting for table metadata lock | DROP TABLE IF EXISTS `table` |    0.000 |
| 38257 | username  | localhost | database | Query   |    0 | init                            | SHOW PROCESSLIST             |    0.000 |
+-------+-----------+-----------+----------+---------+------+-------+---------------------------------+----------------------+----------+

2) If there are any active processes before the DROP, you should wait for them to finish (or kill them, if you are sure).

3) Then kill all processes that "Sleep" before your waiting one:

KILL 38239;
KILL 38240;

4) After that, the blocked process should proceed with the DROP operation.

Waiting before DROP is a known issue in InnoDB.

---

Update: Also noticed this behavior on non-exclusive updates (updates where number of affected records may vary), such as:

# may update any number of customers, from 0 to all
UPDATE customers SET `active` = 'N' WHERE `last-payment-date` < 2018-01-01;

Solution is exactly the same.

---

• _{The table provided is taken from a real life scenario, data is obfuscated and slightly edited for security and presentation purposes.}

Answer 2

I had the same issue and it just solved by restarting the server:

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

Maybe some lock was being held and the restart released it.