How to Connect R with MySQL or how to install RMySQL package?

Question

I am new in R and i am trying to connect R with MySQL. I have installed mysql-5.5.22-winx64 and R-2.12.0 for 64 bit. I have already set MYSQL_HOME environment path(C:\Program Files\MySQL\MySQL Server 5.5) and trying to follow these steps:

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. but still i am getting this error:-

   *> install.packages('RMySQL',type='source')
    --- Please select a CRAN mirror for use in this session ---
    trying URL 'http://ftp.iitm.ac.in/cran/src/contrib/RMySQL_0.9-3.tar.gz'
    Content type 'application/x-gzip' length 165363 bytes (161 Kb)
    opened URL
    downloaded 161 Kb
   
   * installing *source* package 'RMySQL' ...
   ERROR: configuration failed for package 'RMySQL'
   * removing 'C:/PROGRA~1/R/R-212~1.0/library/RMySQL'
   * restoring previous 'C:/PROGRA~1/R/R-212~1.0/library/RMySQL'
   
    The downloaded packages are in
     ‘C:\Users\sharad\AppData\Local\Temp\RtmpdQHwCb\downloaded_packages’
    Warning message:
         In install.packages("RMySQL", type = "source") :
   installation of package 'RMySQL' had non-zero exit status*
   

Please suggest me how can I solve this problem?

Finally I got the solution:- You can see the solution in detail on my blog.

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
                 <drive>\<path>\R\R-2.14.0\bin\(64 bit) Or
                 <Drive>\<path>\R\R-2.14.0\bin\i386\ (32 bit) and
            to C:\Windows\System32.
   
         b. Renviron.site: create or edit a file 
                 <DRIVE>\<path>\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*

North

I hope somebody made any progress on this. I gave up after a few days of hammering it away and use a linux box to do SQL stuff. – Zoogeography

-1 Read the installation documentation that comes with the package. – Maryannamaryanne

@G.Grothendieck Anyone who thinks documentation written by programmers is necessarily helpful to anyone besides programmers is out of their gourd :) – Meatman

Many of the steps in this question are usually unnecessary. If you have installed a popular MySQL distribution such as XAMPP then installing RMySQL is normally just a standard R package source install with no special instructions. In most cases you don't have to define any environment variables at all (other than those you would have to define for installing any package from source) and normally you don't have to muck around with dll files. If you have put MySQL where it can't find it then you will have to define MYSQL_HOME but only during installation. – Maryannamaryanne

Also whoever downvoted the comment about reading the included documentation is doing a disservice to others. The included documentation is correct and you can't be sure whether other sources are correct or not. Even if they are correct at one point in time typically they get out of date whereas the included documentation is kept up to date. Most of the questions I have seen about RMySQL stem from people reading third party instructions that were wrong or misleading or using old versions of various software. – Maryannamaryanne

@G.Grothendieck OK, I'd like to try the XAMPP solution. I've downloaded MySQL with the XAMPP installer. What would the next steps be? I cannot even see how to use MySQL through XAMPP. – Meatman

There is documentation in the main xampp folder. xampp-control.exe there starts the xampp console and you can turn on and off each component from there. – Maryannamaryanne

@G.Grothendieck Again "go read the documentation you have trouble finding and understanding" is not the fix I'm looking for. However this has become irrelevant because a coworker knowledgable in R and XAMPP has confirmed that the XAMPP approach is useless for our purposes. – Meatman

Answer 1

It is not a direct answer but still you may find it helpful:

1. Use a more up to date version of R (currently at 2.15)

2. On Windows platforms I'd rather use RODBC + Windows MySQL driver, unless you are in a environment with heterogenous platforms (i.e. Linux and Windows) where code is heavily shared among team members. And even then choosing between using RMySQL and RODBC in the same script depending on the platform it runs is a simple if() {...} else {...}

Notice that I am not saying there are no success stories with what you are trying to do, but IMHO you'll be up and running sooner with the above.

Vitta

I was getting same error with R2.15, so i moved to R2.12. I also tried with RODBC but not able to connect R with MySQL. Could you explain how can i use RODBC to connect R and MySQL? – North

I'd stick to R v2.15... anyway, have you installed the MySQL connector packages for Windows? dev.mysql.com/downloads/connector/odbc/#downloads – Vitta

Yes, I have already installed mysql-connector-odbc-5.1.10-winx64. – North

Did you add a connection in your Windows ODBC manager for the MySQL server you are planning to connect? – Vitta

yes i have added in-- Control Panel\All Control Panel Items\Administrative Tools\Data Sources(ODBC) – North

Have you configured a "channel" with the proper server, port, username and password? – Vitta

In Control Panel\All Control Panel Items\Administrative Tools\Data Sources(ODBC) there should be an "Add button" when you click on the MySQL ODBC driver.... – Vitta

I have already added it using this manual dev.mysql.com/doc/refman/5.0/en/… – North

let us continue this discussion in chat – North

Answer 2

You can now skip all the complicated steps and just do install.packages("RMySQL")

Choreodrama

Answer 3

I ran into this over the weekend at a hackathon on Mac OSX - took me a solid 4 hours to piece everything together despite having a few reference materials (mentioned at the end). I didn't find an easy walk-through, so I decided to post one while it is fresh in my mind.

I'm not sure of the compatibility with Windows, but hopefully these instructions will make it easier for you too.

