Connecting Revolution R to MySQL on Windows

My colleague Saar Golde was having some troubles connecting Revolution R to MySQL on Windows (64-bit). Turned out the problem was the lack of an environment variable. He documented the instructions for fixing the problem on Windows 7, below. Thanks, Saar!

The Problem:

A client is about to send me a couple of large MySQL tables, so I needed to install a MySQL server and connect it to R so I can do some analysis on it.

The Process:

1. Install a MySQL server.

For some reason I could not get MySQL 5.1 to talk to R using the RMySQL package. Not sure what was wrong – maybe it’s the lack of an available MySQL 5.1 server for 64-bit windows. So I opted to use MySQL 5.0 (the exact version is 5.0.91-community-nt MySQL Community Edition).

2. Install the RMySQL package

Downloaded from Revolution’s repository, but CRAN should also work fine.

3. Load RMySQL

This is the tricky step:

> require(MySQL)

does not work right off the bat. You get an error like this one:

Error in utils::readRegistry("SOFTWARE\\MySQL AB", hive = "HLM", maxdepth = 2) :Registry key ‘SOFTWARE\MySQL AB’ not found

Error : .onLoad failed in ‘loadNamespace’ for ‘RMySQL’

4. Manually add MYSQL_HOME variable to the list of system variables

This is for Windows 7 –  it should not be very different for other versions. Right click on ‘my computer’, choose ‘properties’, click on ‘advanced system settings’, under the ‘advanced’ tab click on ‘Environment Variables…’. Create a new system variable (not a user variable!) named ‘MYSQL_HOME’ and enter the MySQL directory address there. In my case it is ‘C:/Program Files/MySQL/MySQL Server 5.0’ (notice the slash instead of the standard Microsoft backslash).

5. Restart R

If there is an alternative way for R to recognize the change in the system variable, that may be preferable. In any case, restarting works. require(RMySQL) works now.

6. Go have a beer. Repeat if necessary.