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.

please post the result of SHOW ENGINE INNODB STATUS if the database is under innodb of course — Sebas, Jun 03 '12 at 15:06
looks like the table you are trying to drop is already in use (well, I mean it's been locked by some other process) and hence it's waiting for the process to commit. — Rahul, Jun 03 '12 at 15:13
@cool_cs, which mysql version you are running? This looks like a bug in old version of mysql (mysql version: 4.1.22, 4.1.24). see this: http://bugs.mysql.com/bug.php?id=31937 — Rahul, Jun 03 '12 at 15:15
Can you run this 2 command and pose the result. 1. `show innodb status\G` and 2. `show processlist`. also, `show open tables;` — Rahul, Jun 03 '12 at 15:20

score 26 · Answer 1 · answered May 24 '19 at 17:16

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.

score 23 · Accepted Answer · edited Dec 04 '17 at 18:28

23

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.

edited Dec 04 '17 at 18:28

the

21,007
11
68
101

answered Jun 03 '12 at 15:28

Sebas

21,192
9
55
109

I dropped the table, and now I cannot create a new table as I get the errorcode(28): cannot sync file '..' to disk. I am assuming by dropping the table I would have more free space. – cool_cs Jun 03 '12 at 15:48
which file? I think you should restart the server since the lock lasted a long time. – Sebas Jun 03 '12 at 15:49
1

when i run a really big create table statement with multiple columns i get this error message. by restarting the server do you mean exiting and reconnecting again? how do you explicity restart server? – cool_cs Jun 03 '12 at 15:50
1

restart your server, and then open a new defect in Stack overflow :-) – Sebas Jun 03 '12 at 15:51
I solved it by using `mysqladmin processlist` and `mysqladmin kill` [as shown here](http://dev.mysql.com/doc/refman/5.7/en/kill.html). Worked perfectly if your root user has `SUPER`privileges or if logging in with the user that owns the blocking process. – Jonas Lomholdt Jan 09 '17 at 14:18

score 12 · Answer 3 · answered Sep 04 '15 at 09:51

12

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

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

answered Sep 04 '15 at 09:51

the

21,007
11
68
101

I dont want to drop database because it would remove privileges to that DB. – agilob Apr 22 '18 at 09:16
If it happened to anyone in Windows - run services.msc and restart the MySQL process. – Ran Jun 21 '23 at 17:54

Dropping table makes MySQL hang

3 Answers3

Linked