Bug #13 Case-sensitivity problem on Mac OS X
Submitted: 13 Dec 2002 21:55 Modified: 15 Dec 2003 15:04
Reporter: Lenz Grimmer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.6 OS:Mac OS X (Mac OS X)
Assigned to: Miguel Solorzano 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...