Why can't I drop MySQL Database?
I ran into an issue that queries on my databases (named: caloriecalculator) was taking too long and it won't drop at all. I followed these steps below and it fixed my issue:
- See all MySQL processes: mysqladmin processlist -u root -p
Kill all processes relating to caloriecalculator as it was blocking my next queries to be executed. mysqladmin -u root -p kill 4
Now run: drop database caloriecalculator;
I would try:
- Backup/save any databases that have important data.
- Remove mySQL
- Reinstall mySQL
- Restore any backed up databases.
I had this happen to me on a Linux server, and the cause was a corrupted database directory.
UPDATE: one thing to do is to go into MySQL database directory and perform a ls -la, to verify that the evil DB is the same as the others as regards permissions, ownership and so on. For example here the 'original' database cannot be dropped (it was created by a stupid tool ran as root):
drwx------ 2 mysql mysql 4096 Aug 27 2015 _db_graph
drwx------ 2 mysql mysql 4096 Jul 13 11:58 _db_xatex
drwxrw-rw- 2 root root 12288 May 18 14:27 _db_xatex_original
drwx------ 2 mysql mysql 12288 Jun 9 08:23 _db_xatex_contab
drwx------ 2 mysql mysql 12288 May 18 17:58 _db_xatex_copy
drwx------ 2 mysql mysql 4096 Nov 24 2016 _db_xatex_test
Running chown mysql:mysql _db_xatex_original; chmod 700 _db_xatex_original would fix the problem (but check inside the directory to verify there too permissions and ownerships are copacetic).
In the end, I employed the following ugly hack (after trying stopping, restarting and repairing whatever could be targeted by a REPAIR):
- created a database "scapegoat"
- stopped MySQL Server
- copied the directory created by MySQL Server, /var/lib/mysql/scapegoat, to /tmp
- restarted MySQL Server, dropped the database "scapegoat", stopped the server
- Now I had a copy of a clean, empty DB dir that MySQL no longer knew anything about.
- moved the "evildb" directory to /tmp (so that if thing went wrong I could put it back)
- moved the "scapegoat" directory to /var/lib/mysql renaming it to "evildb"
- started MySQL Server
- not sure if I ran any more repairs at this point
- and the "evildb" database became droppable!
My explanation is that when asked to drop a database, MySQL Server first performs some checks on the files in the database directory. If these checks fail, the drop also fails. These checks must be subtly different from the ones performed by REPAIR. Maybe in the affected directory there is something unexpected.
I think this was on a MySQL 5.1 or 5.2 on a SuSE 11.2 Linux distribution. Hope it helps.
UPDATE
On thinking back, I don't remember getting errors about "proc". So I'm less sure that the problem lies in the directory. It might be connected with the proc table, without being a table corruption. Have you tried visually inspecting the proc database table, in order to find something there that belongs to the evil DB?
USE mysql;
SELECT * FROM proc;
That, or any errors therefrom, could help in solving the problem. You might, who know, have some lines with the wrong db column. In a pinch, you could export the proc table and reload it after cleaning (either through SQL or via a disk file).
TEST
I have partial verification for the above update. By intentionally inserting rubbish into the proc table apropos a newly created database evil, I partially reproduced your symptoms (undroppable database, MySQL connection crashes on attempt). Error number is not 1548 though; but maybe it would be, if I inserted the right rubbish in that table... anyway, the useful bit is that by removing all references to the evil db, the latter became droppable again:
mysql> drop database evil;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> use mysql;
No connection. Trying to reconnect...
Connection id: 1
Current database: *** NONE ***
Database changed
mysql> DELETE FROM proc WHERE db = 'evil';
Query OK, 2 rows affected (0.00 sec)
mysql> drop database evil;
Query OK, 0 rows affected (0.00 sec)