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

• MySQL duplicates with CONCAT error 1548 - Cannot load from mysql.proc. The table is probably corrupted
• SQL error: BIGINT UNSIGNED value is out of range in (…), but it doesn't make sense
• How to repair corrupted table
• MySQL manager or server PID file could not be found
• PHP/MySQL issue after security update 2010-005
• mysql problems after Mac OS X software update
• How to remove MySQL completely Mac OS X Leopard

Answer 1

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:

1. See all MySQL processes: mysqladmin processlist -u root -p

2. Kill all processes relating to caloriecalculator as it was blocking my next queries to be executed. mysqladmin -u root -p kill 4

3. Now run: drop database caloriecalculator;