Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

Please help.

EDIT:

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?

share|improve this question

migrated from serverfault.com Jan 27 '12 at 17:40

This question came from our site for professional system and network administrators.

3  
Out of curiosity, what does SHOW FULL PROCESSLIST report while the database drop is busy spinning its wheels? That's usually the first place I look when some query is taking substantially longer than expected. –  db2 Jan 27 '12 at 18:38
    
Unfortunately I didn't get a chance to run your query, but I did use shell command "top" and MySQL was using very little resources, I think it was under 3% of memory. –  Buttle Butkus Jan 28 '12 at 1:12
    
This is curious, if you find a solution post it as answer please. The only thing the immediately comes to mind is the server is thrashing in paging so I/O grinds to halt. If this were the case though you wouldn't even be able to maintain an ssh connection. And for the record a 2 GB table is meh, let alone a 2 GB DB ;) –  atxdba Jan 28 '12 at 2:13
1  
@db2: thank you 1000x for your comment! I had the same problem as the OP, and with your remark, I was able to kill an offending thread that I thought didn't exist. –  René Nyffenegger Feb 18 '12 at 20:58

5 Answers 5

up vote 10 down vote accepted

Rather than killing the process (as in the accepted answer) 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.

share|improve this answer
    
is mysqladmin a standard api? I have never used it. I usually use mysql command line or phpmyadmin. –  Buttle Butkus Mar 28 '13 at 2:37
    
Yes, it's part of the MySQL server install. 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. –  Stefan Haflidason Apr 1 '13 at 4:35
    
Thanks. Any reason why you use mysqladmin instead of mysql? It seems like you have to type "mysqladmin" in front of every command. That would get tedious fast. –  Buttle Butkus Apr 2 '13 at 23:17
    
Generally speaking you can use either. 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 Haflidason Apr 23 '13 at 0:54

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.

share|improve this answer

As per the edit in my question, I believe this is the answer:

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.

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.

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.

Then run

DROP DATABASE `database_name`;

And everything should be clean.

EDIT:

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 "best answer" instead of this one.

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.