December 20, 2005
Files that Don't Belong in MySQL Data Directories
If you've ever been tempted to use your MySQL data directory for storing things other than that which is created by MySQL, don't. Today I went to DROP a database in our dev environment (in order to rebuild it with a dump from production) and got this response:
mysql-dev> DROP DATABASE medical_admin; ERROR 1010 (HY000): Error dropping database (can't rmdir './medical_admin/', errno: 17)
I've gotten this very error when triggers were first introduced and the .TRG file wasn't being removed on the DROP (we do not have triggers on this database though, and that bug was fixed). So I trucked (cd) over to the data dir and sure enough, an ls -l on the /data/mysql/medical_admin dirictory revealed:
/data/mysql> ls -l medical_admin total 1 -rwxr-xr-- 1 mysql mysql 175 Nov 15 13:30 changed_evaluations.dat
Not good. Who knows why that was stuck there, but let me just reiterate that there are much better places for such files. If the file was huge, like many, many gigabytes, and it only fit on our large storage array I could be more forgiving if it was in /data or even /data/mysql, but not down in the database directory. In this case it looks like ~ or /tmp would be a much better fit.
Posted by mike at December 20, 2005 4:57 PM