MySQL: database flush error (errno 13; errno 17; errno 39)

Question

I was unable to delete the database:

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

The db / mydb directory exists in the mysql tree but does not have a 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 delete the database no matter what (but please read the entire post first: the error was given for a reason , and it may be important to know what the reason is!), You can:

• find the data directory with the command SHOW VARIABLES WHERE Variable_name LIKE '%datadir%';
• stop the MySQL server (for example, service mysql stop or service mysql stop rcmysqld stop or similar on Linux, NET STOP <name of MYSQL service, often MYSQL57 or similar> or via SERVICES.MSC on Windows)
• go to datadir (this is where you should research; see below)
• delete directory with the same name as the database
• start the MySQL server again and connect to it
• execute DROP DATABASE
• this is!

Reasons for Errno 13

MySQL does not have write permissions to the parent directory in which mydb folder.

Check it out 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 the MySQL and AppArmor / SELinux packages. What happens is that AppArmor expects mysqld to store its data in /path/to/data/dir , and allows full R / W there, but MySQLd is from a different distribution or assembly and actually stores its data elsewhere ( for example: /var/lib/mysql5/data/** unlike /var/lib/mysql/** ). So, you see that the directory has the correct access rights and ownership, and yet it gives Errno 13, because apparmor / selinux will not allow access to it.

To check this, check the system log for security breaches, manually check the apparmor / selinux configuration and / or impersonate the mysql user and try changing to the base var directory, then gradually increasing cd until you are in the target directory. and do something like touch aardvark && rm aardvark . If the permissions and ownership are the same, and yet the above leads to an access error, there is a possibility that this is a security infrastructure problem.

Reasons for Errno 39

This code means "the directory is not empty." The directory contains some hidden files that 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 does not think about deleting. Such files could be created using SELECT... INTO OUTFILE "filename"; command where filename has 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 database data directory, for example /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 out (or delete if not necessary) and try again. In addition, first determine why they were created - this may indicate an error in some application . Or worse: see below ...

UPDATE: Error 17 as exploit flag

This happened on a Linux system with Wordpress installed. Unfortunately, the client had time constraints, and I could not create a disk image or conduct a real analysis - I reinstalled the entire machine and Wordpress was updated in the process, so I can only say that I'm almost sure that they did it through this plugin .

Symptoms : the mysql data directory contained three files with a .ppx extension. What to expect?!? - and inside the files there was most of the base64 code that was passed to base64_decode , gzuncompress and [eval()][2] . Yeah. Of course, these were only the first attempts, unsuccessful. The site was good and truly pwn3d.

Therefore, if you find a file in your mysql data directory that causes error 17, check it with the file utility or scan it with antivirus. Or visually inspect its contents. Do not take it there for some harmless mistake.

The victim in this case (he had some kind of friend “doing maintenance”) would never have guessed that he was hacked until the maintenance / update / any other script ran DROP DATABASE (don't ask me why - I'm not sure , even I want to know) and got an error. Judging by the processor load and syslog messages, I’m pretty sure that the host has turned into a spam farm.

Another mistake 17

If you run rsync or copy between the two MySQL installations of the same version, but with different platforms or file systems such as Linux or Windows (which is not recommended and risky, but many nonetheless do this), and, in in particular, with different case sensitivity settings, you may accidentally get two versions of the same file (data, index or metadata); say Customers.myi and Customer.MYI . MySQL uses one of them and knows nothing about the other (which may be outdated and lead to catastrophic synchronization). When you delete a database, which also happens in many mysqldump... |... mysql mysqldump... |... mysql backup schemes mysqldump... |... mysql DROP will work because this extra file ( or these additional files) exists. If this happens, you will be able to recognize obsolete files that need to be manually deleted from the file’s time, or from the fact that their access scheme is different from most other tables.

Finding Directory Data

In general, you can find the data directory, /etc/my.cnf /etc/sysconfig/my.cnf my.cnf ( /etc/my.cnf , /etc/sysconfig/my.cnf , /etc/mysql/my.cnf on Linux; my.ini in MySQL directory of program files on Windows), under the heading [mysqld] , like 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) 

Answer 2

In my case, this was due to the lower_case_table_names parameter.

The error number 39 was issued when I tried to reset the databases that contain the names of the upper case tables with the lower_case_table_names parameter enabled.

This is fixed by the return of lowercase changes to the previous state.

Answer 3

Just go to / opt / lampp / var / mysql

Here you can find your datavase name. Open this folder. Delete if there are files in it

Now go to phpmyadmin and omit this database

Answer 4

As for ERRORCODE 39 , you can simply delete the physical table files on disk. the location depends on the distribution and the settings of your OS. On Debian, it is usually located in / var / lib / mysql / database_name /. So do:

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

And then delete the database from the tool of your choice or with the command:

 DROP DATABASE <database_name> 

Answer 5

In my case, an additional file not belonging to the database was inside the database folder. Mysql found that the folder is not empty after deleting all the tables that caused the error. I delete the file and the database is working fine.

Answer 6

on Linux, just go to "/ var / lib / mysql" with the right mouse button and (open as adminstrator), find the folder corresponding to your database name inside the mysql folder and delete it. this is. The database has been deleted.