Sharad Sharad - 3 months ago 20
MySQL Question

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

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*



jcb jcb
Answer

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.

Comments