So I was preparing the data for this website locally on my machine. My staging environment was pretty much the same (same Java VM, JBoss AS, MySQL version, etc.) The only difference is the operating system, it's OS X and the live site is running on CentOS. I created a package to deploy and everything looked fine. Until I edited documents on the live site. MySQL started to throw constraint violation errors it didn't show in the staging environment.
Now, it took me about half an hour to realize what was going on and I hope this will save someone some time:
- MySQL is case sensitive or not wrt identifiers in the database catalog.
- This depends on the operating system it is running on, which clearly doesn't make any sense but you learn it at some point. Table names, for example in SQL queries or even in foreign key constraint declarations (references FOO...) have to be specified in the correct case if you run MySQL on Linux but not if you run it on OS X.
I know this and I have written all database identifiers in uppercase everywhere, in all source and scripts. What I didn't expect was that the mysqldump tool, which I used to export the package for deployment from the staging database, generates all constraint declarations in the exported file with lowercase references 'foo'.
This you'd usually consider a regular bug in a tool, if MySQL would have caught it on import. But no, it silently ignored these obviously illegal constraint declarations (because the target table can't be found), imported them anyway, and then failed at runtime with a constraint violation. Nice, eh?
The more I work with MySQL, the more I ask myself why I put up with it. There are so many screwy things like this in MySQL that frankly it is unacceptable. In Seam In Action I am going to be demonstrating the H2 database. The reason for this is that in
Been there, done that! That happened to me after staying awake 24 hours straigh, desperately trying to make a deadline 2 hours later. It's ridicoulus, and having BLOBS in the db didn't help when trying to awk it.
rant {
Speaking about wasted time, I must say that although MySQL has some quirks, classloading issues is the issue I spend most time on. The improvements made in java 6 and Jboss AS 4.2 helps debugging, but not being able to pass object references between EARs, come on! Thats a show stopper if any. Jar hell is closing in on everyone, especially with all the runtime dependency resolving that takes place in Java EE environments. I hate having to spend hours on ibiblio trying to fill out my poms for dependencies, and to filter out clashing classes. O boy is that making me sick of Java.
}
MySQL is case sensitive or not wrt identifiers in the database catalog.
This was the first thing told to my by my mySQL teacher. I still face problem with it sometime, its not as such hard to fix this. Like someone said, practice makes the man perfect.
Things would remain harder until you learn about MySQL case sensitiveness. Nice read anyways.