Panasonic Youth rob sanheim writes about software, business, ruby, music, stuff and things

Rails Schema Dumper + Mysql ENUM’s = Bad For Business

Yesterday was burned on trying to get our legacy database working again with Rails. I say "again" as we already had things working with a dual db setup - where the legacy stuff was just always there and never needed to be re-created via schema dump and "rake clone_db_structure". The plan was to throw all the tables together into the legacy database, remove the AlternateDatabase hacks, tweak the db to remove enum's we were indexing on, merge/create all that into one database, and flip the switch to be on one database. Of course, things never go that smoothly...in the process I learned more about what actually happens you type "rake", why enums are bad, and the importance of checking the obvious.

My problems started with test failures that Jim wasn't getting - which initially made me think I had made an error in the process of copying and munging local databases. After some retries, ensuring I was fully up to date, and some help from Jim in making sure I had the process right, I still had errors and yet Jim's machine passed all tests just fine. Looking through the logs revealed different mysql errors, but I was confused as to why I would be the only one to get them. Thats the worst kind of failure.

Finally, I got smart and asked for Jim's mysql version. His was 4.0.something, mine was 5.0.16. A hah - a prime suspect. I knew from experience that mysql's engine tends to be very lax with constraints and keys, particularily with earlier versions when it was less enterprisey. So now I realized it was probably something that Rails' schema dumper created in schema.rb that Mysql 5 hated but Mysql 4 was okay with. And it was - in schema.rb I saw multiple fields like this:

So we had a string column with a zero length (or limit) but a default value - obviously these constraints don't make sense, and Mysql errors out on it. Mysql 4 just went on cruising with it for Jim, probably ignoring the default silently. Eventually, we changed every single Enum column in the legacy dev database to an equivalent varchar column. This allowed the schema dumper to create a valid schema.rb, which would then be used by rake for creating the test database and running everything. Green bar, finally.

Lessons Learned:

• ActiveRecord doesn't like Enums - we initially thought we would be okay by just changing the enums that were part of indexes. We were wrong. Don't use enums and watch out for legacy tables with them.
• Schema dumper silently converts enums into string fields with a length of zero, so be careful as your DB could very well error out on it. Someone should update and apply this patch so there is more warning!
• Don't just rely on autotest for the health of your code - run the full rake too, as there are slight differences that can result in failures in one and not the other.
• Make sure everyone is on the same version of everything for their dev environment. This should be an obvious first step, but I overlooked it.

“Make sure everyone is on the same version of everything for their dev environment. This should be an obvious first step, but I overlooked it.”

I don’t know about that one. Unless you can guarantee that your deployment environment will be the same as your development evironment then having a heterogenus environment would help you spot problems for deplyment later on (for example if you’d both been using mysql 4, you wouldn’t have caught this problem).

kogent: generally, you do want your dev environment to match your deployment environment - at least in areas you can easily control, like DB versions and of course framework versions. If you are doing QA testing because you need to deploy onto multiplle platforms, then I’d agree that you should have those environments locally too, as well as on your test server for constant testing.

The usual case with Rails deployment, though, is having a set deployment environment (mysql 5.x, Rails 1.x, Ruby 1.8.x, etc) - and all developers should match that to avoid having the “it works on my machine!” problem. If we were talking rich desktop apps that need to work on win32, mac, etc, then its a whole ‘nother ballgame.

Thanks for this post, I just got hit with this same issue and I was afraid I was the only one!