为什么我不能删除 MySQL 数据库？

Why can't I drop MySQL Database?

提问by Will Nielsen

Problem

问题

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

我在Mac OS 10.8.2上运行MySQL 5.5.23并且无法删除特定数据库，但我可以删除其他数据库。

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 mysqlcheckfrom 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

• 我已经重新启动系统
• 我尝试通过 CLI 重新启动 MySQL
  • $ sudo /usr/local/mysql/support-files/mysql.server stop
  • 但收到此错误 ERROR! MySQL server PID file could not be found!
• 我已经修复了 mysql.proc 表。
  • REPAIR TABLE mysql.proc
  • REPAIR TABLE mysql.proc USE_FRM
• 我已经修复了所有 mysql.* 表。
  • REPAIR TABLE mysql.*
• 从命令行 运行mysqlcheck 时
  • mysqlcheck --repair --all-databases
  • mysqlcheck --repair specific-db
    • 我收到此错误： 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]

更新[1] 2013-01-05 11:15 am [纽约]

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

日志和反馈（根据@Thomas 的评论）为了查找所有日志，我运行了 (cli)：

$(ps auxww|sed -n '/sed -n/d;/mysqld /{s/.* \([^ ]*mysqld\) .*//;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.

我正在调查mysql_upgrade.

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

更新[2] 2013-01-05 下午4:04 [纽约]

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]

更新[2.1] 2013-01-05 下午5:37 [纽约]

I ran ps auxww | grep mysqland found the mysqldprocess 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.

我运行ps auxww | grep mysql并找到了该mysqld进程并杀死了它 ( sudo kill [process id])。然后我能够成功重新启动mysql。但是，我仍然没有运气删除上面提到的特定数据库。

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.

在尝试手动修复损坏以及此处列出的许多建议和其他答案后，重新安装 mySQL 是解决我问题的唯一方法。

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

在Mac（运行 10.8.2）上，我还必须手动删除一些内容以进行全新安装：

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

• MySQL 重复出现 CONCAT 错误 1548 - 无法从 mysql.proc 加载。表可能已损坏
• SQL 错误：BIGINT UNSIGNED value is out of range in (...)，但没有意义
• 如何修复损坏的表
• 找不到 MySQL 管理器或服务器 PID 文件
• 安全更新 2010-005 后的 PHP/MySQL 问题
• Mac OS X 软件更新后的 mysql 问题
• 如何彻底删除 MySQL Mac OS X Leopard

采纳答案by Michael Durrant

I would try:

我会尝试：

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

• 备份/保存任何包含重要数据的数据库。
• 删除 mySQL
• 重新安装 MySQL
• 恢复所有备份的数据库。

回答by Md. Zubaer Ahammed

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:

我遇到了一个问题，即对我的数据库（名为：caloriecalculator）的查询花费的时间太长，而且根本不会下降。我按照以下步骤操作，它解决了我的问题：

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

1. 查看所有 MySQL 进程：mysqladmin processlist -u root -p

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

2. Now run: drop database caloriecalculator;

2. 杀死与卡路里计算器相关的所有进程，因为它阻止了我要执行的下一个查询。mysqladmin -u root -p kill 4

3. 现在运行：删除数据库卡路里计算器；

回答by LSerni

I had this happen to me on a Linux server, and the cause was a corrupted database directory.

我在 Linux 服务器上发生了这种情况，原因是数据库目录损坏。

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):

更新：要做的一件事是进入 MySQL 数据库目录并执行ls -la, 以验证邪恶的数据库在权限、所有权等方面与其他数据库相同。例如，此处“原始”数据库不能删除（它是由以 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_originalwould fix the problem (but check insidethe directory to verify there too permissions and ownerships are copacetic).

运行chown mysql:mysql _db_xatex_original; chmod 700 _db_xatex_original将解决该问题（但检查目录内部以验证那里的权限和所有权是否一致）。

---

---

In the end, I employed the following ugly hack (after trying stopping, restarting and repairing whatever could be targeted by a REPAIR):

最后，我采用了以下丑陋的 hack（在尝试停止、重新启动和修复 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!

• 创建了一个数据库“替罪羊”
• 停止 MySQL 服务器
• 将 MySQL 服务器创建的目录 /var/lib/mysql/scapegoat 复制到 /tmp
• 重新启动 MySQL 服务器，删除数据库“替罪羊”，停止服务器
• 现在我有一个干净的、空的数据库目录的副本，MySQL 不再知道它。
• 将“evildb”目录移动到/tmp（这样如果出现问题我可以把它放回去）
• 将“替罪羊”目录移动到 /var/lib/mysql 将其重命名为“evildb”
• 启动 MySQL 服务器
• 不确定此时我是否进行了更多维修
• 并且“evildb”数据库变得可删除！

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.

我的解释是，当要求删除数据库时，MySQL Server 首先对数据库目录中的文件进行一些检查。如果这些检查失败，丢弃也会失败。这些检查必须与由 执行的检查略有不同REPAIR。也许在受影响的目录中有一些意想不到的东西。

I think this was on a MySQL 5.1 or 5.2 on a SuSE 11.2 Linux distribution. Hope it helps.

我认为这是在 SuSE 11.2 Linux 发行版上的 MySQL 5.1 或 5.2 上。希望能帮助到你。

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 proctable, withoutbeing a table corruption. Have you tried visually inspecting the procdatabase table, in order to find something there that belongs to the evil DB?

回想起来，我不记得收到关于“proc”的错误。所以我不太确定问题出在目录. 它可能与proc表相关联，而不是表损坏。您是否尝试过目视检查proc数据库表，以找到属于邪恶数据库的内容？

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 dbcolumn. In a pinch, you could export the proctable and reload it after cleaning (either through SQL or via a disk file).

那，或由此产生的任何错误，可能有助于解决问题。你可能知道，有些行列错了db。在紧要关头，您可以导出proc表并在清理后重新加载它（通过 SQL 或通过磁盘文件）。

TEST

测试

I have partial verification for the above update. By intentionallyinserting rubbish into the proctable apropos a newly created database evil, I partiallyreproduced your symptoms (undroppable database, MySQL connection crashes on attempt). Error number is not 1548 though; but maybe it would be, if I inserted the rightrubbish in that table... anyway, the useful bit is that by removingall references to the evildb, the latter became droppable again:

我对上述更新进行了部分验证。通过故意将垃圾插入到proc新创建的数据库表中evil，我部分重现了您的症状（无法删除的数据库，尝试时 MySQL 连接崩溃）。但是错误号不是 1548；但也许是这样，如果我在那个表中插入了正确的垃圾......无论如何，有用的一点是，通过删除对evil数据库的所有引用，后者再次变得可删除：

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)

回答by Nisharg Shah

If you using xampp In windows

如果你在 windows 中使用 xampp

you can also drop your database using phpmyadmin

你也可以使用 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]

转到 xampp -> mysql -> 数据 -> [数据库名称]

delete your [database name] now.

立即删除您的 [数据库名称]。

回答by amralieg

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

我遇到了同样的问题，我所做的就是从 mysql 数据目录中删除数据库目录。