Installing 64bit RMySQL under Windows

Recently I've spent several hours trying to create a 64 bit build of RMySQL, the MySQL driver for the R statistical computing environment. To help others, as well as for my own future reference, I'm describing the key points here. I've used what was the lastest release of the respective packages at the moment:

Finding MySQL

Most problems I encountered with RMySQL revolve around the location of various MySQL components. There are several aspects to this.

The library as well as some header files have to be available at build time, but the DLL must be located at runtime as well. The compile-time settings are determined by the files configure.win, inst\FindMySQLhome.R and src\Makevars.win64 inside the RMySQL source package. The runtime location is computed in R\zzz.R.

The required paths consist of two parts. The first is the location of the whole MySQL installation (e.g. C:\Program Files\MySQL\MySQL Server 5.5.15), while the second denotes a subdirectory inside that location which contains the required files (e.g. lib).

Finding the top level MySQL directory

In order to locate the directory where all of MySQL is installed to, RMySQL will first examine the environment variable MYSQL_HOME. If that isn't set, then the Location entry of the HKEY_LOCAL_MACHINE\Software\MySQL AB registry key is used instead. At build time, the registry is only used to suggest values for the MYSQL_HOME variable, which has to be set.

Due to MySQL Bug #42423, the installer of the 64bit MySQL distribution will create the wrong registry key. As the installer itself is a 32bit application, it's run using WoW64 and because it doesn't explicitely request a 64bit view of the registry (using the KEY_WOW64_64KEY flag) it creates the registry key HKEY_LOCAL_MACHINE\Wow6432Node\Software\MySQL AB instead.

Possible solutions:

Set MYSQL_HOME

By setting the MYSQL_HOME environment variable.

Copy the registry key

Using regedit you can simply copy the whole registry subtree to the version not including Wow6432Node.

Patch RMySQL

Applying Wow6432Node.patch to the RMySQL sources, you can have RMySQL check the 32bit registry key instead of the 64bit one both during installation and at runtime.

Choosing the correct subdirectory

Within this directory, there are several subdirectories. One called lib contains the libmysql.dll which will be required at run time. It also has the libmysql.lib file which the linker will use at compile time. The include directory contains the header files the compiler will use.

Apparently other MySQL versions used different layouts, namely a directory called lib\opt to contain what's now directly in lib. So you have to adjust for these differences.

Possible solutions:

Copy the directory

Simply copy all the files contained in lib into a subdirectory called opt.

Patch RMySQL

Applying libDir.patch to the RMySQL sources, you can have RMySQL use the new directory name instead.

Installing RMySQL

If you are ready to install RMySQL, you can do so by executing these comands:

SET MYSQL_HOME=C:\Program Files\MySQL\Server
PATH C:\Rtools\bin\x86;C:\Rtools\MinGW64\x86_64-w64-mingw32\bin;C:\Rtools\MinGW64\bin;C:\Rtools\MinGW64\libexec\gcc\x86_64-w64-mingw32\4.5.2;C:\Program Files\R\R-2.13.1\bin;%PATH%
SET CFLAGS=-I/cygdrive/c/Rtools/MinGW64/x86_64-w64-mingw32/include
R.exe CMD INSTALL --no-multiarch RMySQL_0.8-0.tar.gz

The MYSQL_HOME variable only needs to be set if you haven't done so globally for your whole system or account.