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

Question

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:

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

What should I do?

Answer 1

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:

• 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!

Reasons for Errno 13

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

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

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.

"EASY FIX" considered harmful

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

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

(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?")

Reasons for Errno 39

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.

Reasons for Errno 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 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.

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

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.

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.

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

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.

Yet another Error 17

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.

Finding the data-dir

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.

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)