Why is DROP DATABASE taking so long? (MySQL)

Question

New CentOS installation.

I was running an import of a large DB (2GB sql file) and had a problem. The SSH client seemed to lose the connection and the import seemed to freeze. I used another window to login to mysql and the import appeared to be dead, stuck on a particular 3M row table.

So I tried

DROP DATABASE huge_db;

15-20 minutes later, nothing. In another window, I did:

/etc/init.d/mysqld restart

The DROP DB window messaged: SERVER SHUTDOWN. Then I actually restarted the physical server.

Logged back into mysql, checked and the db was still there, ran

DROP DATABASE huge_db;

again, and again I'm waiting already about 5 minutes.

Once again, it's fresh installation. The huge_db is the only db (other than system dbs). I swear I've dropped db's this large before and quickly, but maybe I'm wrong.

I've successfully dropped the database. It took something like 30 minutes. Also note that I think I was mistaken when I thought the mysqldump import was dead. The terminal connection was lost, but I think the process was still running. I most-likely killed the import mid-table (the 3M row table) and probably 3/4 of the way through the whole db. It was misleading that "top" showed mysql using only 3% of memory, when it seemed like it should be using more.

Dropping the DB ended up taking 30 min, so, again, I might not have had to restart the server and possibly could have just waited for the DROP to finish, but I don't know how mysql would react to getting a DROP query for the same db that it's importing via mysqldump.

Still, the question remains, why does it take 30min+ to DROP a 2GB database when all it should have to do is delete all the db files and remove all references to the DB from information_schema? What's the big deal?

• daydreamer: possibly related stackoverflow.com/a/18465205/9236556

Answer 1

Rather than killing the process, it would be safer if you did it within MySQL:

$ mysqladmin processlist -u root -p
Enter password: 
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| 174 | root | localhost | example | Sleep | 297 | | |
| 407 | root | localhost | | Query | 0 | | show processlist |
+-----+------+-----------+-------------------+---------+------+-------+------------------+

The query with id 174 is the one blocking deletion of the 'example' database, so before you kill any processes first let MySQL try to terminate the query:

$ mysqladmin kill 174

Run the processlist command above again to confirm that it was killed.

If this doesn't work, then you could perhaps look at killing the errant process, but before that you might try restarting the MySQL server.

You can also run commands like 'SHOW FULL PROCESSLIST' and 'KILL 174' in the MySQL shell, for example if you only have the MySQL client installed. The main point is to avoid killing the process using 'kill' in the shell unless absolutely necessary.

Generally speaking you can use either mysql or mysqladmin. You shouldn't need to be running commands like this that often though; once you start killing queries regularly something is definitely wrong and you'd be better off fixing that problem (killing the query process is just treating the symptom).

• Stefan Magnuson: @BugsBuggy A common way in which this can occur is if another process (e.g. a web server, or in this case an import process) is running and is connected to the database. When you issue the DROP DATABASE command the server will not proceed until all connections have been closed.
• baptx: Even after killing the process, if we try to drop the database again, it still takes forever. The processlist command displays the state "closing tables". When I waited until the drop database command finished, it said "ERROR 2013 (HY000): Lost connection to MySQL server during query" and did not drop the database. I got this issue since I shutdown my computer but if was hanging because of MySQL (MariaDB) so I had to force shutdown.

Answer 2

Though I thought that the import process had died, it was probably still running.

The DROP DATABASE command probably waited for the database to finish importing before it ran.

So, rather than DROP DATABASE taking a long time, it was probably just the import.

1.)

If anyone else reads this and is trying to cancel a database import and drop the database, I recommend you first find the PID (process id) for the import and run this from a different terminal:

$ kill [PID]

...where [PID] would be the actual PID for the process.

You should see the import halt immediately if the other terminal is still connected.

2.)

You could also run SHOW PROCESSLIST in the phpMyAdmin SQL tab. The resulting table shows running processes, and clicking the 'x' next to the row you want to kill should do the trick. This would be the same effect as the accepted answer or killing the mysql process from the mysql command line.

Then run

DROP DATABASE `database_name`;

And everything should be clean.

---

Another answer suggested that killing the process within mysql is better than doing it from outside. I have not tested that answer, but it sounds very plausible. So I have marked it as the "accepted answer" instead of this one.

Answer 3

I faced the same problem . But this time I checked show processlist; it said checking for permissions for longer time. Then I found that mysqld_safe was running as root whereas folder level permissions was only for mysql user. Hence I killed the query, ofcourse it took long time saying its in killed state but I waited for it to react then it killed the query and changed the folder level permissions to root also by adding it to group and chmod to 770. Then I executed the same drop database blah; it did work for me in 2 secs for 20GB database.

Answer 4

Try to truncate the largest tables in youra database before you drop it. I saw very similar behavior when working with MySQL archives of firewall traffic and this helped immensely.

• ejoubaud: According to MySQL docs, "Truncate operations drop and re-create the table, which is much faster than deleting rows one by one", so no point in truncating to drop - dev.mysql.com/doc/refman/8.0/en/truncate-table.html

Answer 5

The first thing that comes to mind is the status Checking Permissions...

When you issue DROP DATABASE mydb; everything inside /var/lib/mysql/mydb is checked to see if OS permissions exists for the right to drop every file.

Some have felt that reducing the number of mysql users may help