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

[5 Sep 2014 18:02] Justin Swanhart
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)
[6 Sep 2014 10:50] Umesh Shastry
Hello Justin,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[6 Sep 2014 11:33] Umesh Shastry
///

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
[7 Sep 2014 0:40] Davi Arnaut
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).
[17 Sep 2014 19:30] Justin Swanhart
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).
[31 Oct 2014 15:46] Paul Dubois
Noted in 5.7.6 changelog.

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