RMySQL system error: 10060

问题:

I have a project with a connection that was working properly on the same device. I suddenly got the error below. And I could connect from the same device through MySQL workbench.

What could be the reason?

library(RMySQL)

con <- dbConnect(RMySQL::MySQL(),
                 host = "xxx",
                 dbname="yyy",
                 user = "zzz",
                 password = "############")

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

And here's the session info

> sessionInfo()
R version 3.3.1 (2016-06-21)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows >= 8 x64 (build 9200)

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252   
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C                          
[5] LC_TIME=English_United States.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RMySQL_0.10.10 DBI_0.4-1     

loaded via a namespace (and not attached):
[1] tools_3.3.1

回答1:

My code started having this exact problem after doing a system wide package update on 3/29/2017 for the first time since 2/1/2017.

Looking at the versioning for RMySQL, RMySQL_0.10.9.tar.gz published 2016-05-08 15:39 RMySQL_0.10.10.tar.gz published 2017-03-21 07:50

I reverted my version to 0.10.9 and the problem went away:

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

I posted your issue to the RMySQL bug page, so we'll see if they address it

回答2:

I checked another latest version of RMySQL package recently, RMySQL_0.10.11.zip The problem exists in that too. Apologies, cant comment as not allowed currently, hence wanted to update about the version where the problem is still being seen.

Additional Info Connection to MySQL on my system is working from

1) Cmdline

2) MySQL Workbench

3) Tableau

4) Excel

It is just not working on R. I checked following

link1

link2

link3

SHOW GLOBAL STATUS like 'Aborted%clients%';

And every time connection is dropped from R, the value of above variable goes +1. Even doing

SET GLOBAL connect_timeout = 10;

has not helped.

UPDATE

Is there a dependency/requirement for PERL to be installed for usage of RMySQL? As of now, my system does not have PERL. if this requirement is indeed true, then that might be 1 of the reason and may be the latest version has the problem fixed. Please let me know about the PERL dependency?

回答3:

I was able to fix this issue on my PC using R 3.4 by following @Page's advice to look into the package versioning. However, not only did I need to install an earlier version of RMySQL (reverting from 0.10.11 to 0.10.9), I also needed to change versions on its dependancy package DBI (reverting from 0.6-1 to 0.5), using the following command:

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