Migrating rails app from sqlite to mysql

I run a pretty simple rails site for pickup ultimate games in Fort Collins, hosted at Dreamhost. For many months, it had been working just fine using sqlite for the database. Somehow in the last few weeks, it developed a locking issue and has just not worked right since.

Why the locking issue all of a sudden? Perhaps it experienced concurrent requests for the first time? Maybe some change to dreamhost’s NFS setup, or the mod_rails/passenger setup? Who knows. But whatever the reason, sqlite was no longer working, so I migrated the site to mysql.

Two steps involved: create the mysql databases, and transfer the data from the sqlite dbs to the mysql db’s. I was using sqlite because it was easy, essentially no overhead for setup / admin (at a cost, obviously). I created a devel and test db on my mysql instance at home, and the production db on the dreamhost-provided mysql server. Updated the rails config to match.

Migrating the data was the only part with any gotchas. In theory, something like this should work:

echo .dump | sqlite3 production.sqlite3 |  \
    mysql -u username --password="passwd" -h myhost production

Dump from sqlite, import directly into mysql. The issue is some differences in the SQL dialect between sqlite and mysql. See that article at StackOverflow for the details, but it ended up being a matter of a few minor edits to be able to import the dump into the new mysql production database.

I expect many more months of smooth sailing from here…