MySQL：删除数据库时出错（errno 13；errno 17；errno 39）

MySQL: Error dropping database (errno 13; errno 17; errno 39)

提问by Philippe Blayo

I failed to drop a database:

我删除数据库失败：

mysql> drop database mydb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './mydb', errno: 39)

Directory db/mydb exists in mysql tree but has no table:

目录 db/mydb 存在于 mysql 树中但没有表：

# ls -l db/mydb
-rw-rw---- mysql mysql HIS_STAT.MYD
-rw-rw---- mysql mysql HIS_STAT.MYI

What should I do?

我该怎么办？

回答by LSerni

Quick Fix

快速解决

If you just want to drop the database no matter what (but pleasefirst read the whole post: the error was given for a reason, and it might be important to know what the reason was!), you can:

如果您无论如何只想删除数据库（但请先阅读整篇文章：错误是有原因的，了解原因可能很重要！），您可以：

• find the datadir with the command SHOW VARIABLES WHERE Variable_name LIKE '%datadir%';
• stop the MySQL server (e.g. service mysql stopor rcmysqld stopor similar on Linux, NET STOP <name of MYSQL service, often MYSQL57 or similar>or through SERVICES.MSCon Windows)
• go to the datadir (this is where you should investigate; see below)
• remove the directory with the same name as the database
• start MySQL server again and connect to it
• execute a DROP DATABASE
• that's it!

• 使用命令查找数据目录 SHOW VARIABLES WHERE Variable_name LIKE '%datadir%';
• 停止MySQL服务器（例如service mysql stop或rcmysqld stop或Linux上的相似，NET STOP <name of MYSQL service, often MYSQL57 or similar>或通过SERVICES.MSC在Windows上）
• 转到数据目录（这是您应该调查的地方；见下文）
• 删除与数据库同名的目录
• 再次启动 MySQL 服务器并连接到它
• 执行 DROP DATABASE
• 就是这样！

Reasons for Errno 13

错误 13 的原因

MySQL has no write permission on the parent directory in which the mydbfolder resides.

MySQL 对mydb文件夹所在的父目录没有写权限。

Check it with

检查它

ls -la /path/to/data/dir/         # see below on how to discover data dir
ls -la /path/to/data/dir/mydb   

