Connecting Revolution R to MySQL on Windows

June 4, 2010
By

(This article was first published on Revolutions, and kindly contributed to R-bloggers)

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.

To leave a comment for the author, please follow the link and comment on his blog: Revolutions.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Tags: , ,

Comments are closed.