MySQL and Rails 3 on a PC

Rails 3.0 is the new big thing, and deservedly so. But getting MySQL working with Rails 3 on a PC can be problematic. Here's some easy steps to make your life a whole lot easier if you're doing Rails and MySQL development on a PC (although most of the instructions, except for the obvious PC-isms, can apply to Mac or Linux systems, too).

First, let's go over some background technical issues.

Rails 3 works with both Ruby 1.8.7 and Ruby 1.9.2. When it comes to the PC environment, working with Ruby 1.8.7 is still a little more reliable than 1.9.2, so our installation procedure will focus on Ruby 1.8.7.

Traditionally, Rails applications have used the mysql gem to work with MySQL databases. However, this gem hasn't been updated since 2009, and the ActiveRecord component of Rails, which is responsible for object-relational database mapping, has changed dramatically. Accordingly, the new mysql2 gem is recommended instead.

The mysql2 gem requires some native extensions, so you'll need the DevKit or the gem can't be built properly.

I've found the easiest way to get all of the components that I want on my PC, e.g. — Ruby, DevKit, etc., is to download RailsInstaller.

So, let's get started:

  1. Install RailsInstaller: The RailsInstaller package comes with Ruby 1.8.7, Rails 3.0.7, DevKit, Git, bash and a few other niceties.

  2. Test Your PATH Environment Variable: RailsInstaller should set up your PATH properly. To test this, go to the Start menu, under Programs -> RailsInstaller, where you'll see entries for "Command Prompt with Ruby and Rails" and "Git Bash". The first gives you a standard PC command window (but with C:\RailsInstaller\Ruby1.8.7\setup_environment.bat automatically called to set up the environment) and the second gives you a UNIX-like window and a bunch of UNIX commands (compliments of DevKit).

    To view the PATH in the command window:

       > echo %PATH%

    To view the PATH using bash:

       $ echo $PATH

    Check that both C:\RailsInstaller\DevKit\bin and C:\RailsInstaller\Ruby1.8.7\bin appear in the PATH. The PATH should be correct for the command window; if not, edit C:\RailsInstaller\Ruby1.8.7\setup_environment.bat as needed.

    If the PATH is not correct for the bash window, update the PATH environment using the Control Panel (preferred) or by creating a separate .bashrc file.

    To do this, bring up the Control Panel, click the System icon and then select the Advanced tab. Click the Environment Variables button, then edit the PATH in the "System variables" section to include the relevant directories.

    This requires the "Local Admin" privilege on your PC. If organization policy precludes this level of access, which is common with government agencies and government contractors, then set the path in C:\Documents and Settings\<username>\.bashrc. This file will conform to standard bash shell conventions.

  3. Install MySQL: Install the Community Edition of MySQL (the free one), which is currently at version 5.5. Download and install it from the MySQL site. Accept the various defaults, and make sure you agree to set it up as a service (so you don't have to worry about starting/stopping it manually). During the install, you'll be asked to provide a password to go with the "root" account — remember your password, please.

    Upon completion of the installation, ensure that MySQL's bin directory is added to the PATH (so you can easily use tools like the mysql client program).

  4. MySQL Workbench: Strictly speaking, this isn't required, but if you're going to download MySQL you might as well also install the free tools that go with it. MySQL Workbench provides features that allow you to run queries against the database, design your database tables and manage your server instance (including user accounts).

  5. Create Database: Create a schema for your Rails application, along with a user account. I typically create users with MySQL Workbench, but I create schemas using the mysql client (since MySQL Workbench is terrible in this area).

  6. Install the mysql2 Gem: You'll need to do the install manually, rather than via bundle, since there are some key parameters that need to be provided. Also, you'll want to do the install from bash, so the various UINX-ish utilities are available to the build process.

    Also, a note on versions. If you're using Ruby 1.8.7, you should be using version 0.2.7 of the gem. Those using Ruby 1.9.2 (at your own peril), should be using the latest version of the gem.

    To do the install:

       $ gem install mysql2 -v=0.2.7 --
         '--with-mysql-lib="c:\Program Files\MySQL\MySQL Server 5.5\lib"
         --with-mysql-include="c:\Program Files\MySQL\MySQL Server 5.5\include"'

    Note the careful use of quotes.

  7. Setup Your Rails App: Make sure your Rails app is properly configured to use the mysql2 gem. List the mysql2 gem in the application's Gemfile with version 0.2.7. Also, in the database.yml file, the adapter should be "mysql2" and not "mysql".

  8. Start the Rails Server: Start up your Rails application. Everything should be fine at this point. However, if the application is complaining about not finding "libmysql.dll" then go to the next step (otherwise skip it).

  9. Workaround: If your application complained about not being able to find "libmysql.dll" then you'll need to do this workaround. Copy "libmysql.dll" from the lib directory of your MySQL installation into C:\RailsInstaller\Ruby1.8.7\bin. Then start your Rails app again and everything should be fine.

Well, the steps aren't as painless as I'd like, but they should get you up and running with Ruby 1.8.7, Rails 3 and MySQL. Good luck!



Comments

David Keener By dkeener on Monday, June 06, 2011 at 08:43 PM EST

Just an FYI. I found this discussion trail on Stack Overflow which covers why the mysql2 gem is preferred for Rails 3.0 over the aging mysql gem.


David Keener By dkeener on Saturday, June 11, 2011 at 01:35 PM EST

Another thought. With the potential difficulties involved in getting MySQL working in different environments, I can see why the Rails core team moved to sqlite for the default database. This move ensures that every developer, including newbies, will have a working development environment as soon as they get Rails 3.x successfully installed.


David Keener By dkeener on Tuesday, July 10, 2012 at 05:04 PM EST

These instructions need to be updated for the latest version of RailsInstaller, which now supports Rails 3.2 (and needs the latest version of MySQL).


Leave a Comment

Comments are moderated and will not appear on the site until reviewed.

(not displayed)