//
you're reading...
MySQL, Portable Apps, R, xampp

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 using xampp. And I’m quite happy about this very last switch to xampp!

Why I like xampp

  • it’s open-source and actively maintained (thanks to the guys over at ApacheFriends.org!)
  • 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. It assumes you have a directory called Apps directly below your USB drive (<DRIVE>)

  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>\Apps\Rtools\2.14\bin
    <DRIVE>\Apps\Rtools\2.14\MinGW\bin
    <DRIVE>\Apps\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:

  4. 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.
  5. 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.
  6. 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!

About these ads

Discussion

2 thoughts on “RMySQL: using the latest MySQL version

  1. Hello. I get as earlier:

    Error: package ‘RMySQL’ is not installed for ‘arch=i386′

    On 64-bit Windows 7
    :(

    Sad situation to those who have no experience with linux…

    Posted by Kk | December 16, 2011, 3:26 pm
    • I can relate to your frustration ;-)

      That’s strange, it worked for my Windows 7 64 Bit.

      Troubleshooting
      1) Did you pick up the ‘libmysqld’ files instead of the ‘libmysql’ files?
      2) Does the MinGW compiler run at all? If not, then something might have gone wrong with the installation of Rtools? Or adding the paths to the Windows PATH? Be sure that you put the Rtools path to the very beginning of you PATH variable. It’d be helpful if you could post your console output when trying to install ‘RMySQL’.
      3) Did you put the ‘libmysql.dll’ in the right directory (32 Bit vs. 64 Bit R)?

      Posted by songpants | December 16, 2011, 6:52 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: