Dropping table makes MySQL hang

L

3

39

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.

Locust answered 3/6, 2012 at 15:4 Comment(5)

please post the result of SHOW ENGINE INNODB STATUS if the database is under innodb of course – Extramundane 3/6, 2012 at 15:6

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. – Cresol 3/6, 2012 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: bugs.mysql.com/bug.php?id=31937 – Cresol 3/6, 2012 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; – Cresol 3/6, 2012 at 15:20

I have posted the output for these commands. – Locust 3/6, 2012 at 15:32

E

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.

Extramundane answered 3/6, 2012 at 15:28 Comment(5)

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. – Locust 3/6, 2012 at 15:48

which file? I think you should restart the server since the lock lasted a long time. – Extramundane 3/6, 2012 at 15:49

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? – Locust 3/6, 2012 at 15:50

restart your server, and then open a new defect in Stack overflow :-) – Extramundane 3/6, 2012 at 15:51

I solved it by using mysqladmin processlist and mysqladmin kill as shown here. Worked perfectly if your root user has SUPERprivileges or if logging in with the user that owns the blocking process. – Mamey 9/1, 2017 at 14:18

B

26

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.

Backbend answered 24/5, 2019 at 17:16 Comment(0)

E

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