Site icon R-bloggers

RMySQL: using the latest MySQL version

[This article was first published on [R]appster, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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

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!


To leave a comment for the author, please follow the link and comment on their blog: [R]appster.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.