RMySQL: using the latest MySQL version

In order to connect my R related stuff to a webserver and MySQL I went all the way from using xampp to setting up my own (W)AMPP  (Apache MySQL PHP Perl) to finally back to xampp. And I’m quite happy about this very last switch to xampp!

Why I like xampp

• it can be run as a portable app from an USB device
• it’s hassle free when you want to use an AMPP bundle but don’t like to spend your time configuring the whole thing

Why I parted from xampp

The reason I parted from xampp in the first place was the fact that I thought that the latest RMySQL version that is up on CRAN only seems to like MySQL version 5.0.67 (dating back to 2008-08-04). This forced my to use a way outdated version of xampp (version 1.6.8).

So at some point I decided to set up my own (W)AMPP bundle. It’s not that hard, but you do need to spend some time aligning config files. For those interested, I’ve written a little manual on how to do this on Windows. When you read it, please have in mind that I just scratched it down for my own purpose. It’s not really polished

Why I switched back to xampp

Yesterday I found out that RMySQL indeed connects to any MySQL version if you’re fine with some initial copy and pasting of MySQL files. Unfortunately, this info is not readily available (neither on CRAN nor under the provided URL), at least not for Windows. I actually found it on Stack Overflow (thanks to Yuri) which is becoming one of my favorite resources for programming related information. I’ve adapted it a little to fit my needs.

How to use RMySQL with an arbitrary version of MySQL

Here’s the walk through for a fully portable installation:

1. Get the latest portable xampp (here) and put it on your USB drive (mine lives here: <DRIVE>\Apps\xampp\1.7.7)
2. Get the latest RTools (here) and install it to your USB drive (mine lives like: <DRIVE>\Apps\Rtools\2.14). Be sure that the following paths are included in your Windows PATH variable:
   <DRIVE>\Rtools\2.14\bin
   <DRIVE>\Rtools\2.14\MinGW\bin
   <DRIVE>\Rtools\2.14\MinGW64\bin
3. Set MYSQL_HOME
   There are some alternatives here:
   a) You create or edite file <DRIVE>\Apps\R\R-2.14.0\etc\Renviron.site and add a line: MYSQL_HOME=<DRIVE>\Apps\xampp\1.7.7\mysql
   b) In case you use Eclipse (which I still favor over RStudio exactly because of features like this) you can set an environment variable directly in your R Run Configuration:
   
   

1. copy
   <DRIVE>\Apps\xampp\1.7.7\mysql\lib\libmysql.lib
   to
   <DRIVE>\Apps\xampp\1.7.7\mysql\lib\opt\
   to meet dependencies of RMySQL.
2. copy
   <DRIVE>\Apps\xampp\1.7.7\mysql\lib\libmysql.dll
   to
   <DRIVE>\Apps\R\R-2.14.0\bin\ (64 bit)
   or to
   <DRIVE>\Apps\R\R-2.14.0\bin\i386\ (32 bit)
   or to
   C:\Windows\System32.
3. run install.packages()
   

   install.packages("RMySQL", type="source", lib="<DRIVE>\Apps\R\R-2.14.0\library", destdir="<DRIVE>\Apps\R\R-2.14.0\destdir")

If the last step finishes without any errors, then you’re good to go. Otherwise probably something just went wrong with file paths.

Greetz!