unable to drop database in mysql

Question

When I try to drop a database it hangs up. The processlist shows:

20045 | root | localhost | NULL | Query | 4 | Waiting for table metadata lock | drop database dbname1

There is no foreign key in entire database and there is no locking. Below are the details for the same:

mysql>
mysql> select * from INNODB_LOCKS ;
Empty set (0.00 sec)

mysql> select * from INNODB_LOCK_WAITS ;
Empty set (0.01 sec)

I don't know why this happens, anyone has any ideas on the issue? I am using MySQL 5.5.31-0ubuntu0.12.04.1-log (Ubuntu).

check this link: http://stackoverflow.com/questions/10871388/dropping-table-makes-mysql-hang — user1502952, Aug 27 '13 at 12:14

smrati katiyar · Answer 1 · 2013-08-27T12:28:46.137

7

If you instantly want to solve the problem just restart mysql, and then try again to drop database.I also face similar problems some time, the above trick always works for me.

edited Aug 27 '13 at 12:28

answered Aug 27 '13 at 12:18

smrati katiyar

457
1
6
11

OMG! So easy, thank you! – Юрий Ярвинен Aug 11 '23 at 12:12

score 0 · Answer 2 · edited Mar 26 '16 at 15:10

0

There are various reasons:

some time your database is in use>> may be some transaction/query is executing when you are trying to delete.
may be query window is open with the db name selected.In this case it will tell that db is used by the query window, so you cannot drop the db.

edited Mar 26 '16 at 15:10

Undo

25,519
37
106
129

answered Mar 05 '14 at 19:04

Samuel Joy

578
5
8

Hi , thanks for reply. yes link given by you is good to overcome of this problem. Actually I know the way how to overcome but I want it to some prevention methods for the same. for more details see below my answer what i did for it , i knew it is not complete solution but at least i know when these things are happening and in reply either i received alert mail or apply any overcome methods – Rajnish Mar 06 '14 at 05:40

score 0 · Answer 3 · answered Mar 06 '14 at 05:47

thanks to everyone for reply. all methods are working fine and good to overcome of this problem.

Actually I know the way how to overcome but I want it to some prevention methods for the same.

see actually whenever I faced this issue just kill all sleep thread and rerun the alter or drop will resolve it or any method you explain will also work.

but see all methods we need some downtime right and you know what does it means for production database.

main thing I observed that some earlier command/transaction which used that table and now they are in sleep mode and that's causes these situation again and again.

there should be some prevention method for the same .I am still looking for it.
one easy solution ,I am using currently is , I created a bash script which regular check (at interval of every minutes) MySQL process list and if there is any thread i.e waiting for metadata lock and time exceed 30 sec either it send an alert email or do all steps mentioned above to overcome of these situations.

I knew this is not complete solution ,but script always send me alert whenever database faced same situation.
Still I am looking for best solution ,whenever I got I will post here.

unable to drop database in mysql

3 Answers3