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
Triage: Triaged: D3 (Medium) / R3 (Medium) / E2 (Low)

[8 Mar 2010 15:56] Bob Hansen
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
[8 Mar 2010 16:11] Bob Hansen
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.
[9 Mar 2010 8:08] Sveta Smirnova
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)
[9 Mar 2010 19:26] Bob Hansen
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
[9 Mar 2010 19:53] Bob Hansen
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.
[9 Mar 2010 19:54] Bob Hansen
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.

[9 Mar 2010 20:00] Bob Hansen
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.