MySQL Bugs: #49258: Corrupted tables after setting lower-case-table-names=0 in MyISAM on Windows

Bug #49258	Corrupted tables after setting lower-case-table-names=0 in MyISAM on Windows
Submitted:	1 Dec 2009 13:09	Modified:	2 Feb 2010 3:50
Reporter:	Bug me Not	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.0.86	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Setting lower-case-table-names=0 on a case-insensitive file-system with table names with camel-cased named makes indexes corrupted.

This is also noted on http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html (at the value-meaning-table), however I think that this still is a bug, because it only happens on a combination of Windows + MyISAM + camedcased-tablenames.

How to repeat:
- Set lower-case-table-names=0 in my.ini
- Execute CREATE statement with tablename TransactionItems, storage engine MyISAM and one BIGINT auto_increment primary key
- Execute some INSERT statements
- Execute CHECK TABLE statement on the table
- MySQL will report that the table needs to be repaired

Thank you for the bug report. Could you please provide a complete test case. I
couldn't repeat on my own:

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.89-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > create database d5;
Query OK, 1 row affected (0.03 sec)

mysql 5.0 > use d5
Database changed
mysql 5.0 > show variables like "%lower%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | ON    |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.00 sec)

mysql 5.0 > CREATE TABLE TransactionItems (id serial, col1 char(1));
Query OK, 0 rows affected (0.11 sec)

mysql 5.0 > show create table TransactionItems\G
*************************** 1. row ***************************
       Table: TransactionItems
Create Table: CREATE TABLE `TransactionItems` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `col1` char(1) default NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 5.0 > insert into TransactionItems (col1) values ("a");
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > insert into TransactionItems (col1) select col1 from TransactionItems;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.0 > insert into TransactionItems (col1) select col1 from TransactionItems;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.0 > insert into TransactionItems (col1) select col1 from TransactionItems;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql 5.0 > insert into TransactionItems (col1) select col1 from TransactionItems;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql 5.0 > insert into TransactionItems (col1) select col1 from TransactionItems;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql 5.0 > check table TransactionItems;
+---------------------+-------+----------+----------+
| Table               | Op    | Msg_type | Msg_text |
+---------------------+-------+----------+----------+
| d5.TransactionItems | check | status   | OK       |
+---------------------+-------+----------+----------+
1 row in set (0.00 sec)

Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".