MySQL Bugs: #73820: Database can not be dropped if it contains .CFG files

Bug #73820	Database can not be dropped if it contains .CFG files
Submitted:	5 Sep 2014 18:02	Modified:	31 Oct 2014 15:46
Reporter:	Justin Swanhart	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.6, 5.6.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
FLUSH TABLES FOR EXPORT creates .cfg files for the exported tables.

When DROP DATABASE is used it fails if the directory contains any .cfg files that have not been manually removed

How to repeat:
Use FLUSH TABLES FOR EXPORT to export a table, then immediately try to drop the database.  

Suggested fix:
Automatically remove .cfg files when dropping databases (just like .TRN, .TRG, etc are removed automatically)

Hello Justin,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

///

mysql> use test1;
Database changed
mysql>
mysql> create table t1(id int not null primary key)engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> \! ls -l /tmp/master/test1
total 112
-rw-rw---- 1 ushastry ushastry    65 Sep  8 06:05 db.opt
-rw-rw---- 1 ushastry ushastry  8556 Sep  8 06:05 t1.frm
-rw-rw---- 1 ushastry ushastry 98304 Sep  8 06:05 t1.ibd
mysql>
mysql> FLUSH TABLES t1 FOR EXPORT;
Query OK, 0 rows affected (0.00 sec)

mysql> \! ls -l /tmp/master/test1
total 116
-rw-rw---- 1 ushastry ushastry    65 Sep  8 06:05 db.opt
-rw-rw---- 1 ushastry ushastry   334 Sep  8 06:10 t1.cfg
-rw-rw---- 1 ushastry ushastry  8556 Sep  8 06:05 t1.frm
-rw-rw---- 1 ushastry ushastry 98304 Sep  8 06:05 t1.ibd
mysql>
mysql> DROP DATABASE test1;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
mysql>
mysql> \! ls -l /tmp/master/test1
total 116
-rw-rw---- 1 ushastry ushastry    65 Sep  8 06:05 db.opt
-rw-rw---- 1 ushastry ushastry   334 Sep  8 06:10 t1.cfg
-rw-rw---- 1 ushastry ushastry  8556 Sep  8 06:05 t1.frm
-rw-rw---- 1 ushastry ushastry 98304 Sep  8 06:05 t1.ibd
mysql>

// Just for fun copied t1.cfg to test.cfg

mysql> DROP DATABASE test1;
ERROR 1010 (HY000): Error dropping database (can't rmdir './test1/', errno: 17)
mysql>
mysql> \q

[ushastry@cluster-repo mysql-advanced-5.6.21]$ bin/perror 17
OS error code  17:  File exists

A hopeful translation of the bug report:

session 1> FLUSH TABLES test.t1 FOR EXPORT;
session 2> DROP DATABASE test; # Waits for table to be unlocked
session 1> UNLOCK TABLES;
session 2> # Drop database returns an error
session 2> DROP DATABASE test; # Succeeds

Likely the table lock is being released before the .cfg file is removed. (UNLOCK TABLES causes the .cfg file to be removed).

I think what happened in the customer system is that a table had been imported previously, and the .cfg file was not removed after ALTER TABLE ... IMPORT TABLESPACE .  Later the database containing the table (and still containing .cfg) was dropped but got and error because the file still exists (this is similar to the copy made by Umesh).

Noted in 5.7.6 changelog.

DROP DATABASE failed if the database directory contained .cfg files
(such as created by FLUSH TABLES FOR EXPORT ).