Why can't I drop MySQL Database?

Question

Problem

I'm running MySQL 5.5.23 on Mac OS 10.8.2 and am unable to drop a particular database, but I can drop others.

When I attempt to drop the specific table I get this error:

#1548 - Cannot load from mysql.proc. The table is probably corrupted

Attempted Fixes

I have restarted the system
I have tried to restart MySQL via CLI
- $ sudo /usr/local/mysql/support-files/mysql.server stop
- but received this error ERROR! MySQL server PID file could not be found!
I have repaired the mysql.proc table.
- REPAIR TABLE mysql.proc
- REPAIR TABLE mysql.proc USE_FRM
I have repaired all mysql.* tables.
- REPAIR TABLE mysql.*
When running mysqlcheck from the Command Line
- mysqlcheck --repair --all-databases
- mysqlcheck --repair specific-db
  - I received this error : mysqlcheck: Got error: 2002: Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2) when trying to connect

Current Status

I still cannot drop the original specific database, but can drop others.

Update[1] 2013-01-05 11:15 am [New York]

Logs and Feedback (per @Thomas in comments) To find all logs, I ran (cli):

$(ps auxww|sed -n '/sed -n/d;/mysqld /{s/.* \([^ ]*mysqld\) .*/\1/;p;}') --verbose --help|grep '^log'

I received this feedback:

130105 11:35:21 [Warning] Can't create test file /usr/local/mysql-5.5.23-osx10.6-x86_64/data/wills-mbp.lower-test
130105 11:35:21 [Warning] Can't create test file /usr/local/mysql-5.5.23-osx10.6-x86_64/data/wills-mbp.lower-test
130105 11:35:21 [Note] Plugin 'FEDERATED' is disabled. /usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
130105 11:35:21 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

I'm looking into the mysql_upgrade.

Update[2] 2013-01-05 4:04 pm [New York]

I ran this :

sudo /usr/local/mysql/support-files/mysql.server stop

And received this error:

ERROR! MySQL server PID file could not be found!

Update[2.1] 2013-01-05 5:37 pm [New York]

I ran ps auxww | grep mysql and found the mysqld process and killed it (sudo kill [process id]). I was then able to restart mysql successfully. However, I'm still having no luck dropping that specific database mentioned above.

Resolved

After trying to manually repair the corruption and many of the suggestions and the other answer listed here, reinstalling mySQL was the only thing that solved my problem.

On a Mac (running 10.8.2) I also had to do some manual deletions for a clean install:

sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm /etc/my.cnf

Articles consulted

Server is running. I can drop other tables. I have root privileges. — Will Nielsen, Jan 05 '13 at 16:15
What happens when you try to drop it? Any particular error message? Anything in the logs perhaps? — Thomas, Jan 05 '13 at 16:17
The database exists. I meant to have the error upon Drop in the main post (will edit). Sorry. Here it is : `#1548 - Cannot load from mysql.proc. The table is probably corrupted` — Will Nielsen, Jan 05 '13 at 16:21
As unhelpful as it might be I have to say that I'm astonished of the quality of your first post here on SO. Good job. — h2ooooooo, Jan 05 '13 at 16:23
Thanks. Markdown rocks and making it easier for others to assess the problem helps me and others arrive at the solution more rapidly. — Will Nielsen, Jan 05 '13 at 16:24
@Thomas et al, I am looking into the logs presently. Please see the above edits to the post for information I have found at this point. — Will Nielsen, Jan 05 '13 at 16:42
Clean DB: http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261 — bancer, Apr 01 '14 at 12:05

score 8 · Answer 1 · answered Jan 27 '18 at 21:49

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;

score 3 · Accepted Answer · answered Jan 05 '13 at 16:22

3

I would try:

Backup/save any databases that have important data.
Remove mySQL
Reinstall mySQL
Restore any backed up databases.

answered Jan 05 '13 at 16:22

Michael Durrant

93,410
97
333
497

After trying to manually repair the corruption and many of the suggestions and the other answer listed here, reinstalling mySQL was the only thing that solved my problem. On a *Mac* (running 10.8.2) I also had to do some manual deletions for a clean install: sudo rm /usr/local/mysql sudo rm -rf /usr/local/mysql* sudo rm -rf /Library/StartupItems/MySQLCOM sudo rm -rf /Library/PreferencePanes/My* sudo rm -rf /Library/Receipts/mysql* sudo rm -rf /Library/Receipts/MySQL* sudo rm /etc/my.cnf – Will Nielsen Jan 08 '13 at 02:49

LSerni · Answer 3 · 2017-07-13T10:33:32.050

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)

Strangely I haven't any rows in my `mysql.proc` table. Any ideas why that might be. I confess I'm ignorant on what data or its quantity in the `mysql.proc` table should look like. — Will Nielsen, Jan 05 '13 at 17:01
Mine (on my laptop) is completely empty too. On a server, I see several functions defined in there. Probably if you don't have any `CREATE`d `FUNCTION`, that table stays empty. But then, it's unlikely that it's the table that causes the problem. Try the directory hack, and see whether it helps. — LSerni, Jan 05 '13 at 17:09
I attempted to stop MySQL but received this error: ERROR! MySQL server PID file could not be found! — Will Nielsen, Jan 05 '13 at 20:39
One thing that I had to do for it to work was to change the ownership to mysql. `sudo chown -R mysql:mysql evildb` — Raza, Jul 13 '17 at 06:23

Nisharg Shah · Answer 4 · 2019-03-29T13:01:06.547

1

If you using xampp In windows

you can also drop your database using phpmyadmin

go to home -> databases -> click on your [database name] -> drop

OR

you can also drop your database manually

go to xampp -> mysql -> data -> [database name]

delete your [database name] now.

edited Mar 29 '19 at 13:01

answered Jul 10 '18 at 16:37

Nisharg Shah

16,638
10
62
73

score 1 · Answer 5 · answered Jan 10 '19 at 11:57

1

I had the same problem and all I did was to delete the database directory from the mysql data directory.

answered Jan 10 '19 at 11:57

amralieg

113
8