Friday, December 11, 2009

Integrating MySQL with R, the statistical package

For the better part this morning I have been trying to get MySQL working with R. I was able to download and install RMySQL but getting it to load was a nightmare. Every time I ran library(RMySQL) it always gave me the error:
Loading required package: DBI
Error in if (utils::file_test("-d", MySQLhome)) break :
argument is of length zero
Error : .onLoad failed in 'loadNamespace' for 'RMySQL'
Error: package/namespace load failed for 'RMySQL'

I am running R on a WinXP SP2, MySQL v5.0.51 and R v2.10. The problem that I came to figure out was that R was trying to connect to the wrong version of MySQL. Typing
readRegistry("SOFTWARE\\MySQL AB", hive="HLM", maxdepth=2)
to see the versions of MySQL's in the registry gave:
QL Connector/ODBC 3.51`
$`MySQL Connector/ODBC 3.51`$Version
[1] "3.51.27"

$`MySQL Server 5.0`
$`MySQL Server 5.0`$Location
[1] "C:\\Program Files\\MySQL\\MySQL Server 5.0\\"

$`MySQL Server 5.0`$Version
[1] "5.0.51a"

This basically means that there are 2 instances of MySQL in the registry, ie MySQL v3.51 and v.5.0.51. Gotcha, this is the problem:

When connecting to MySQL R usually uses the first instance of MySQL found in the registry. In my case it was trying to connect to MySQL v3.51.

The problem is solved by removing this instance from the registry.
Go to

Type in regedit
Navigate to HKLM(HKEY_LOCAL_MACHINE)->Software->MySQL Server 5.0.
Click on the folder with the unwanted MySQL and delete it.

Now try again to load RMySQL and BINGO you are done, or so I hope!!!

No comments:

Post a Comment