9

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
wibeasley
  • 5,000
  • 3
  • 34
  • 62
OmaymaS
  • 1,671
  • 1
  • 14
  • 18
  • 1
    I have the same problem but can't revert it. I have MySQL workbench installed and working properly as well, with RMySQL failing. My problem also started last week. – Mauricio Tec Apr 03 '17 at 15:31

3 Answers3

6

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

Page
  • 111
  • 3
  • Thanks. I uninstalled R, Rstudio, Rtools, and used the versions I use on the other device, and it worked. However, it would be good to make sure this issue is related to versions. – OmaymaS Apr 01 '17 at 19:41
  • When I try to install an older version as advise, I get: `> install_version("RMySQL", version = "0.10.9", repos = "http://cran.us.r-project.org") Downloading package from url: http://cran.us.r-project.org/src/contrib/Archive/RMySQL/RMySQL_0.10.9.tar.gz /bin/tar: Cannot execute remote shell: No such file or directory /bin/tar: C\:\\Users\\HPPC~1\\AppData\\Local\\Temp\\Rtmps5RBpn\\file1f8436b560b8.tar.gz: Cannot open: I/O error /bin/tar: Error is not recoverable: exiting now Error: Does not appear to be an R package (no DESCRIPTION)` – OmaymaS Apr 08 '17 at 21:11
  • 1
    reverting to previous version has not solved the issue for me. Still getting the same error. – tushaR Jun 12 '17 at 05:35
0

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?

Community
  • 1
  • 1
Anugraha Sinha
  • 621
  • 6
  • 13
0

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")
jFrostad
  • 85
  • 1
  • 6