adding RMySQL package to R fails (on Windows)?

Question

I can't figure out why my RMySQL package won't install - here's what I get:

> install.packages('RMySQL',type='source')
trying URL 'http://cran.mirrors.hoobly.com/src/contrib/RMySQL_0.7-5.tar.gz'
Content type 'application/x-gzip' length 160769 bytes (157 Kb)
opened URL
downloaded 157 Kb

* installing *source* package 'RMySQL' ...
ERROR: configuration failed for package 'RMySQL'
* removing 'C:/PROGRA~1/R/R-212~1.0/library/RMySQL'

The downloaded packages are in '(foo)'

Warning message:
In install.packages("RMySQL", type = "source") : installation of package 'RMySQL' had    non-zero exit status

I don't think it's a problem with the MySQL install, as the right values seem to be in the registry:

> Sys.getenv('MYSQL_HOME')
                 MYSQL_HOME "C:/PROGRA~1/MySQL/MYSQLS~1.1/" 
> readRegistry("SOFTWARE\\MySQL AB", hive="HLM", maxdepth=2)
$`MySQL Server 5.1`
$`MySQL Server 5.1`$DataLocation
[1] "C:\\Documents and Settings\\All Users\\Application Data\\MySQL\\MySQL Server 5.1\\"

$`MySQL Server 5.1`$FoundExistingDataDir 
[1] "0"

$`MySQL Server 5.1`$Location 
[1] "C:\\Program Files\\MySQL\\MySQL Server 5.1\\"

$`MySQL Server 5.1`$Version
[1] "5.1.54"

Puzzled. Any help would be greatly appreciated!

Stage

No experience with this package, but have you tried the Windows binary? I.E., just install.packages("RMySQL"). I don't know if this package contains C/C++/FORTRAN, but if does, then you'd have to have the compilers set up in R. I found this at ?install.packages: Using packages with type = "source" works on Windows provided the package contains no C/C++/Fortran code that needs compilation, Otherwise you will need to have installed the Rtools collection as described in the ‘R for Windows FAQ’ and you must have the PATH set up as required by Rtools. – Weathering

@richardh There is no Windows binary version of this package. – Himation

Answer 1

The basic process is described here, but there are several hints, So I will describe the whole solution (please change the R version and paths if needed):

1. Install latest RTools from here
2. install MySQL or header and library files of mysql
3. create or edit file C:\Program Files\R\R-2.12.1\etc\Renviron.site and add line like MYSQL_HOME=C:/mysql (path to your mysql files)
4. copy libmysql.lib from mysql/lib to mysql/lib/opt to meet dependencies.
5. copy libmysql.dll to C:\Program Files\R\R-2.12.1\bin or to windows/system32 directory.
6. run install.packages('RMySQL',type='source') and wait while compilation will end.

This worked for me on Windows 7 64 bit, so there should be no problems with 32 bit versions

Initialy posted here (similar question).

Fibre

success! I think the key was step 5 - copying libmysql.dll into the R folder. Had not seen that suggested anywhere else. – Stage

that were the missing steps – Subsidiary

re: #3, has this file changed to Rcmd_environ in R 2.15.2? – Stage

Yeah, the installation succeeded. Now how do use MySQL from R command prompt? – Aqua

I had the same issues and after doing all the above I needed the final step of copying the 'libmysql.dll' file to the MySQL directory: C:/Program Files/MySQL/MySQL Server 5.6/bin. I'm running Windows Server 8, R-3.0.2, MySQL Server 5.6 I don't know why this was required but I found this step on another page and when I did it it worked. – Palingenesis

Installation of the MySQL client was necessary for me – Jinn

I could not get this to work installing to C:\Program Files\R\ - needed to put it in C:\R - the spaces were causing an error – Radiophone

Answer 2

First I would try following the directions on this page: http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL

Then, ensure that you actually have a MySQL client on your computer, not just the server itself. I've had a problem myself where I had to use an older binary for the client, but you can download these from mysql website.

Zita

Answer 3

