|von Gagern » Martin » HOWTOs »||RMySQL|
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:
I used the binary distribution for Windows. Obviously you'll have to enable the 64bit components in the installer. The default install location is C:\Program Files\R\R-2.13.1.
We'll need this to compile RMySQL from source. I let it install to the default location, C:\Rtools. I didn't let the installer modify my system PATH environment variable, as I have Cygwin installed, and wanted to avoid any conflict in my daily work.
As RMySQL has to be compiled against the same version of the MySQL DLL it will use at run time, we'll have to compile this from source. So download the source tarball and use it as described below.
MySQL Community Server 5.5.15
It is important to use a 64bit version of MySQL, as we will link against its libmysql.dll. I used the MSI installer, but it should be possible to use the ZIP archive instead. It should be enough to have the Connector/C package, but I haven't tried that.
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).
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.
By setting the MYSQL_HOME environment variable.
Using regedit you can simply copy the whole registry subtree to the version not including Wow6432Node.
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.
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.
Simply copy all the files contained in lib into a subdirectory called opt.
Applying libDir.patch to the RMySQL sources, you can have RMySQL use the new directory name instead.
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%
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.