SOLVED: Rails with MySQL on Windows

For the last couple of days, I was struggling to get Rails to talk to MySQL on Windows. Though I was able to install the "mysql" gem, I got an error message when I tried to run the "rake db:create" command for creating the database tables needed for my model. Web searches revealed many detailed discussions on the problem, but no definite solution. Whatever approach I tried only resulted in a different error message. Finally, against the run of play (as cricket commentators would say), I was able to get things working. So here are the steps I followed, for the benefit of others who may be facing the same issue:

1. Install Ruby and Rails using the RailsInstaller - I used version 1.3, which provides Ruby 1.8.7 and Rails 3.0.7, along with other related packages like Bundler and DevKit

2. Install MySQL Community Server - I already had version 5.5.23

3. Install the mysql gem:

gem install mysql

4. Carefully read the message from the mysql gem installer - I got:

You've installed the binary version of mysql2. It was built using MySQL Connector/C version 6.0.2. It's recommended to use the exact same version to avoid potential issues.

At the time of building this gem, the necessary DLL files where available in the following download:

http://dev.mysql.com/get/Downloads/Connector-C/mysql-connector-c-noinstall-6.0.2-win32.zip/from/pick

And put lib\libmysql.dll file in your Ruby bin directory, for example C:\Ruby\bin

5. Download the exact same version of the MySQL C connector as mentioned in the message you see. Get the zip archive, not the MSI installer. Simply extract the "lib\libmysql.dll" file and put it in your "Ruby\bin" directory (you don't really need the rest of the C connector files).

6. Add a new line "gem mysql" to the Gem file of your Rails project.

7. Replace the (sqlite) contents of your config\database.yml file with:

development:

  adapter: mysql

  encoding: utf8

  database: blog_development

  pool: 5

  username: root

  password:

  socket: /tmp/mysql.sock

Now, when you run the "rake db:create" command, it should work without any issues. Even before that, you can test the MySQL connectivity using irb (the Interactive Ruby shell):

C:\Documents and Settings\User>irb

irb(main):001:0> require "Rubygems"

=> true

irb(main):002:0> require "mysql"

=> true

irb(main):003:0> Mysql.connect("localhost", "root", "", "phpwork")

=> #<Mysql:0x2cdfca8>

irb(main):004:0>