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:
Install latest RTools from here
install MySQL or header and library files of mysql
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)
copy libmysql.lib from mysql/lib to mysql/lib/opt to meet dependencies.
copy libmysql.dll to C:\Program Files\R\R-2.12.1\bin or to windows/system32 directory.
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:-
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*
SET THE FOLLOWING ENVIRONMENT VARIABLES
* a. MYSQL_HOME : /path to MySQL installation folder
e.g. MYSQL_HOME= C:\Program Files\MySQL\MySQL Server 5.5\
b. R_HOME: /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
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
\\R\R-2.14.0\bin\(64 bit) Or
\\R\R-2.14.0\bin\i386\ (32 bit) and
to C:\Windows\System32.
b. Renviron.site: create or edit a file
\\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*
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*
解决方案
It is not a direct answer but still you may find it helpful:
Use a more up to date version of R (currently at 2.15)
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.