MySQL error while dropping databases

Category:

Tutorials > Database Management Systems > MySQL > Administration

A few months back I was trying to drop some unnecessary test and archived databases from MySQL servers and while dropping a database I got the following error message.

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

First, I thought it was related to directory permissions ie. MySQL user's write permissions on the database directory were revoked by someone for some strange reason. I opened another SSH sessions on the same server and took a look into the MySQL data directory but strangely did not find any permission related issues and MySQL user had the right ownership and permissions on data directory. I was also thinking may be someone locked the directory file somehow but then I listed the contents of database directory dw_test/ and found out that there was a file inside dw_test/ directory. This was the file that usually gets created by our backup script containing the binary log coordinates of the server where the backup was taken from and because of the presence of the file MySQL was denied deletion of the folder and hence the database (although MySQL had successfully deleted all table and index files from the directory). Basically, this dw_test was a copy of some other database created by someone for testing purposes.

$ sudo rm /data1/mysql/data/dw_test/log.status

Then, I logged back in to MySQL cli and ran the following with success.

drop database dw_test;
Query OK, 0 rows affected (0.08 sec)

Did this tutorial help a little? How about buy me a cup of coffee?

Buy me a coffee at ko-fi.com

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I recommend thoroughy testing on a production-like test system first before moving to production.

Comments (write a comment):

A quick "perror 17" would have told you "File exists" and that might have steered the search a little faster. Posted by: Scott on Jan 06, 2011

Did you use the perror utility?

$ perror 17
OS error code 17: File exists Posted by: Daniël van Eeden on Jan 07, 2011

thanks for sharing your thoughts guys but I do not use perror that often because of its nondescriptive error messages but you are right I could have used perror if I wanted to.

Thanks. Posted by: shahryar ghazi on Jan 07, 2011

# find `grep datadir /etc/my.cnf | awk -F"=" '{print $2}'` -type f | egrep -v 'frm|MYD|MYI|TRN|TRG|opt'

This will look for junk files in datadir Posted by: Shantanu Oak on Jan 20, 2011

I got exactly the same error when I attempted to drop one of my databases.

For my part, it was a file still existing into the directory created before with the OUTFILE command...
Thus, always paid attention to check the directory before to drop it! Posted by: SuperYannou on Mar 11, 2011

Thanks , saved my day,, Posted by: Ravi on Aug 12, 2011

log into the data dir and go into which database u want to delete and look into it u will find a junk file in it ...... ex .out delete that file then check it u will be able to delete the the database u want to delete Posted by: sai on Feb 21, 2012

leave a comment on tutorial leave a comment