I created a binary which might work for people:

http://kenahoo.blogspot.com/2011/09/rmysql-binary-for-windows-7.html

I basically followed all these directions, including the ones at the Vanderbilt page, but for one reason or another it doesn't always seem to work. Anyway, hope this might be helpful for people who have the same versions of stuff I used.

Selfanalysis

Thanks, it was compiled with MySQL 5.5.15 but it seems to work great with MySQL 5.6.12 as well! – Notice

Answer 4

I was having this same problem on Windows 7 with R 2.12.1 x64 and resolved it by:

1. Changing the R directory in my PATH variable to C:\Program Files\R\R-2.12.1\bin\x64 (rather than just ...\R-2.12.1\bin).
2. Copying ...\MySQL Server 5.5\lib\libmysql.dll to ...\MySQL Server 5.5\bin\.
3. Running R CMD INSTALL RMySQL_0.7-5.tar.gz in cmd.exe (install.packages('RMySQL',type='source') in R still would not work).

Son

Do you use 64bit mysql or 32bit? – Banquet

Answer 5

I came across this step-by-step guide to install RMySQL under Windows7 64bit.

http://www.ahschulz.de/2013/07/23/installing-rmysql-under-windows/

The software I used are:

1. Windows 8 OS
2. R3.0.2 - 64bit
3. Rtools 3.1
4. MySQL Server Community Edition 5.6 - 64bit

Following the above guide (with screenshots provided), my installation and loading of the RMySQL package works.

Many thanks to Arne Hendrik Schulz!

Donetsk

Answer 6