I was trying to get R and MySQL to communicate in a local environment (there may need to be changes for a server environment). I use XAMPP (though I didn't use RMySQL for the connection), but in the end I was able to use a PHP page to write an R file, execute that file, and have R write to a MySQL table. To the best of my knowledge this only works for MacOSX...

All software used was in dmg form so no binary installs necessary.

1. Download R and run some basic commands to make sure that you have it working.

2. In R, you need to install RODBC (if you don't have it already). Type this into the R console.

install.packages("RODBC")

This installs RODBC, but since OS Mavericks, certain files are no longer included, so you get an error message

ODBC headers sql.h and sqlext.h not found

and you need to get the sql.h and sqlext.h files in the right place.

To do this the easiest way, make sure that you have homebrew installed (easy instructions). Then use this code in terminal to make the install.

Once that's done, you enter into the R console one more time

install.packages("RODBC")

3. Search MySQL for the appropriate ODBC installation. I'm running Mac OSX 10.6 so I downloaded the dmg and installed it. This took care of itself.

4. Now comes the tricky part. Apparently Mac OX took out the ODBC Administrator after a recent OS release, so you need to download ODBC Manager (http://www.odbcmanager.net/). It too is a dmg file so just drag and drop to your utilities folder.

I had difficulties with the 5.3.6 dmg install (kept failing), so I installed 5.2.7 instead.

5. Open ODBC Manager. You need to configure the DSN, so click the tab "System DSN" and click "add".

6. You'll get a popup window asking you to select a driver. Mine had "MySQL ODBC 5.2 Driver" based on my MySQL ODBC install. Click "Ok". If you don't see the driver, then you need to confirm that the MySQL ODBC installed.

7. In the next popup window, make the Data Source Name (DSN) whatever you want - but remember that this is the name you need to use to call from R. In the keyword area below (keywords will be in quotes and the value will be in parentheses), ADD

   "database" (with value of your database name)

   "server" (for the local environment do NOT use localhost - instead use the local IP address 127.0.0.1. *** This was the KEY piece for me)

   "uid" (database user ID)

   "pwd" (database password)

   "socket" (not sure if this was required, but after multiple tutorials it was left in my configuration and things work, so maybe you need it. You can find your socket location in my.cnf - do a spotlight search. The socket file location is under CLIENT)

   Here's what my configuration looked like:

   DSN ("test" - this was the at the top)

   database ("televisions")

   socket ("/Applications/XAMPP/xamppfiles/var/mysql.sock")

   uid ("root")

   pwd ("")

   server ("127.0.0.1")

8. In R, execute below - I believe these last 3 steps need to be done every time you start R and before you make a MySQL query.

   library(RODBC)

9. Make sure that you've turned on MySQL and Apache from the XAMPP control panel.

10. Then execute

    odbcConnect("test") - notice how I used my DSN in the double quotes. Interchange as necessary.

This should get you up and running. You can read other tutorials about making MySQL queries in R.

I hacked this together from a lot of great posts on Stack Overflow (thanks everyone!), random other sites/email exchange histories, and the "R In A Nutshell" book by Joseph Adler, but let me know if I missed something or it's unclear.

Good luck!

Selfloading

Answer 4

I also spent a few hours trying to make this work in Windows 10, getting errors. The fixes I found for RMySQL were messy and complex but RODBC has more painlessly and elegantly lead to a solution. I had R, RStudio, MySQL Server, MySQL Workbench and the following additional steps were required to make it work:

1. Install the package in RStudio install.packages(RODBC)
2. Download and install the MySQL ODBC connector here
3. Configure the MySQL ODBC connector. Here are some instructions. Just search from the start screen on Windows 10 for 'ODBC' and it pops up that window. Get the connection parameters right and use the test button to make sure it's working. It shows a list of your databases in a drop down menu once it connects. In "Data Source Name" give it a name which will go into the R script, say 'mysql_odbc'

Now you can connect, run a query and disconnect:

library(RODBC)
cursor <- odbcConnect("mysql_odbc", uid="root", pwd="HaysPuffyWalton5")
out <- sqlQuery(cursor, "SELECT * FROM emp WHERE deptno = 10");
close(cursor);

Wellestablished

Answer 5

This worked for me: Using Windows XP SP3 32bit OS, R Studio, Rv2.15.3

Follow the instructions from: http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL

However if you have downloaded MySQL edition 5.6, you will have to copy libmysql.lib present for example in this path: D:\Program Files\MySQL\MySQL Server 5.6\lib and create a new folder as in: D:\Program Files\MySQL\MySQL Server 5.6\lib\opt and paste the above libmysql.lib in the opt folder. Then the Renviron.site file will have this: MYSQL_HOME=D:/Program Files/MySQL/MySQL Server 5.6. Please note the forward slash.

Finally follow instructions from install.packages line as provided in the link above. I had to do a few trials to get it finally right.

Sherburne

Answer 6

IF THIS ERROR IN R

library(RMySQL)
#-----------
Loading required package: DBI

Error : .onLoad failed in loadNamespace() for 'RMySQL', details

  call: i$Location

  error: $ operator is invalid for atomic vectors

Error: package or namespace load failed for 'RMySQL'

THEN FROM OUTSIDE R

set MYSQL_HOME=F:/Program Files/MySQL/MySQL Server 5.6

AND BACK INSIDE:

library(RMySQL)

#Loading required package: DBI
#MYSQL_HOME defined as F:/Program Files/MySQL/MySQL Server 5.6

OR JUST FROM INSIDE R

Sys.setenv(MYSQL_HOME = "F:/Program Files/MySQL/MySQL Server 5.6")
library(RMySQL)

#Loading required package: DBI
#MYSQL_HOME defined as F:/Program Files/MySQL/MySQL Server 5.6

Enwreathe

Answer 7

Follow the guide here:

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

I followed it, and it worked like a charm. :)

Bareback