This post originated from an RSS feed registered with Web Buzz by Cheah Chu Yeow. Original Post: Table names and case sensitivity - bad MySQL Feed Title: redemption in a blog Feed URL: http://blog.codefront.net/xml/rss20/feed.xml Feed Description: ramblings of a misfit - web development, Mozilla, Firefox, Thunderbird, CSS, programming Latest Web Buzz Posts Latest Web Buzz Posts by Cheah Chu Yeow Latest Posts From redemption in a blog

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.