MySQL Bugs: #52116: database directories cannot be rm'd upon DROP DATABASE when foreign files exist

Bug #52116	database directories cannot be rm'd upon DROP DATABASE when foreign files exist
Submitted:	16 Mar 2010 20:53	Modified:	20 Mar 2010 17:33
Reporter:	Zachary Jones	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.51a, 5.1.46-bzr	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	blocked, Drop, file system

Description:
When executing a DROP DATABASE, the command fails when the directory containing the relevant database files contains foreign files (ie. those not added by MySQL).  The the failure message from the OS is identical to executing "rm /dir/" when the /dir/ is not empty.  The DROP DATABASE procedure is halted incomplete, and the database name remains in MySQL's master database, but it can no longer respond to normal commands.

How to repeat:
Create a new database {drop_bug_test}.  From the unix shell, su to root or another user with permission to add files to the MySQL database directories.  Find the directory containing the .MYI and .MYD files for {drop_bug_test}   Use vi or cp to add a file to this directory.  In the MySQL console, execute DROP DATABASE {drop_bug_test}

Suggested fix:
a) Have the files system code that controls the directory rm check for the existence of foreign files prior to removing the database files and issue a warning that said foreign files exist.  Suggest such files storage practices are deleterious. 

b) offer a config setting to automatically delete foreign files upon DROP.

Verified just as described:

77-52-28-202:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.46-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database file;
Query OK, 1 row affected (0.00 sec)

mysql> use file;
mysql> select 1 into outfile 'file/t.txt';
Query OK, 1 row affected (0.00 sec)

mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.40 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop database file;
ERROR 1010 (HY000): Error dropping database (can't rmdir './file/', errno: 17)
mysql> exit
Bye
77-52-28-202:5.1 openxs$ ls -la var/file/
total 8
drwx------   3 openxs  staff   102 17 бер 06:45 .
drwx------  37 openxs  staff  1258 17 бер 06:44 ..
-rw-rw-rw-   1 openxs  staff     2 17 бер 06:45 t.txt
77-52-28-202:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.46-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases like 'f%';
+---------------+
| Database (f%) |
+---------------+
| file          |
+---------------+
1 row in set (0.00 sec)

For goodness sake, don't do that!
MySQL will not silently remove files that don't belong to it. The contents of the database directory is private to the server and should not be meddled with.

DROP DATABASE should be atomic, if possible. Either it works successfully or it does not work at all. In this case it is possible to check for files that do NOT belong to the database.

If there are foreign files that prevent dropping database (lost+found in a mount point is a typical situation, also files created like in my test case), then why drop any content of the database? Just give error message and leave it. Check first if it is possible to do DROP successfully, and only then try to drop objects in the database.

I think we should consider this as a feature request at least.

Valeriy,
DROP DATABASE behaviour is undefined if the contents of the database directory has been meddled with.
Even when we make DROP DATABASE atomic, making it robust against vandalism is a different story.

I agree it is a possible feature request to make the server handle this situation. Feel free to classify it as such. But it's not the job of RDBMS to manage files on the filesystem that don't belong to it. On the same token a number of other "smart" things can be requested from it -- all to contribute functionality that is not a primary function of RDBMS.

My thanks for the detailed consideration.  I think that the interest to avoid non-RDBMS file management is valid, though this case is one that demands different handling because of the use of directories by the RDBMS.  DROP DATABASE should be atomic, since the state created by this bug results in a condition that the use does not seem to be able to fix.  I think that's worse for the image of the product than philosophical ideas about RBDMS operation.  In terms of priority, I think few people will ever experience this bug.