Text

· · to read

I’m developing a Rails site that uses MySQL and runs on Heroku. I’m using Xeround for the database, which has a free trial. Xeround is a database PaaS (Platform as a Service) that runs on several places, including Amazon Web Services in their U. S. East region, which is where Heroku runs. Running it in the same place where Heroku runs gives it a low latency.

To set up Xeround, sign up for an account and set up a database instance in the AWS U. S. East region. Go to the page for the database instance, and you will see the username and the database URLs.

Rails reads its database configuration from config/database.yml. Heroku overwrites config/database.yml with a file it generates from the DATABASE_URL configuration variable. The configuration variable uses the database driver name as the network scheme (e. g. mysql://). There is more than one mysql driver for Rails; the one suggested in the Rails Guide is mysql2.

To use mysql2 as the database driver with Xeround, use the following for the DATABASE_URL configuration variable:

heroku config:add DATABASE_URL='mysql2://{{username}}:{{password}}@{{hostname}}:{{port}}/{{dbname}}'

The username, hostname, and port number can be seen on the DB Instance Manager page on Xeround. The hostname and port are under one of the three DNS Names; I picked the top one. The password is what was entered for the database password during signup. The database name is the name of the database to be added using PHPMyAdmin.

The driver also needs to be included in the Gemfile. To include it just for production, use:

group :production do
  gem 'mysql2'
end

After adding this, run bundle install, commit your changes, and push to Heroku.

The database still needs to be created. To create the database, click on one of the links under DNS Names on the DB Instance Manager. This brings up PHPMyAdmin. Enter the database password and log in. Then create the database, with the same name used for the DATABASE_URL configuration variable.

Now that the database has been created, the DATABASE_URL is set, and the Gemfile is updated and pushed, you should be able to migrate the database by running heroku run rake db:migrate.

I’m using the latest version of Rails (3.1.1) on Heroku’s cedar stack.

It will be interesting to see how well Xeround works for this project. There are other MySQL hosting options for EC2 if I need them, and migrating is fairly simple.

7 thoughts on “Rails 3.1 and Xeround MySQL on Heroku”

  1. Thanks for the tip Ben,

    I’m trying to do the same with Rails 3.0.5 but having some problems and comments.
    Is the adapter mysql or mysql2? The example on Xeround about the setup site is mysql://username:password@host:port/database

    mysql2 is correct gem to use with Rails 3 (mysql 0.2.7 for 3.0.x and latest for 3.1) but the name in the url might be just a name they picked. I guess yours worked but still…

    I imagine the Xeround addon of Heroku is not needed right?
    heroku addons:add xeround –app xxxx says That add-on is only available to selected users

    And finally with mysql or mysql2 in the name of the db url when I try to migrate I just get:

    rake aborted!
    database configuration does not specify adapter

    Did ask Heroku but no answer yet…

    Cheers.

  2. Jose,

    The adapter is mysql2. It’s specified in two places: the Gemfile and the front part of the DATABASE_URL. In a non-heroku rails app the two places will be the Gemfile and config/database.yml. The mysql2 adapter is used in the Rails Guide and should be used over mysql unless there are issues getting it to work. The mysql gem is still supported.

    I saw the example URL on Xeround’s website and came up with my own, which uses an enhanced driver (mysql2). The Xeround one might work if you put mysql in your Gemfile instead of mysql2 but I wouldn’t recommend it.

    I understand the rationale behind both connection strings (short and sweet) and yml files (super easy to grok). I prefer yml files, myself. I searched for a script to convert between them and didn’t find one. I personally would like to see one. :)

    Thanks, and please let me know if you’ve got it working!

  3. I am looking into Xeround right now and can see some issues concerning the fact that alter table is not supported. Have you had to deal with that yet? If so, how did you overcome it?

    • I haven’t been using Xeround since a few days after that post, but I was curious so I googled and tried to figure out what I would do. Xeround does support renaming a table with the ALTER TABLE RENAME statement. What I’ve come up with so far is the following: A) create a new table with the desired schema and the name suffixed with _new, B) do a SELECT INTO from the old table to the new table C) rename the old table (add an _old) suffix, D) rename the new table to remove the suffix. After that the application would be accessing the new table.

  6. Okay, so the Xeround docs aren’t that great and this article helped out a ton after banging my head against the keyboard for an hour. Thank you so much!!!

Comments are closed.