Case issue with MySQL (4/5) datasource on ColdFusion 8 for Linux

I was setting up my recently installed ubuntu development environment when I ran into a frustrating issue with how CF 8 connects to MySQL.

My current hosting company provides Windows for ColdFusion 8. For some reason, they don’t offer it on Linux. I went to phpMyAdmin and backed up my blog’s database so I could set it up on my laptop.

I recently installed ubuntu 7.10on my laptop and made sure to keep an eye on case issues as I noted in a previous entry (link below). I used the SQL script that was exported from phpMyAdmin and created a local copy of the database using MySQL Query Browser. I then created a user for that DB using MySQL Administrator.

Finally, I went to the CF8 Admin to create the data source for my local server. I selected MySQL (4/5) from the drop-down and filled out the form fields making very sure to match the database name’s case. When I submitted the form, I got an error trying to connect.

The name of the database was mixed case (i.e. “myDataBaseName”), but ColdFusion 8 converted the name to all lowercase (“mydatabasename”) when it created the connection string which caused the error.

I dropped the schema, changed the SQL file to create the database using an all lowercase name and recreated the database. I then recreated the user and updated the DSN entry in the CF admin. This time, of course, it worked.

So is this a bug with CF 8 or is there some connection string voodoo that would allow CF to use the mixed case database name? This doesn’t seem like too big an issue for most users, but if you were trying to move an application from Windows to Linux that used multiple database schemas and employed cross-schema joins, this could be a big, big problem.