On Linux, this can also happen if you mix and match MySQL and AppArmor/SELinux packages. What happens is that AppArmor expects mysqld to have its data in /path/to/data/dir, and allows full R/W there, but MySQLd is from a different distribution or build, and it actually stores its data elsewhere(e.g.: /var/lib/mysql5/data/**as opposed to /var/lib/mysql/**). So what you see is that the directory has correct permissions and ownershipand yet it still gives Errno 13 because apparmor/selinux won't allow access to it.

在 Linux 上，如果您混合搭配 MySQL 和 AppArmor/SELinux 包，也会发生这种情况。什么情况是，AppArmor的预期的mysqld有它的数据/path/to/data/dir存在，并允许全R / W，但mysqld正在从不同的分布或构建，它实际上将其数据存储在其他地方（如：/var/lib/mysql5/data/**相对于/var/lib/mysql/**）。所以你看到的是该目录具有正确的权限和所有权，但它仍然给出 Errno 13，因为 apparmor/selinux 不允许访问它。

To verify, check the system log for security violations, manually inspect apparmor/selinux configuration, and/or impersonate the mysql user and try going to the base var directory, then cd incrementally until you're in the target directory, and run something like touch aardvark && rm aardvark. If permissions and ownership match, and yet the above yields an access error, chances are that it's a security framework issue.

要验证，请检查系统日志是否存在安全违规，手动检查 apparmor/selinux 配置，和/或模拟 mysql 用户并尝试转到基本 var 目录，然后逐步 cd 直到您进入目标目录，然后运行类似touch aardvark && rm aardvark. 如果权限和所有权匹配，但上述内容产生访问错误，则很可能是安全框架问题。

"EASY FIX" considered harmful

I have happened upon an "easy fix" suggested on a "experts forum" (notStack Overflow, thank goodness), the same "fix" I sometimes find for Web and FTP problems -- chown 777. PLEASE NEVER DO THAT. For those who don't already know, 777 (or 775, or 666) isn't a magic number that somehow MySQL programmers forgot to apply themselves, or don't want you to know. Each digit has a meaning, and 777 means "I hereby consent to everyone doing whatever they want with my stuff, up to and including executing it as if it were a binary or shell script". By doing this (and chances are you won't be allowed to do thison a sanely configured system),

• you risk several security conscious programs to refuse to function anymore(e.g. if you do that to your SSH keys, goodbye SSH connections; etc.) since they realize they're now in a insecure context.
• you allow literally everyone with any level of access whatsoever to the system to read and writeyour data, whether MySQL allows it or not, unbeknownst to MySQL itself- i.e. it becomes possible to silently corrupt whole databases.
• the above mightsometimes be done, in exceedingly dire straits, by desperate and knowledgeable people, to gain access again to an otherwise inaccessible screwed MySQL installation (i.e. even mysqladminno longer grants local access), and will be immediately undone as soon as things get back to normal - it's not a permanent change, not even then. And it's not a fix to "one weird trick to be able to drop my DB".

(needless to say, it's almost neverthe realfix to any Web or FTP problems either. The fix to "Of late, the wife's keys fail to open the front door and she can't enter our home" is 'check the keys or have the lock repaired or replaced'; the admittedly much quicker chown 777is "Just leave the front door wide open! Easy peasy! What's the worst that might happen?")

“EASY FIX”被认为是有害的

我偶然发现了“专家论坛”（不是Stack Overflow，谢天谢地）上建议的“简单修复” ，我有时会为 Web 和 FTP 问题找到相同的“修复”—— chown 777。请永远不要这样做。对于那些还不知道的人来说，777（或 775 或 666）并不是一个神奇的数字，MySQL 程序员不知何故忘记应用自己，或者不想让您知道。每个数字都有一个含义，777 的意思是“我在此同意每个人对我的东西做任何他们想做的事情，直到并包括像执行二进制或 shell 脚本一样执行它”。通过这样做（并且很可能不允许您在配置合理的系统上执行此操作），

• 你冒着几个安全意识程序拒绝运行的风险（例如，如果你对你的 SSH 密钥这样做，再见 SSH 连接等），因为他们意识到他们现在处于不安全的环境中。
• 您实际上允许任何对系统具有任何级别访问权限的人都可以读取和写入您的数据，无论 MySQL 是否允许，MySQL 本身都不知道- 即可以悄悄地破坏整个数据库。
• 有时，绝望和知识渊博的人可能会在极其可怕的困境中完成上述操作，以再次访问原本无法访问的错误 MySQL 安装（即，甚至mysqladmin不再授予本地访问权限），一旦事情恢复，将立即撤消正常 - 这不是永久性的变化，即使如此。并且它不是“能够删除我的数据库的一个奇怪技巧”的解决方案。

（不用说，它几乎从来都不是解决任何 Web 或 FTP 问题的真正方法。“最近，妻子的钥匙无法打开前门，她无法进入我们家”的解决方法是“检查钥匙或修理或更换锁”；无可否认，更快的chown 777是“只要让前门敞开着！轻松！可能发生的最糟糕的事情是什么？”）

Reasons for Errno 39

错误 39 的原因

This code means "directory not empty". The directory contains some hiddenfiles MySQL knows nothing about. For non-hidden files, see Errno 17. The solution is the same.

此代码的意思是“目录不为空”。该目录包含一些MySQL 一无所知的隐藏文件。非隐藏文件见Errno 17，解决方法同上。

Reasons for Errno 17

错误 17 的原因

This code means "file exists". The directory contains some MySQL file that MySQL doesn't feel about deleting. Such files could have been created by a SELECT ... INTO OUTFILE "filename";command where filenamehad no path. In this case, the MySQL process creates them in its current working directory, which (tested on MySQL 5.6 on OpenSuSE 12.3) is the data directory of the database, e.g. /var/lib/mysql/data/nameofdatabase.

此代码表示“文件存在”。该目录包含一些 MySQL 文件，MySQL 不想删除这些文件。此类文件可能是由没有路径的SELECT ... INTO OUTFILE "filename";命令创建的filename。在这种情况下，MySQL 进程在其当前工作目录中创建它们，该目录（在 OpenSuSE 12.3 上的 MySQL 5.6 上测试）是数据库的数据目录，例如/var/lib/mysql/data/nameofdatabase.

Reproducibility:

再现性：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1676
Server version: 5.6.12-log openSUSE package
[ snip ]    

mysql> CREATE DATABASE pippo;
Query OK, 1 row affected (0.00 sec)

mysql> USE pippo;
Database changed
mysql> SELECT version() INTO OUTFILE 'test';
Query OK, 1 row affected (0.00 sec)

mysql> DROP DATABASE pippo;
ERROR 1010 (HY000): Error dropping database (can't rmdir './pippo/', errno: 17)

-- now from another console I delete the "test" file, without closing this connection
-- and just retry. Now it works.

mysql> DROP DATABASE pippo;
Query OK, 0 rows affected (0.00 sec)

Move the file(s) outside (or delete if not needed) and retry. Also, determine why they were created in the first place - it could point to a bug in some application. Or worse: see below...

将文件移到外面（或在不需要时删除）并重试。此外，首先要确定创建它们的原因 - 它可能指向某些应用程序中的错误。或者更糟：见下文...

UPDATE: Error 17 as exploit flag

更新：错误 17 作为漏洞利用标志

This happened on a Linux system with Wordpress installed. Unfortunately the customer was under time constraints and I could neither image the disk or do a real forensics round - I reinstalled the whole machine and Wordpress got updated in the process, so I can only say that I'm almostcertain they did it through this plugin.

这发生在安装了 Wordpress 的 Linux 系统上。不幸的是，客户受到时间限制，我既无法对磁盘进行映像，也无法进行真正的取证 - 我重新安装了整个机器并且 Wordpress 在此过程中得到了更新，所以我只能说我几乎可以肯定他们是通过这个完成的插件。

Symptoms: the mysqldata directory contained three files with extension PHP. Wait, what?!?-- and inside the files there was a bulk of base64 code which was passed to base64_decode, gzuncompressand [eval()][2]. Aha. Of course these were only the first attempts, the unsuccessful ones. The site had been well and truly pwn3d.

症状：mysql数据目录包含三个扩展名为 PHP 的文件。等等，什么？！？- 在文件中，有大量 base64 代码被传递给base64_decode,gzuncompress和[eval()][2]。啊哈。当然，这些只是第一次尝试，没有成功。该网站一直很好，真正的 pwn3d。

So if you find a file in your mysql data dir that's causing an Error 17, check it with fileutilityor scan it with an antivirus. Or visually inspect its contents. Do not assume it's there for some innocuous mistake.

因此，如果您在 mysql 数据目录中发现导致错误 17 的文件，请使用file实用程序检查它或使用防病毒软件对其进行扫描。或目视检查其内容。不要认为它是一些无害的错误。

(Needless to say, to visually inspect the file, never double click it).

（不用说，要目视检查文件，切勿双击它）。

The victim in this case (he had some friend "do the maintenance") would never have guessed he'd been hacked until a maintenance/update/whatever script ran a DROP DATABASE(do not ask me why - I'm not sure even I want to know) and got an error. From the CPU load and the syslog messages, I'm fairly positive that the host had become a spam farm.

在这种情况下，受害者（他有一些朋友“做维护”）永远不会猜到他被黑了，直到维护/更新/任何脚本运行DROP DATABASE（不要问我为什么 - 我什至不确定我想要知道）并出现错误。从 CPU 负载和系统日志消息来看，我相当肯定主机已成为垃圾邮件群。

Yet another Error 17

又一个错误 17

If you rsyncor copy between two MySQL installations of the same version but different platform or file systemssuch as Linux or Windows (which is discouraged, and risky, but many do it nonetheless), and specifically with different case sensitivitysettings, you can accidentally end up with two versionsof the same file (either data, index, or metadata); say Customers.myiand Customer.MYI. MySQL uses one of them and knows nothing about the other (which could be out of date and lead to a disastrous sync). When dropping the database, which also happens in many a mysqldump ... | ... mysqlbackup schemes, the DROPwill fail because that extra file (or thoseextra files) exists. If this happens, you should be able to recognize the obsolete file(s) that need manual deletion from the file time, or from the fact that their case scheme is different from the majority of the other tables.

如果您rsync或在相同版本但不同平台或文件系统（例如 Linux 或 Windows）的两个 MySQL 安装之间进行复制（这是不鼓励且有风险的，但许多人仍然这样做），特别是使用不同的区分大小写设置，您可能会意外结束使用同一文件的两个版本（数据、索引或元数据）；说Customers.myi和Customer.MYI。MySQL 使用其中一个并且对另一个一无所知（这可能已经过时并导致灾难性的同步）。当删除数据库时，这也发生在许多mysqldump ... | ... mysql备份方案中，DROP将失败，因为额外的文件（或那些额外文件）存在。如果发生这种情况，您应该能够从文件时间或从它们的案例方案与大多数其他表不同的事实中识别需要手动删除的过时文件。

Finding the data-dir

查找数据目录

In general, you can find the data directory by inspecting the my.cnffile (/etc/my.cnf, /etc/sysconfig/my.cnf, /etc/mysql/my.cnfon Linux; my.iniin the MySQL program files directory in Windows), under the [mysqld]heading, as datadir.

通常，您可以通过检查标题下的my.cnf文件（Linux 上的/etc/my.cnf, /etc/sysconfig/my.cnf, /etc/mysql/my.cnf；my.iniWindows 中的 MySQL 程序文件目录中的.[mysqld]datadir

Alternatively you can ask it to MySQL itself:

或者，您可以询问 MySQL 本身：

mysql> SHOW VARIABLES WHERE Variable_name LIKE '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

回答by Sambuvarayas

In my case it was due to 'lower_case_table_names' parameter.

就我而言，这是由于 'lower_case_table_names' 参数造成的。

The error number 39 thrown out when I tried to drop the databases which consists upper case table names with lower_case_table_names parameter is enabled.

当我尝试删除包含带有lower_case_table_names 参数的大写表名的数据库时抛出的错误号39 已启用。

This is fixed by reverting back the lower case parameter changes to the previous state.

这是通过将小写参数更改恢复到先前状态来解决的。

回答by venkat

Simply go to /opt/lampp/var/mysql

只需转到 /opt/lampp/var/mysql

There You can find your databasename. Open that folder. Remove if any files in it

在那里你可以找到你的database名字。打开那个文件夹。删除其中的任何文件

Now come to phpmyadminand drop that database

现在来phpmyadmin放下那个database

回答by TomRA

As for ERRORCODE 39, you can definately just delete the physical table files on the disk. the location depends on your OS distribution and setup. On Debian its typically under /var/lib/mysql/database_name/ So do a:

至于ERRORCODE 39，你肯定可以删除磁盘上的物理表文件。位置取决于您的操作系统分发和设置。在 Debian 上，它通常在 /var/lib/mysql/ database_name/ 下，所以请执行以下操作：

rm -f /var/lib/mysql/<database_name>/

And then drop the database from your tool of choice or using the command:

然后从您选择的工具中删除数据库或使用以下命令：

DROP DATABASE <database_name>

回答by Jaime Montoya

This was how I solved it:

我是这样解决的：

mysql> DROP DATABASE mydatabase;
ERROR 1010 (HY000): Error dropping database (can't rmdir '.\mydatabase', errno: 13)
mysql> 

I went to delete this directory: C:\...\UniServerZ\core\mysql\data\mydatabase.

我去删除这个目录：C:\...\UniServerZ\core\mysql\data\mydatabase.

mysql> DROP DATABASE mydatabase;
ERROR 1008 (HY000): Can't drop database 'mydatabase'; database doesn't exist

回答by fran

In my case an additional file not belonging to the database was inside the database folder. Mysql found the folder not empty after dropping all tables which triggered the error. I remove the file and the drop database worked fine.

在我的情况下，一个不属于数据库的附加文件位于数据库文件夹中。在删除所有触发错误的表后，Mysql 发现文件夹不为空。我删除了文件，drop 数据库工作正常。

回答by aditya

in linux , Just go to "/var/lib/mysql" right click and (open as adminstrator), find the folder corresponding to your database name inside mysql folder and delete it. that's it. Database is dropped.

在 linux 中，只需转到“/var/lib/mysql”右键单击并（以管理员身份打开），在 mysql 文件夹中找到与您的数据库名称对应的文件夹并将其删除。就是这样。数据库被删除。