Ruby, Rails, Firefox, Anime, Mac
In: Web development17 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.