Using Mysql with R in the Rmysql package

Question

I am new when the subject is Mysql and installed this program to use with R. I managed to install the Rmysql library, following tutorials I found on the Internet and in the course I am doing in Coursera as

http://biostat.mc.vanderbilt.edu/wiki/Main/RMySQL and

http://www.ahschulz.de/2013/07/23/installing-rmysql-under-windows/

I even tried to install an older second Rmysql library: https://stackoverflow.com/questions/43073782/rmysql-system-error-10060

Using the code:

require(devtools)

install_version("RMySQL", version = "0.10.9", repos = "http://cran.us.r-
project.org")

But none of that worked. When I went to test Mysql, connecting with UCLA Mysql using the following command:

ucscDb <- dbConnect(MySQL(), user="genome", host="genome-mysql.cse.ucsc.edu")

The following error appeared:

Error in .local(drv, ...) : 
Failed to connect to database: Error: Lost connection to MySQL server at 
'reading authorization packet', system error: 10060

I believe this happened due to problems in installing Mysql. Does anyone have any hint or suggestion?

Answer 1

The easiest method of installing Rmysql on R for Windows is via install.packages.

Do the following:

Step 1: Normal installation of R for Windows https://cran.r-project.org/bin/windows/base/

Step 2: Open the R environment and install the latest version of Rmysql straight from CRAN:

> install.packages("RMySQL", repos="http://cran.r-project.org")
Warning in install.packages("RMySQL", repos = "http://cran.r-project.org") :
  'lib = "C:/Program Files/R/R-3.4.0/library"' is not writable
also installing the dependency ‘DBI’

tentando a URL 'http://cran.r-project.org/bin/windows/contrib/3.4/DBI_0.6-1.zip'
Content type 'application/zip' length 745244 bytes (727 KB)
downloaded 727 KB

tentando a URL 'http://cran.r-project.org/bin/windows/contrib/3.4/RMySQL_0.10.11.zip'
Content type 'application/zip' length 2296883 bytes (2.2 MB)
downloaded 2.2 MB

package ‘DBI’ successfully unpacked and MD5 sums checked
package ‘RMySQL’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
        C:\Users\alastori\AppData\Local\Temp\RtmpwLAGXJ\downloaded_packages

Step 3: Test the Mysql connection

> library(DBI)
> ucscDb <- dbConnect(RMySQL::MySQL(), user="genome", host="genome-mysql.cse.ucsc.edu")
> result <- dbGetQuery(ucscDb,"show databases;"); dbDisconnect(ucscDb);
[1] TRUE
> result
              Database
1   information_schema
2              ailMel1
3              allMis1
4              anoCar1
5              anoCar2
...

Have fun!