Installation and Configuration of MySQL for Rails Applications - Issues and Solutions

We chose MySQL as the database management system used by our original Virtual Realty Listing Services application in the first edition of this book. At the time, it was the most popular lightweight DBMS that provided the functionality associated with a "real" relational database. We chose it again for the revised version of the application used in the second edition of the book, and for the administrative interface application written in Rails for the second edition. It made sense to continue to use the same DBMS, and it made sense to build and configure the administrative interface so that it could administer the same database used by the main application.

Other lightweight DBMS's have arisen since the first edition of the book was published, including SQLite, a "self-contained, serverless, zero-configuration, transactional SQL database engine." SQLite is used for the internal database in the iPhone, and it is now the default DBMS used for Rails applications (having usurped that position from MySQL).

With the advent of newer lightweight DBMS's, and with the purchase of Sun (which owns MySQL) by Oracle, there has been an increase in the number of support issues associated with using MySQL, especially with Rails (which is a rapidly evolving framework that frequently sacrifices backward compatibility for enhanced functionality).

In particular, the MySQL database adapter is no longer packaged with the Rails distribution. This would not be a problem if installing MySQL support for Ruby and Rails were simply a matter of invoking Ruby's package management service, RubyGems.

But it isn't.

Windows

For Windows environments, running gem install mysql --no-ri --no-rdoc from the command line will fetch Ruby's MySQL "gem" from the online repository and install it. But starting with Rails 2.2, this was not enough, especially if you had installed a version of MySQL newer than 5.0. A copy of the file needs to be dropped into the C:\ruby\bin directory—but not the 5.1 (or greater?) version of that file! The 5.1 MySQL client code does not work properly with Ruby, so what's needed is the 5.0 version of libmySQL.ddl.

Fortunately, this individual file can be obtained without going through the conniptions of downloading an entire 5.0 distribution: it can be downloaded from the InstantRails web site.

If you are already running version 5.0 of MySQL, or if you can obtain version 5.0 from the web, you may not need to go through the second step described above (dropping a copy of libmySQL.ddl into the C:\ruby\bin directory). However, version 5.1 is now the default, and newer versions are already on the horizon, meaning that support for 5.0 is likely to dissipate over time. Hopefully, version 5.4 (not yet in beta for Windows) will remedy this problem, but keep these notes handy if problems arise with future releases of MySQL.

Mac OS X

Problems also arise on the Macintosh. Under Mac OS X Leopard, you must build the native MySQL driver code yourself. (Why this is the case is not explained on the Rails wiki or in any of the Rails support forums.) To accomplish this, you enter the following lines at the command prompt in the Terminal application:

$ export MYSQL_HOME="/usr/local/mysql"
$ sudo env ARCHFLAGS="-arch i386" gem install mysql -- \
--with-mysql-dir=$MYSQL_HOME --with-mysql-lib=$MYSQL_HOME/lib \
--with-mysql-include=$MYSQL_HOME/include
If /usr/local/mysql is not the name of the directory into which you installed MySQL, substitute the correct directory name on the line that establishes the MYSQL_HOME variable. (Replacing ARCHFLAGS="-arch i386" with ARCHFLAGS="-arch ppc" should work for older PowerPC Macintoshes, but this has not been confirmed.)

Summary

Information on installing MySQL (and the required Ruby gems to support it) on a variety of platforms can be found at http://wiki.rubyonrails.org/database-support/mysql#installation. The information supplied here is accurate as of its date of publication, but the MySQL and Rails web sites should be considered "authoritative" sources.

Downloads