Switching to from SQLite to MySQL (Ruby on Rails)

Ruby on Rails is a great thing. Migrations in particular, allow for schema-agnostic database setups in production and development (so using MySQL for development, and then using something like SQLite for production is extremely easy to do).

Migrations really start to rock when you run into pitfalls that come with using other databases (and need to ditch them quickly). SQLite in particular, has some concurrency problems that if not taken care of, may turn your cheery development release into a night of swearing, and sweat. Not to fault SQLite (as it really is a performance monster if you can use it), there have been some attempts at making concurrency better in Rails.

In the mean time, using a fully fledged database like MySQL can quickly turn your application around if it is running into concurrency problems. The question though, is how.

  • Getting into the switch
    The first step is to get MySQL setup via a package manager, or compile it. Since there are potentially hundreds of ways (with different combinations) of doing it, you may need to read up on the specific method required for your distribution or operating system.

    For example:

    Debian based distributions use: apt-get install mysql

    Fedora/Red Hat/CentOS distributions use: yum install mysql

    (Note that you may need to look up the precise package name since there may be multiple versions of MySQL available with apt-cache search mysql and yum search mysql respectively).

    After finishing up, (and making sure the root password is changed with mysqladmin -u root password newpassword1, etc), creating the database with `CREATE DATABASE myproject`, making sure MySQL is listening only where you want it to, and making another user for only that database (using GRANT), its time to edit database.yml.

  • Making database.yml play fetch
    Editing database.yml and setting development and production to point at your SQL database will do it here (or simply adding a space behind db:migrate and typing RAILS_ENV=production might do it too). The normal database.yml should be commented enough to show you the light–but beware, database.yml is very picky about formatting. In particular, I hear that it is very picky with spaces versus tabs (although i’ve never run into that problem using RadRails, as it appears tabs are converted to spaces).

  • Adding the MySQL gem
    You’ll need to add the mysql gem:
    $ sudo gem install mysql

  • Getting your database up
    Now the goodness of migrations come along. Go into your rails project root directory (that would be where you see “config, app” and others).

    $ rake db:migrate

    (or as above, rake db:migrate RAILS_ENV=production)

    If it worked without any errors, you’re on the path to glory. If not, look at what error got dumped out (sans the huge trace that follows), and take a stick to your problem.

  • Migrating your production data
    Here is the stickler. Although you can migrate SQLite data dumps by hand using:

    $ sqlite3 database.db .dump > dump-file

    $ perl -pne s/”//g dump-file > dump-no-double-quote

    $ mysql -u someuser -p somedatabase < dump-no-double-quote

    And replace all the things you get errors with (note that if you have things like booleans in SQLite, they’re represented in the database as t/f, and MySQL represents them as 1/0)..it becomes a pain with a huge amount of data.

    I have not tested it out (as I did the above search and replace pecking), but you may want to try this instead.

    1For the security oriented, you may want to either erase the history file with `history -c`, or do the password change the old way — via the MySQL client (documentation everywhere for that).

  • 2 responses to “Switching to from SQLite to MySQL (Ruby on Rails)

    1. Phokz

      i followed your howto and found this issue – if you have some code (e.g. html) stored in database, it stops working because of missing doublequotes.

      You have to strip doublequotes only in command part of sql query, not from data.

      I’m going to give a try to manage_fixtures plugin.

    2. Max

      Nice post. I just made the switch to always be using MySQL in all my dev now. Your article definitely helped. Before I had to move a local sqlite database to production. I had to do this:
      http://www.maxkpage.com/blog/free-sqlite-to-mysql-converter-super-easy/ which some people may be wondering how to do.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out /  Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )


    Connecting to %s

    %d bloggers like this: