MySQL:删除数据库时出错(errno 13; errno 17; errno 39) [英] MySQL: Error dropping database (errno 13; errno 17; errno 39)

问题描述

我无法删除数据库:

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

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

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

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

我该怎么办?

推荐答案

快速修复

如果您只想删除数据库(但请首先阅读整篇文章:错误是出于某种原因，这可能很重要知道原因是什么！)，您可以:

Quick Fix

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

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

• find the datadir with the command SHOW VARIABLES WHERE Variable_name LIKE '%datadir%';
• stop the MySQL server (e.g. service mysql stop or rcmysqld stop or similar on Linux, NET STOP <name of MYSQL service, often MYSQL57 or similar> or through SERVICES.MSC on 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!

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

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

使用

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

在Linux上，如果混合并匹配MySQL和AppArmor/SELinux软件包，也会发生这种情况.发生的是AppArmor期望mysqld将其数据保存在/path/to/data/dir中，并允许在其中进行完整的读/写操作，但是MySQLd来自其他发行版或内部版本，并且实际上将其数据存储在其他地方(例如:/var/lib/mysql5/data/**而不是/var/lib/mysql/**).因此，您看到的是该目录具有正确的权限和所有权，但它仍然提供Errno 13，因为apparmor/selinux不允许访问它.

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 ownership and yet it still gives Errno 13 because apparmor/selinux won't allow access to it.

要进行验证，请检查系统日志中是否存在安全冲突，手动检查apparmor/selinux配置，和/或模拟mysql用户，然后尝试转到基本var目录，然后递增cd直到您位于目标目录中，然后运行类似touch aardvark && rm aardvark的命令.如果权限和所有权匹配，但是上面的代码会导致访问错误，则很可能是安全框架问题.

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.

简易修复"被认为是有害的

我碰巧在专家论坛"上建议轻松解决" (不是堆栈溢出，谢天谢地)，我有时会使用相同的修复" 查找Web和FTP问题-chown 777. 请不要这样做 .对于那些还不知道的人，777(或775或666)不是 MySQL程序员以某种方式忘记应用的幻数 自己，或者不想让你知道.每个数字都有一个 意思是777，意思是"我在此同意每个人对自己的应用做任何自己想做的事 东西，直到并包括执行它，就好像它是二进制文件或外壳程序一样 脚本".这样做(可能您不会被允许这样做 (在配置合理的系统上)

I have happened upon an "easy fix" suggested on a "experts forum" (not Stack 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 this on a sanely configured system),

• 您可能会冒险使用几个具有安全意识的程序来拒绝运行(例如，如果您对SSH密钥执行此操作，请再见SSH) 连接；等)，因为他们意识到自己现在处于不安全的环境中.
• 您实际上允许具有系统任何级别访问权限的每个人读取和写入您的数据，无论MySQL是否允许. 不是， MySQL本身不为人所知-即，有可能 默默破坏整个数据库.
有时候，绝望而又知识渊博的人可能会在极度严峻的海峡中，做出上述可能 无法通过螺丝固定的MySQL安装(即mysqladmin不再授予 本地访问)，并且将尽快撤消 一切恢复正常-这不是永久性的变化，甚至都不是 然后.而且，这不是能够删除我的数据库的一个怪异技巧"的解决方法.

• 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 write your data, whether MySQL allows it or not, unbeknownst to MySQL itself - i.e. it becomes possible to silently corrupt whole databases.
• the above might sometimes 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 mysqladmin no 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".

(不用说，对于任何Web或FTP问题， real 几乎都是从来没有.前门而她不能进入我们的家"是检查钥匙或修理或更换了锁"；公认的更快的chown 777是只要将前门敞开！轻松自在！这可能是最糟糕的情况了"发生了?)

(needless to say, it's almost never the real fix 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 777 is "Just leave the front door wide open! Easy peasy! What's the worst that might happen?")

此代码表示目录不为空".该目录包含MySQL不了解的某些隐藏文件.对于非隐藏文件，请参见Errno17.解决方案相同.

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

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

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 filename had 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.

可重复性:

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

这是在安装了Wordpress的Linux系统上发生的.不幸的是，客户受到时间限制，我既无法对磁盘进行映像也无法进行真正的取证工作-我重新安装了整台计算机，并且Wordpress在此过程中得到了更新，所以我只能说我差不多了. >确定他们是通过此插件完成的.

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 almost certain they did it through this plugin.

症状:mysql数据目录包含三个扩展名为PHP的文件. 等等，什么?！?-文件中有大量base64代码，这些代码已传递给base64_decode，gzuncompress和[eval()][2]. 啊哈.当然，这些只是第一次尝试，没有成功.该网站运行良好，而且是真正的pwn3d.

Symptoms: the mysql data 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, gzuncompress and [eval()][2]. Aha. Of course these were only the first attempts, the unsuccessful ones. The site had been well and truly pwn3d.

因此，如果您在mysql数据目录中找到导致错误17的文件，请使用file实用工具进行检查或使用防病毒软件对其进行扫描.或目视检查其内容. 不要以为它存在一些无害的错误.

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

(不用说，要目视检查文件，不要双击它).

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

在这种情况下的受害者(他有一个朋友做维护")永远不会猜到自己已经被黑客入侵，直到维护/更新/任何脚本运行了DROP DATABASE(不要问我为什么-我不确定我什至不知道)并收到错误消息.从CPU负载和syslog消息来看，我非常肯定主机已成为垃圾邮件场.

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.

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

If you rsync or copy between two MySQL installations of the same version but different platform or file systems such as Linux or Windows (which is discouraged, and risky, but many do it nonetheless), and specifically with different case sensitivity settings, you can accidentally end up with two versions of the same file (either data, index, or metadata); say Customers.myi and 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 ... | ... mysql backup schemes, the DROP will fail because that extra file (or those extra 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.

通常，您可以通过检查my.cnf文件(在Linux上是/etc/my.cnf，/etc/sysconfig/my.cnf，/etc/mysql/my.cnf；在Windows中是MySQL程序文件目录中的my.ini)来找到数据目录， c32>标题，如datadir.

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

或者，您也可以要求MySQL本身:

Alternatively you can ask it to MySQL itself:

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

这篇关于MySQL:删除数据库时出错(errno 13; errno 17; errno 39)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！