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:Microsoft Windows
Assigned to: CPU Architecture:Any

[9 Mar 2009 21:16] Chuck Bell
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.
[10 Mar 2009 14:12] MySQL Verification Team
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>
[29 Apr 2009 18:46] Chuck Bell
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.
[29 Apr 2009 18:46] Chuck Bell
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.