MySQL Bugs: #7655: MySQL server ignores case mismatch in table names on NTFS

Bug #7655	MySQL server ignores case mismatch in table names on NTFS
Submitted:	4 Jan 2005 10:54	Modified:	4 Jan 2005 14:24
Reporter:	Andrew Morrow	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.x	OS:	Windows (Microsoft Windows)
Assigned to:		CPU Architecture:	Any

Description:
The MySQL server ignores upper/lower case errors in table names if the underlying filesystem ignores such errors.

On Windows (and probably on Mac OS X), the typical underlying filesystem with match file names in a case insensitive manner.  The MySQL server will also forgive the case mismatch in table names.  This is not so on UNIX/Linux , only because the underlying filesystems on those platforms are case-sensitive.

If the application is developed on these case-insensitive filesystems, the porting effort encounters errors when porting to the case-sensitive filesystems.

This is common porting problem. You might compare it to the same problem with Java class names that I recported as

http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=5046336

How to repeat:
Connect to a MS Windows-basedd server.

(In the following example, note, of course, that "abc" does not equal "ABC")

mysql> CREATE TABLE abc (  mytest int(11)  ) TYPE=MyISAM;
Query OK, 0 rows affected (0.11 sec)

mysql> select count(*) from ABC;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql>

This should have returned, instead, a "Table 'ABC'' doesn't exist"

Suggested fix:
Add a feature to strctly enforce case-sensitive table names, even if the underlying filesystems is not case-sensitive.  This would allow this kind of error to be detected during the development phase on all platforms rather than the porting phase.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is documented in the manual chapter 
"MySQL on Windows Compared to MySQL on Unix"
( http://dev.mysql.com/doc/mysql/en/Windows_vs_Unix.html )
and  in more detail in the manual section
"Identifier Case Sensitivity"
( http://dev.mysql.com/doc/mysql/en/Name_case_sensitivity.html )

Case-insensitive names
    Filenames are not case sensitive on Windows, so MySQL database and table names are also not case sensitive on Windows. The only restriction is that database and table names must be specified using the same case throughout a given statement. See section 9.2.2 Identifier Case Sensitivity.

If you port from Windows, you can simply disable case-sensitivity on Unix too (see the manual for lower_case_table_names) to safe efforts on porting phase