[R] getting started with R and mysql

[Prof Brian Ripley]

On 08/08/2012 18:42, Michael Friendly wrote:
> I have a mysql database I installed on my Ubuntu server, and I'm trying
> to figure out how to access and analyze it
> from a Win XP machine running R 2.15.1.
>
> I thought that RMySQL was the way to go. (Is there an easier way?) I
> tried to follow the instructions at
> http://www.r-bloggers.com/installing-the-rmysql-package-on-windows-7/
> so I installed mysql-installer-community-5.5.27.0.msi, then set the
> environment variable MYSQL_HOME,
> and tried to install RMySQL within R. It failed, as shown below, but I
> can't see what is wrong.
> What's missing? If I only want to read tables from the database and work
> on them in R, is there an
> easier package?

 From Windows, RODBC is likely to be easier, not least as binaries are 
available.  You will need to install the ODBC driver of the appropriate 
(32- or 64-bit) architecture.

A while ago the tricky steps were to get the Unix-alike server to allow 
access from elsewhere.  This may have changed (because we have serious 
MySQL database servers, I now leave this to my sysadmins).

>
>  > Sys.getenv("MYSQL_HOME")
> [1] "C:\\Program Files\\MySQL\\MySQL Server 5.5"
>  > install.packages('RMySQL',type='source')
> also installing the dependency ‘DBI’
>
> trying URL 'http://probability.ca/cran/src/contrib/DBI_0.2-5.tar.gz'
> Content type 'application/x-gzip' length 308395 bytes (301 Kb)
> opened URL
> downloaded 301 Kb
>
> trying URL 'http://probability.ca/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 'DBI' ...
> ** R
> ** inst
> ** preparing package for lazy loading
> Creating a generic function for 'summary' from package 'base' in package
> 'DBI'
> ** help
> *** installing help indices
> ** building package indices
> ** installing vignettes
> 'DBI.Rnw'
> ** testing if installed package can be loaded
>
> * DONE (DBI)
> * installing *source* package 'RMySQL' ...
> ** package 'RMySQL' successfully unpacked and MD5 sums checked
> checking for $MYSQL_HOME... C:\Program Files\MySQL\MySQL Server 5.5
> test: Files\MySQL\MySQL: unknown operand
> ** libs
> Warning: this package has a non-empty 'configure.win' file,
> so building only the main architecture
>
> gcc -I"C:/R/R-215~1.1/include" -DNDEBUG -I"C:\Program Files\MySQL\MySQL
> Server 5.5"/include -O3 -Wall -std=gnu99 -mtune=core2 -c RS-DBI.c -o
> RS-DBI.o
> RS-DBI.c:1: error: bad value (core2) for -mtune= switch

That looks like the wrong compiler.

> make: *** [RS-DBI.o] Error 1
> ERROR: compilation failed for package 'RMySQL'
> * removing 'C:/R/R-2.15.1/library/RMySQL'
>
> The downloaded source packages are in
> ‘C:\Documents and Settings\friendly\Local
> Settings\Temp\RtmpCU2QBs\downloaded_packages’
> Warning messages:
> 1: running command 'C:/R/R-2.15.1/bin/i386/R CMD INSTALL -l
> "C:/R/R-2.15.1/library"
> C:\DOCUME~1\friendly\LOCALS~1\Temp\RtmpCU2QBs/downloaded_packages/RMySQL_0.9-3.tar.gz'
> had status 1
> 2: In install.packages("RMySQL", type = "source") :
> installation of package ‘RMySQL’ had non-zero exit status
>  >
>


-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595