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!
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.
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:
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.