Bug #13 Case-sensitivity problem on Mac OS X

• Submitted: 13 Dec 2002 21:55
• Modified: 15 Dec 2003 15:04
• Reporter: Lenz Grimmer
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.6
• OS: MacOS (Mac OS X)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[13 Dec 2002 21:55] Lenz Grimmer

Description:
It seems like there is a problem with Case sensitivity in database 
names on Mac OS X - even though only one database exists, the capitalization 
of the table name seems to make a difference in queries. 

How to repeat:
USE test; 
 
CREATE TABLE City ( 
  ID int(11) NOT NULL auto_increment, 
  name char(35) NOT NULL default '', 
  Country char(3) NOT NULL default '', 
  District char(20) NOT NULL default '', 
  Population int(11) NOT NULL default '0', 
  PRIMARY KEY  (ID) 
) TYPE=MyISAM; 
 
 
INSERT INTO City VALUES (1,'Kabul','AFG','Kabol',1780000); 
INSERT INTO City VALUES (2,'Qandahar','AFG','Qandahar',237500); 
# Note the lowercase "city" in the next INSERT query 
INSERT INTO city VALUES (3, 'Herat','AFG','Herat',186800); 
 
SELECT * FROM City; 
 
+----+----------+---------+----------+------------+ 
| ID | name     | Country | District | Population | 
+----+----------+---------+----------+------------+ 
|  1 | Kabul    | AFG     | Kabol    |    1780000 | 
|  2 | Qandahar | AFG     | Qandahar |     237500 | 
+----+----------+---------+----------+------------+ 
 
SELECT * FROM city; 
 
+----+----------+---------+----------+------------+ 
| ID | name     | Country | District | Population | 
+----+----------+---------+----------+------------+ 
|  1 | Kabul    | AFG     | Kabol    |    1780000 | 
|  2 | Qandahar | AFG     | Qandahar |     237500 | 
|  3 | Herat    | AFG     | Herat    |     186800 | 
+----+----------+---------+----------+------------+ 
 
SHOW TABLES; 
 
+----------------+ 
| Tables_in_test | 
+----------------+ 
| City           | 
+----------------+ 
 

Suggested fix:
N/A

[13 Dec 2002 22:00] MySQL Developer

Actually, it must of course mean "problem with case  
sensitivity in *table* names" - sorry for the confusion.

[21 Dec 2002 8:41] MySQL Developer

Fixed in 4.0.6 by changing configure to make table names case insensitive on MacOSX

[15 Dec 2003 15:04] Michael Widenius

In MySQL 4.0.17 we test the case sensitive-ness of the file system and set lower_case_table_names to 1 if the file system is case-senstive.

This will fix problems like this...