MySQL Bugs: #43514: Drop MyISAM fails on Windows with lower_case_table

Bug #43514	Drop MyISAM fails on Windows with lower_case_table_names = 2
Submitted:	9 Mar 2009 21:16	Modified:	10 Mar 2009 14:12
Reporter:	Chuck Bell	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	6.0.11	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
During a test of using the --lower_case_table_name startup option on Windows, the MyISAM table files are not being deleted properly.

When a table is created in a database with an uppercase name and later the table is dropped, the *.my* files are not removed and subsequent DROP DATABASE commands will fail. 

Notes: 
* This problem does not occur when using the memory storage engine.
* This problem should be checked for all storage engines.

How to repeat:
Build the mysql-6.0-backup tree.

Start the server with --lower_case_table_names = 2 on  Windows.

Use this test case:

CREATE DATABASE UPPER;
CREATE TABLE UPPER.t1 (a int) ENGINE=MYISAM;
INSERT INTO UPPER.t1 VALUES (1), (2), (3);
BACKUP DATABASE UPPER TO 'upper.bak';
DROP TABLE upper.t1;
DROP DATABASE UPPER;

The DROP DATABASE will fail with this error:

mysql> DROP DATABASE UPPER;
ERROR 1010 (HY000): Error dropping database (can't rmdir '.\upper', errno: 41)

If you check the datadir/UPPER folder, you will see the t1.myd and t1.myi files.

Note: This problem does not occur in the main tree.

Suggested fix:
Unknown -- requires research and possibly consulting with MyISAM developers (possibly a locking/file handle release error on Windows). The goal is to correct any anomolies for when --lower_case_table_names is set to mode 2 and backup is run.

Thank you for the bug report.

c:\dbs\6.0b>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.11-alpha-Win x64 revno 2792 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE UPPER;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE TABLE UPPER.t1 (a int) ENGINE=MYISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO UPPER.t1 VALUES (1), (2), (3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> BACKUP DATABASE UPPER TO 'upper.bak';
+-----------+
| backup_id |
+-----------+
| 539       |
+-----------+
1 row in set (1.39 sec)

mysql> DROP TABLE upper.t1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP DATABASE UPPER;
ERROR 1010 (HY000): Error dropping database (can't rmdir '.\upper', errno: 41)
mysql>

This problem is not related to backup. The problem can be detected using this test:

CREATE DATABASE UPPER;
CREATE TABLE UPPER.t1 (a int) ENGINE=MYISAM;
INSERT INTO UPPER.t1 VALUES (1), (2), (3);
DROP TABLE upper.t1;
DROP DATABASE UPPER;

The error still appears. Code was traced to mi_delete_table.c @ line#61 where the call to my_delete_with_symlink(from, MYF(MY_WME)) failed to delete the t1.MYI and t1.MYD files.

If you change the test above to another storage engine, the DROPs work as expected.

This problem is not related to backup. The problem can be detected using this test:

CREATE DATABASE UPPER;
CREATE TABLE UPPER.t1 (a int) ENGINE=MYISAM;
INSERT INTO UPPER.t1 VALUES (1), (2), (3);
DROP TABLE upper.t1;
DROP DATABASE UPPER;

The error still appears. Code was traced to mi_delete_table.c @ line#61 where the call to my_delete_with_symlink(from, MYF(MY_WME)) failed to delete the t1.MYI and t1.MYD files.

If you change the test above to another storage engine, the DROPs work as expected.

Title changed accordingly.