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.

Comments

[John Ramey]

The "if necessary" phrase keeps evaluating as true, so I'm stuck in an infinite loop in step 6. But it's okay because it's beer.

[Jeff]

I just spent all night testing different versions of R, libmySQL.dll, and RMySQL.

My evening of compiling different versions of things from source was unsuccessful until I stumbled across this.

My setup is:
Windows 7 x64
R 2.10
RMySQL-7.4 (from CRAN)

The problem I was having was that I'm using a 64-bit OS with a 32-bit RGUI. I didn't actually need a full MySQL server installed (I'm using a remote MySQL database), but the only way I could find a compatible library (libmySQL.dll) was by installing MySQL server 5.0.91.

I tried the 64 bit version of MySQL first with no success (error message about not being a valid 32-bit library). But I just tried the 32-bit MySQL 5.0.91, copied the libmySQL.dll from that installation and it works like a charm!

Thanks for the pointers!

[yuanwei]

I have been puzzled by the problems when I try to connect R with MySQL through RMySQL.
It's certain that I have already an environment variable - MYSQL_HOME, but I just can't get the connection work after restarting R...
when I load RMySQL the error occur：
Error : loadNamespace()...
If you know how to solve this tough problem I really appreciate it if you can send an email to me!

[Eric Laszlo]

The RODBC package will also work in connecting to MySQL on 64-bit Windows. You just have to download the ODBC driver from the MySQL website.

You can avoid the "data source name" hassle of going through ODBC by using the odbcDriverConnect() function found in the RODBC package rather than using the odbcConnect() function. The connection string to use for MySQL (if found on your machine) is

Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=dbname;User=uname;Password=pswrd;Option=3;

[Cícero C Nunes]

Excellent!
My RMySQL is working now.
Thanks for it!

[Jason B]

Thanks Yuanwei

RODBC solves the problem of using MySQL with Rev R 4.0 on x64. (Namely that an x64 RMySQL binary isn't available for R 2.11, and it sounds like there may never be one.)

[Steve Sidney]

I have just found this post and wonder if you can help.

I would like to read MySQL database tables into R where MySQL is hosted remotely.

In the above post by Eric on July 29 can I substitute the URL localhost in Server=localhost to able to connect to the db.

Also is RODBC available on CRAN.

Any help would be appreciated

[raghu]

Hi
i have a database called apple in mysql
and table name applecost
below is the table name applecost
| quantity | price | totprice |
+----------+-------+----------+
| 40 | 90 | NULL |
| 50 | 60 | NULL |
| 60 | 30 | NULL |
+----------+-------+----------+
I am calculating totprice by multiplying quantity and price in R.
i got the values 3600 3000 1800=price5
now i want to send values 3600 3000 1800 from R to the column totprice.
i have written the update query in R as
query.str1 = 'update applecost1 set totprice=price5[1,1] where quantity=40'
i got error as in R
"42000 1064 [MySQL][ODBC 3.51 Driver][mysqld-5.1.48-community]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[1,1] where quantity=40' at line 1"
[2] "[RODBC] ERROR: Could not SQLExecDirect 'update applecost1 set totprice=price5[1,1] where quantity=40'" .
how to fix this error? I need help on this to solve this problem....can anyone help me

[Andrew]

I have had nothing but problems trying to get RmySql to install on several machines. I always get the same error. "In install.packages("RMySQL", type = "source") :
installation of package 'RMySQL' had non-zero exit status"