R help - Aug 2012 - getting started with R and mysql

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?

 > 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
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
 >

-- 
Michael Friendly     Email: friendly AT yorku DOT ca
Professor, Psychology Dept.
York University      Voice: 416 736-2100 x66249 Fax: 416 736-5814
4700 Keele Street    Web:   http://www.datavis.ca
Toronto, ONT  M3J 1P3 CANADA

> 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?) 
We use RODBC.  Is there a reason that won't work?

cur
-- 
Curt Seeliger, Data Ranger
Raytheon Information Services - Contractor to ORD
seeliger.curt@epa.gov
541/754-4638
	[[alternative HTML version deleted]]

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