Table names and case sensitivity – bad MySQL

In: Web development

17 Jun 2004

This issue has wasted a good part of a day trying to figure out why my database-driven pages failed to work correctly after dumping a database from my Windows dev machine into a Linux box – I just had to whine about this.

Following database naming conventions (actually, there are many, not all of which advocate using mixed case naming schemes), I decided to use mixed case table names when developing on my Windows box. So a table containing localization (or L10n) information for widgets would be named `Widget_L10n`, and the lookup table for widget to purchases would be named `Widget_Purchases`. No problem. Everything worked fine on the dev machine.

When the time came to move into live testing, I dumped the contents of the database into a file and proceeded to import into the production database server. Of course, nothing worked. I checked the database contents – everything was there. I checked the scripts – everything was good to go; if it works in the dev machine, it should rightly work in the production. I then checked the dump file, thinking it unlikely that something was wrong there but checking anyway – everything seemed fine. I went back to the MySQL client (I was using MySQL Front) and it hit me – the table names were in lowercase. And the PHP scripts were (correctly) using the mixed-case table names. Gah! The database dump file contained queries with the table names in lowercase, so they were lowercase in the Linux box while I was using queries with mixed-case names. At that point I hated myself for forgetting how MySQL stores table names – as a name of the file that stores the table. The manual said so and I even read that before.

Lesson learnt: use all lowercase identifier names in MySQL for portability across Windows and operating/file systems where lettercase matters (such as Linux and Mac OX X UFS volumes). Throw the existing database naming conventions you have out the door.

5 Responses to Table names and case sensitivity – bad MySQL

Avatar

Richard@Home

June 17th, 2004 at 11pm

I got caught with this myself, now EVERY table in EVERY database I devlop has lower_case_only table/field names. Makes life a whole lot easier when porting to other back ends too. An added bonus is that (IMHO) it helps make your SQL easier to read:

SELECT my_table.id, my_table.name
FROM my_table
INNER JOIN my_other table
ON my_table.id=my_other_table.id
WHERE my_table.name LIKE ‘a%’

Avatar

Manuzhai

June 17th, 2004 at 11pm

I got bitten by this once. That time, it turned out to be an actual bug in MySQL in the handling of database names on Windows, but it’s a hell of a nasty problem.

Avatar

minghong

June 18th, 2004 at 12pm

Isn’t that SQL is case insensitive?
So object names should be case insensitive too.
Some SQL vendors support case sensitive object name, but need to use double quote to quote them, e.g.:

CREATE table “Widget_Purchases”
( …… );

But I’m not sure about MySQL. MySQL is bad… Try Firebird Database or PostgreSQL instead.

Avatar

Mike Hillyer

June 19th, 2004 at 4am

And here is your solution:

http://dev.mysql.com/doc/mysql/en/Name_case_sensitivity.html

lower_case_table_names=1

Add that to your my.cnf on Linux and your queries will have their table names converted to lowercase by the server on the fly, solving your problem in one line and a server restart.

Avatar

cactus

July 1st, 2004 at 2pm

And one simple rule that never fails to save a lot of grief and unnecessary debugging – develop on a platform that is as similar to the production machine as possible. In the case of a Linux production server, that shouldn’t be too hard to do!