As Marek said. There is no windows binary for this package. I had similar troubles on my Mac when I tried to install RPostgreSQL (because there's no binary for Mac) in the end I had to compile it on my own and modify several files.

For the MySQL part(if you don't know much about MySQL), you might want to use WAMP which is admittedly more than you need, but easy to install and to start and shut down.

For some compiling help you might be interested in Rtools

HTH

BTW: What kind of windows do you run (sry, if I missed it)

Coleencolella

Answer 7

You however there is one additional requirement at present (with the MySQL server version 5.5 & R2.12.1) follow thehttp://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL instructions at present as the locations of the libmySQL.dll differs to what is expected in the package.

i.e. for the 64bit compiling. it expects the libmySQL.dll to be in "${MYSQL_HOME}"/bin/libmySQL.dll so simply copy the dll over to the /bin dir and it will now compile.

for the 32bit compilling. the libmySQL.lib is expected in "${MYSQL_HOME}"/lib/opt/libmysql.lib again moving this should solve the problem.

Alternatively, if you don't want to or can't move these files. Then you can edit the source code files: Makevars.win Makevars.win32 Makevars.win64 appropriately pointing to /lib/libmysql.dll /lib/libmysql.lib

Ferullo

Answer 8

Add MySQL and RTools directory to the PATH!

Here is my PATH ; ~blah~;C:\Program Files\MySQL\MySQL Server 5.5\bin;c:\rtools\bin;C:\Rtools\gcc-4.6.3\bin;

Illyrian

Answer 9

I was working for last 2 days on Installation of R with RMySQL package, finally got the solution for that, here are the steps to install RMySQL package:-

1. DOWNLOAD SOFTWARE FROM THE FOLLOWING LINKS: *a. R2.13.2: Download R from http://cran.stat.sfu.ca/index.html

     b. RTools 214:  Download RTools from http://cran.cict.fr
   
     c. RMySQL 0.8-0.tar.gz: Download RMySQL from 
     http://biostat.mc.vanderbilt.edu/wiki/main/RMySQL/RMySQL_0.8-0.tar.gz
   
     d. MySQL Server 5.0: download it from http://dev.mysql.com
   
     e. RSTUDIO (optional): download it from http://rstudio.org*
   

2. SET THE FOLLOWING ENVIRONMENT VARIABLES

    * a. MYSQL_HOME : <drive>/path to MySQL installation folder
         e.g. MYSQL_HOME= C:\Program Files\MySQL\MySQL Server 5.5\ 
   
     b. R_HOME: <drive>/path to R installation
        e.g. R_HOME=C:\Program Files\R\R-2.13.2\
   
     c. PATH: Modify path to accommodate the above variables. *
   

   Be sure that the following paths areincluded in your Windows PATH variable: \Rtools\2.14\bin \Rtools\2.14\MinGW\bin \Rtools\2.14\MinGW64\bin

3. CREATE FOLDER AND COPY FILES

   a. OPT: Create a folder OPT under C:\Program Files\MySQL\MySQL Server 5.5\lib and copy
   MYSQLLIB.LIB the above path. Also copy libmysql.dll to \\R\R-2.14.0\bin\ (64 bit) Or \\R\R-2.14.0\bin\i386\ (32 bit) and to C:\Windows\System32.

b. Renviron.site: create or edit a file \\R\R-2.14.0\etc\Renviron.site and add a line: MYSQL_HOME =”C:/Program Files/MySQL/MySQL Server 5.5/” NB: USE FORWARD SLASH AND DOUBLE QUOTES HERE

c. libMySQL.dll: Copy this file to C:\Program Files\R\R-2.13.2\bin\i386 as well as C:\Program Files\R\R-2.13.2\bin

1. RUN COMMANDS

   a. Install.Packages: Run R GUI by clicking on the R icon on desktop or from Start menu. Type INSTALL.PACKAGES(“RMySQL”,type=”Sources”). This will download the required software from repositories.

b. Command Prompt: Copy the downloaded zip file (in step 4.a.) and paste it under R installation folder. Go to start menu and open Command Prompt. Go to the R installation folder and type R CMD INSTALL RMySQL_0.8-0.tar.gz

  *COMMANDS:
  >library(RMySQL)
   >drv = dbDriver("MySQL")
   >con = dbConnect(drv,host="localhost",dbname="test",user="root",pass="root")
   >album = dbGetQuery(con,statement="select * from t_master")
   >album*

Suitable

Answer 10

18 months later, new laptop, same problem. Returning to the scene of the crime - sorry January 2011 self; we're in the future, and this installation still isn't any fun.

Lessons learned:

-Updated RTools are essential. Get them.

-Are you seeing an error along the lines of .onLoad failed in loadNamespace() for 'RMySQL', details? This seems to mean that there's a problem with your system environment variables.

In R, run

Sys.getenv('MYSQL_HOME')

If it spits back a path like

 [1] "C:\\Program Files\\MySQL\\MySQL Server 5.6"

you're good. But if it gives you empty string, you need to set this.

These are all Windows 7 directions, but the process is pretty similar in XP, if memory serves.

1) Bring up System Properties > Advanced > Environment Variables.

2) In the lower 'System Variables' pane, click 'New'.

3) Variable name is MYSQL_HOME and value is whatever the path is to your MySQL installation - mine was C:\Program Files\MySQL\MySQL Server 5.6

Newer versions of MySQL don't seem to create an environment variable on install - that and the lack of binaries for RMySQL explain why this questions has garnered 8,000 hits.

-Still stuck? Here are two additional in-depth writeups: 1, 2

Stage

I'm still having this issue. I have R version x64 3.0.1 and RStudio version 0.97.551 the latest version of Rtools version 3.1.0.1936. My Sys.getenv('MYSQL_HOME') spits back the same value as you - which is correct for me too. However, I get the original error still.... any thoughts? – Triangular

I resolved my problem - it was because RTools was nice installed properly. – Triangular

Answer 11

Following worked for me in red hat linux

wget http://cran.r-project.org/src/contrib/RMySQL_0.9-3.tar.gz
sudo R CMD INSTALL  --configure-args='--with-mysql-inc=/usr/include/mysql' --configure-args='--with-mysql-lib=/usr/lib64/mysql' RMySQL_0.9-3.tar.gz

Ferrer

another option in case of debian: sudo apt-get install libdbd-mysql libmysqlclient-dev – Bienne