getting started with R and mysql
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