Sunday, October 21, 2007

Case Sensitivity in MySQL

So I have this application that I am writing that uses Struts 1.x and Hibernate with a MySQL back end. I am doing the actual development with Netbeans on Windows XP but the production server is a Linux machine running Redhat Enterprise 4. For a while everything went well in that I could deploy from the Windows machine onto the Linux environment and see no ill effects. Recently I ran head first into a brick wall with this arrangement.

I made some changes to my database schema and attempted to upload the application. While running the tests on the Linux environment I started getting some strange data related errors.

After fiddling around for several hours I realized that MySQL is case sensitive in the Linux environment but not in the Windows environment. Generally, I use all lower case names in the database schema so I never noticed before.

I carelessly (but probably luckily) used an uppercase name in the change that i made to the schema. Then using mysqldump to export the database on the Windows machine and re-import the database on the Linux server caused a unforeseen effect. Mysqldump lowercased all the identifiers in my schema. So Foo became foo.

I have no idea how the import did not complain...but Hibernate used my uppercase identifiers when generating the SQL statements and blam...all hell breaks loose.

I went through all my configurations for Hibernate to ensure that I did not make the mistake anywhere else, then re-created the database schema and re-deployed. Now everything works. I wonder what other surprises are in store for me for being so bold (read:stupid) as to work in a mixed environment.

No comments: