MySQL Bugs: #51834: Case Sensitivity table name problem with Windows

Bug #51834	Case Sensitivity table name problem with Windows
Submitted:	8 Mar 2010 15:56	Modified:	9 Mar 2010 19:53
Reporter:	Bob Hansen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.1.44, 5.0, 5.1, 5.5.99	OS:	Any (MS Windos XP Pro SP3 32bit, Mac OS X)
Assigned to:		CPU Architecture:	Any
Tags:	Table Name Case Sensitive Sensitivity Windows Store Created

Description:
Just upgraded from 5.1.40 to 5.1.44 and still same behavior.

Here's the situation. I'm creating tables like other tables, and they are being created lower case, even though I specified "Make table names" as "2 - Store as Created, Case Insensitive".

DatabaseA:
-0910_Courses
-0910_coursesections

DatabaseB:
(empty)

If I use "0 - Store as Created, Case Sensitive" and I use the syntax
CREATE TABLE DatabaseB.0910_Courses LIKE DatabaseA.0910_Courses;
It creates DatabaseB.0910_Courses.

If I use "2 - Store as Created, Case Insensitive" and I use the syntax
CREATE TABLE DatabaseB.0910_Courses LIKE DatabaseA.0910_Courses;
It creates DatabaseB.0910_courses.

So the problem is that the server is requested to create 0910_Courses but actually creates 0910_courses, which is not storing as created.

How to repeat:
Restore the (attached) .sql file to create the tables and try it for yourself.

Suggested fix:
It is acting the way I would expect it to act if I was using the option "1 - Store in Lowercase, Case Insensitive". Check the code for case "2" to ensure it forces case sensitivity.

This may be similar to
http://bugs.mysql.com/bug.php?id=20356

I also want to add that if I change the setting to "0 - Store as Created, Case Sensitive" that it doesn't appear to be forcing case sensitivity for read actions.

SELECT * FROM 0910_Courses;
and
SELECT * FROM 0910_courses;
both execute with success.

Thank you for the report.

Verified as described:

mysql> create table 0910_Courses(f1 int);
Query OK, 0 rows affected (0.18 sec)

mysql> create table 0911_Courses like 0910_Courses ;
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+---------------------------+
| Tables_in_test            |
+---------------------------+
| 0910_Courses              | 
| 0911_courses              | 
...

mysql> select @@lower_case_table_names ;
+--------------------------+
| @@lower_case_table_names |
+--------------------------+
|                        2 |
+--------------------------+
1 row in set (0.00 sec)

One more thing that might help.

If I'm using "0 - Store as Created, Case Sensitive"
and I try to change the name of a table to be upper case

ALTER TABLE `DatabaseA`.`0910_coursesections` RENAME TO `DatabaseA`.`0910_CourseSections`;

then I get the error

MySQL Error Number 1050
Table '0910_CourseSections' already exists

The only two tables in the database are
-0910_Courses
-0910_coursesections

Bad news. I just tried it on Mac version 5.0.82 and its much worse. If you change to type "0" for example and create a new table, it may immediately be invisible and in-accessible. Furthermore, tables that already existed are in-accessible. I hope you can figure this one out.

CREATE 1011_Courses LIKE 0910_Courses, while on type "0". Existing table 0910_Courses cannot be found.

Attachment: editTableOnMac-51834.JPG (image/jpeg, text), 53.51 KiB.

CREATE'd 1011_Courses while using type "0". Switched to type "2" and did SELECT * FROM 1011_Courses.

Attachment: selectFromNewTable-51834.JPG (image/jpeg, text), 100.27 KiB.