Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Lost connection to MySQL server during query #190

Closed
tjmahr opened this issue Mar 24, 2017 · 27 comments
Closed

Lost connection to MySQL server during query #190

tjmahr opened this issue Mar 24, 2017 · 27 comments

Comments

@tjmahr
Copy link

tjmahr commented Mar 24, 2017

I updated to new versions of DBI and RMySQL and some of my queries that work on MySQL views no longer work. Specifically, I get "Lost connection to MySQL server during query" errors when reading MySQL views with many rows.

I'm not sure how to go about troubleshooting the problem because of the interaction of RMySQL, native Rcpp code and DBI. Plus, this is a private organizational database so it's not exactly reproducible.

I can get dbReadTable() to work on views with 20,000 rows but it fails for larger ones (~2,000,000 rows).

# Work relative to RStudio project
wd <- rprojroot::find_rstudio_root_file()
cnf_file <- file.path(wd, "inst", "l2t_db.cnf")

library(DBI)
library(RMySQL)

conn <- dbConnect(RMySQL::MySQL(), default.file = cnf_file, dbname = "eyetracking")

# Getting from a smallish view works
tibble::as_tibble(dbReadTable(conn, "q_BlockAttributesByStudy"))
#> # A tibble: 20,696 × 9
#> [removed]

# Getting from a larger view fails
tibble::as_tibble(dbReadTable(conn, "q_TrialAttributesByStudy"))
#> Error in .local(conn, statement, ...): could not run statement: Lost connection to MySQL server during query

To troubleshoot, I rolled the DBI version back to 0.5-1 and still got the error. Next, I rolled the RMySQL version back to 0.10.9 and the query worked. It took a couple minutes to download the table results but it worked nevertheless.

# Code to roll back versions. Which is why session_info says URL now for source
devtools::install_version("DBI", version = "0.5-1", repos = "http://cran.us.r-project.org")
devtools::install_version("RMySQL", version = "0.10.9", repos = "http://cran.us.r-project.org")
tibble::as_tibble(dbReadTable(conn, "q_TrialAttributesByStudy"))
#> # A tibble: 2,152,429 × 11
#> [removed]

The failure happens rather quickly. It's not like R waits a minute and stalls out during the query. Is there some sort of setting or limiting going on that makes it give up early?

This only happens with views in MySQL. I can query a table with 2 million rows fine. But when there are some joins being done server-side, the query fails.

Session info for failing code
> devtools::session_info()
Session info ---------------------------------------------
 setting  value                       
 version  R version 3.3.2 (2016-10-31)
 system   x86_64, mingw32             
 ui       RStudio (1.0.136)           
 language (EN)                        
 collate  English_United States.1252  
 tz       America/Chicago             
 date     2017-03-24                  

Packages ----------------------------------------------------
 package    * version date       source        
 assertthat   0.1     2013-12-06 CRAN (R 3.0.2)
 backports    1.0.5   2017-01-18 CRAN (R 3.3.2)
 DBI        * 0.6     2017-03-09 CRAN (R 3.3.3)
 devtools     1.12.0  2016-06-24 CRAN (R 3.3.2)
 digest       0.6.12  2017-01-27 CRAN (R 3.3.2)
 memoise      1.0.0   2016-01-29 CRAN (R 3.2.3)
 Rcpp         0.12.10 2017-03-19 CRAN (R 3.3.3)
 RMySQL     * 0.10.10 2017-03-21 CRAN (R 3.3.3)
 rprojroot    1.2     2017-01-16 CRAN (R 3.3.2)
 tibble       1.2     2016-08-26 CRAN (R 3.3.1)
 withr        1.0.2   2016-06-20 CRAN (R 3.3.1)
Session info for successful code
> devtools::session_info()
Session info -------------------------
 setting  value                       
 version  R version 3.3.2 (2016-10-31)
 system   x86_64, mingw32             
 ui       RStudio (1.0.136)           
 language (EN)                        
 collate  English_United States.1252  
 tz       America/Chicago             
 date     2017-03-24                  

Packages ----------------------------
 package    * version date       source        
 assertthat   0.1     2013-12-06 CRAN (R 3.0.2)
 backports    1.0.5   2017-01-18 CRAN (R 3.3.2)
 DBI        * 0.5-1   2016-09-10 url           
 devtools     1.12.0  2016-06-24 CRAN (R 3.3.2)
 digest       0.6.12  2017-01-27 CRAN (R 3.3.2)
 memoise      1.0.0   2016-01-29 CRAN (R 3.2.3)
 Rcpp         0.12.10 2017-03-19 CRAN (R 3.3.3)
 RMySQL     * 0.10.9  2016-05-08 url           
 rprojroot    1.2     2017-01-16 CRAN (R 3.3.2)
 tibble       1.2     2016-08-26 CRAN (R 3.3.1)
 withr        1.0.2   2016-06-20 CRAN (R 3.3.1)
@chambm
Copy link

chambm commented Mar 27, 2017

I can reproduce this except for getting it to work by rolling back to 0.5.1/0.10.9. I also tried installing 0.11.5 and that didn't work either. I get the same Lost connection to MySQL server during query error. The exact same R code works when I test it on a Linux gateway with nearly equivalent versions:

library(RMySQL)
ucscDb = dbConnect(RMySQL::MySQL(), host="genome-mysql.cse.ucsc.edu", port=3306, user="genomep", password="password", dbname="hg19")

sql = 'SELECT snp.name, snp.chrom, chromStart, chromEnd, alleleCount, alleles, snp.transcript 
FROM snp146CodingDbSnp snp
JOIN (SELECT chrom, txStart, txEnd FROM refGene WHERE name IN ("NM_001126112", "NM_033360", "NR_073499", "NM_004448", "NM_000179", "NR_029605", "NM_004333", "NM_001127511")) txInfo ON snp.chrom=txInfo.chrom
AND snp.chromStart BETWEEN txInfo.txStart AND txInfo.txEnd
WHERE snp.transcript IN ("NM_001126112", "NM_033360", "NR_073499", "NM_004448", "NM_000179", "NR_029605", "NM_004333", "NM_001127511")'

results = dbGetQuery(ucscDb, sql)

On Windows/RStudio, I get the error; on Linux, I get the expected results. I tested with the MySQL shell on the same Windows machine and the query works fine.

Working Linux session info:

Session info -------------------------------------------------------------------
 setting  value
 version  R version 3.3.2 (2016-10-31)
 system   x86_64, linux-gnu
 ui       X11
 language (EN)
 collate  en_US.UTF-8
 tz       America/Indianapolis
 date     2017-03-27

Packages -----------------------------------------------------------------------
 package       * version date       source
 DBI           * 0.5-1   2016-09-10 CRAN (R 3.3.2)
 devtools        1.12.0  2016-12-05 CRAN (R 3.3.2)
 digest          0.6.12  2017-01-27 CRAN (R 3.3.2)
 GetoptLong    * 0.1.6   2017-03-07 CRAN (R 3.3.2)
 GlobalOptions   0.0.11  2017-03-06 CRAN (R 3.3.2)
 memoise         1.0.0   2016-01-29 CRAN (R 3.3.2)
 rjson           0.2.15  2014-11-03 CRAN (R 3.3.2)
 RMySQL        * 0.10.10 2017-03-21 CRAN (R 3.3.2)
 withr           1.0.2   2016-06-20 CRAN (R 3.3.2)

Broken Windows session info: (tested with R 3.3.1, 3.3.3; DBI 0.5.1, 0.6; RMySQL 0.10.9, 0.10.10, 0.11.5)

Session info --------------------------------------------------
 setting  value                       
 version  R version 3.3.3 (2017-03-06)
 system   x86_64, mingw32             
 ui       RStudio (1.0.136)           
 language (EN)                        
 collate  English_United States.1252  
 tz       America/Chicago             
 date     2017-03-27                  

Packages ------------------------------------------------
 package       * version date       source        
 DBI           * 0.5-1   2016-09-10 url           
 devtools        1.12.0  2016-06-24 CRAN (R 3.3.3)
 digest          0.6.12  2017-01-27 CRAN (R 3.3.3)
 GetoptLong    * 0.1.6   2017-03-07 CRAN (R 3.3.3)
 GlobalOptions   0.0.11  2017-03-06 CRAN (R 3.3.3)
 memoise         1.0.0   2016-01-29 CRAN (R 3.3.2)
 rjson           0.2.15  2014-11-03 CRAN (R 3.3.2)
 RMySQL        * 0.10.9  2016-05-08 url           
 withr           1.0.2   2016-06-20 CRAN (R 3.3.2)

@vaulot
Copy link

vaulot commented Apr 2, 2017

I have the same problem on Windows and rolling back to R 3.3.1; DBI 0.5.1 did not help....

@nokiddn
Copy link

nokiddn commented Apr 5, 2017

I have the same issue on Windows 7. The curious thing is that some queries do not work with the 64bit R but do work with the 32bit R. R 3.3.2, DBI 0.5.1, RMySQL 0.10.9.

@cloud-brain
Copy link

I also have this problem. And I find it always happen when I try to write a table with million rows. So I try to split the table into several chunks, but it didn't work after several loop. I also try to change the net_read_timeout and net_write_timeout of MySQL which didn't work, too. At last, I have to roll back to 64bit R. R 3.3.3, DBI 0.4-1, RMySQL 0.10.9 and it works.

@chrismerkord
Copy link

I had this problem with a large database query (7 million records).

Rolling back to RMySQL 0.10.9 worked. Thanks for the suggestion.
I did not need to roll back DBI, which is still at 0.6-1.

My session info for the working version:

R version 3.3.3 (2017-03-06)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

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] dplyr_0.5.0              epidemiaweb_0.0.6.4.9000

loaded via a namespace (and not attached):
 [1] Rcpp_0.12.10    lattice_0.20-35 lubridate_1.6.0 tidyr_0.6.1     zoo_1.7-14      packrat_0.4.8-1
 [7] assertthat_0.1  grid_3.3.3      R6_2.2.0        DBI_0.6-1       magrittr_1.5    stringi_1.1.3  
[13] lazyeval_0.2.0  epical_0.3.4    RMySQL_0.10.9   tools_3.3.3     stringr_1.2.0   tibble_1.3.0 

@swatford
Copy link

I'm finding the same problems (DBI 0.6-1/RMySQL 0.10.11). I can get my code to work as long as none of my queries are nested queries. It looks like the examples from the comments are all nested too.

R version 3.3.3 (2017-03-06)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

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

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

other attached packages:
[1] tcpl_1.3          data.table_1.10.4

loaded via a namespace (and not attached):
 [1] parallel_3.3.3     DBI_0.6-1          tools_3.3.3        RColorBrewer_1.1-2 memoise_1.0.0      Rcpp_0.12.10      
 [7] RSQLite_1.1-2      RMySQL_0.10.11     digest_0.6.12      numDeriv_2016.8-1

@Breza
Copy link

Breza commented Apr 25, 2017

After upgrading I was having the same problem with complicated nested subqueries that had huge numbers of results. Downgrading per the suggestion from @tjmahr fixed it.

@crazybilly
Copy link

I also have been plagued by this bug. To get things working once again, I had to downgrade my packages and downgrade R to 3.3.2 i386 (as opposed to 3.4 or 3.3.3 or 3.3.2 64 bit).

@titaiwangms
Copy link

Will this problem be solved?
I import RMySQL in my R package and I got the same error only in windows version as well.
downgrade package version isn't a great solution...

@ofc587a87
Copy link

I'm having the same problem with a select count(distinct) of a big table... Downgrading is not possible in my case as my server is shared with co-workers.

Any news about this problem?

@raggaraluz
Copy link

raggaraluz commented Jun 8, 2017

Issue seems related to a bug that has been recently fixed on mariadb connector 2.3.3, related to query timeout misconfiguration (mariadb-corporation/mariadb-connector-c@42d6d3f). It makes long queries to "timeout" too soon (in about 1 second) and close the MySQL connection.

It may be fixed by either upgrading mariadb connector to 2.3.3 in RMySQL (currently it is using 2.3.2), or by adding a parameter to configure the query timeout in the connection.

Pull request #203 provides a patch for the second option (configuring an explicit timeout)

@CharlesCara
Copy link

I have had the same issue with a nested query, which was fixed by applying the modification made by @raggaraluz .

@krlmlr
Copy link
Member

krlmlr commented Jul 1, 2017

I'm having trouble replicating this.

Setup: Win10 64-bit, MariaDB 10.2 local installation.

Test query: Repeated cross join of a three-row virtual table, takes more than one second to run. Works for me.

library(DBI)
library(magrittr)

N <- 15

con <- dbConnect(RMySQL::MySQL())
query <- 
  letters[1:N] %>%
  paste0("(SELECT 1 AS ", ., " UNION SELECT 2 UNION SELECT 3) ", ., collapse = " CROSS JOIN\n")
cat(query)
#> (SELECT 1 AS a UNION SELECT 2 UNION SELECT 3) a CROSS JOIN
#> (SELECT 1 AS b UNION SELECT 2 UNION SELECT 3) b CROSS JOIN
#> (SELECT 1 AS c UNION SELECT 2 UNION SELECT 3) c CROSS JOIN
#> (SELECT 1 AS d UNION SELECT 2 UNION SELECT 3) d CROSS JOIN
#> (SELECT 1 AS e UNION SELECT 2 UNION SELECT 3) e CROSS JOIN
#> (SELECT 1 AS f UNION SELECT 2 UNION SELECT 3) f CROSS JOIN
#> (SELECT 1 AS g UNION SELECT 2 UNION SELECT 3) g CROSS JOIN
#> (SELECT 1 AS h UNION SELECT 2 UNION SELECT 3) h CROSS JOIN
#> (SELECT 1 AS i UNION SELECT 2 UNION SELECT 3) i CROSS JOIN
#> (SELECT 1 AS j UNION SELECT 2 UNION SELECT 3) j CROSS JOIN
#> (SELECT 1 AS k UNION SELECT 2 UNION SELECT 3) k CROSS JOIN
#> (SELECT 1 AS l UNION SELECT 2 UNION SELECT 3) l CROSS JOIN
#> (SELECT 1 AS m UNION SELECT 2 UNION SELECT 3) m CROSS JOIN
#> (SELECT 1 AS n UNION SELECT 2 UNION SELECT 3) n CROSS JOIN
#> (SELECT 1 AS o UNION SELECT 2 UNION SELECT 3) o

system.time(dbGetQuery(con, paste0("SELECT a FROM ", query)))
#>    user  system elapsed 
#>    2.67    0.18    7.66
Session info
devtools::session_info()
#> Session info -------------------------------------------------------------
#>  setting  value                       
#>  version  R version 3.4.1 (2017-06-30)
#>  system   x86_64, mingw32             
#>  ui       RTerm                       
#>  language (EN)                        
#>  collate  English_United States.1252  
#>  tz       Europe/Berlin               
#>  date     2017-07-01
#> Packages -----------------------------------------------------------------
#>  package   * version date       source        
#>  backports   1.1.0   2017-05-22 CRAN (R 3.4.0)
#>  base      * 3.4.1   2017-06-30 local         
#>  compiler    3.4.1   2017-06-30 local         
#>  datasets  * 3.4.1   2017-06-30 local         
#>  DBI       * 0.7     2017-06-18 CRAN (R 3.4.0)
#>  devtools    1.13.2  2017-06-02 CRAN (R 3.4.0)
#>  digest      0.6.12  2017-01-27 CRAN (R 3.4.0)
#>  evaluate    0.10.1  2017-06-24 CRAN (R 3.4.0)
#>  graphics  * 3.4.1   2017-06-30 local         
#>  grDevices * 3.4.1   2017-06-30 local         
#>  htmltools   0.3.6   2017-04-28 CRAN (R 3.4.0)
#>  knitr       1.16    2017-05-18 CRAN (R 3.4.0)
#>  magrittr  * 1.5     2014-11-22 CRAN (R 3.3.2)
#>  memoise     1.1.0   2017-04-21 CRAN (R 3.4.0)
#>  methods   * 3.4.1   2017-06-30 local         
#>  Rcpp        0.12.11 2017-05-22 CRAN (R 3.4.0)
#>  rmarkdown   1.6     2017-06-15 CRAN (R 3.4.0)
#>  RMySQL      0.10.11 2017-03-29 CRAN (R 3.4.0)
#>  rprojroot   1.2     2017-01-16 CRAN (R 3.4.0)
#>  stats     * 3.4.1   2017-06-30 local         
#>  stringi     1.1.5   2017-04-07 CRAN (R 3.4.0)
#>  stringr     1.2.0   2017-02-18 CRAN (R 3.4.0)
#>  tools       3.4.1   2017-06-30 local         
#>  utils     * 3.4.1   2017-06-30 local         
#>  withr       1.0.2   2016-06-20 CRAN (R 3.4.0)
#>  yaml        2.1.14  2016-11-12 CRAN (R 3.4.0)

@raggaraluz
Copy link

raggaraluz commented Jul 2, 2017

You can reproduce using this:

library(DBI)

con <- dbConnect(RMySQL::MySQL(), host='db4free.net',
                 user='rmysqltest', password='rmysqltest',
                 dbname='rmysqltest')
query <- 'select count(val), avg(val) from foo'
dbGetQuery(con, query)

I was using Windows 7 enterprise and RMySQL from CRAN

Session info:

> devtools::session_info()
Session info ------------------------------------------------------------------
 setting  value                                      
 version  R version 3.4.0 Patched (2017-05-25 r72746)
 system   x86_64, mingw32                            
 ui       Rgui                                       
 language (EN)                                       
 collate  Spanish_Spain.1252                         
 tz       Europe/Paris                               
 date     2017-07-02                                 

Packages ----------------------------------------------------------------------
 package   * version date       source        
 base      * 3.4.0   2017-05-31 local         
 compiler    3.4.0   2017-05-31 local         
 datasets  * 3.4.0   2017-05-31 local         
 DBI       * 0.7     2017-06-18 CRAN (R 3.4.1)
 devtools    1.13.1  2017-05-13 CRAN (R 3.4.0)
 digest      0.6.12  2017-01-27 CRAN (R 3.4.0)
 graphics  * 3.4.0   2017-05-31 local         
 grDevices * 3.4.0   2017-05-31 local         
 memoise     1.1.0   2017-04-21 CRAN (R 3.4.0)
 methods   * 3.4.0   2017-05-31 local         
 RMySQL      0.10.11 2017-03-29 CRAN (R 3.4.1)
 stats     * 3.4.0   2017-05-31 local         
 utils     * 3.4.0   2017-05-31 local         
 withr       1.0.2   2016-06-20 CRAN (R 3.3.1)

@jeroen
Copy link
Member

jeroen commented Jul 5, 2017

I have upgraded libmariadbclient on Windows to v2.3.3 for the RMySQL stable branch. Can you confirm if this resolves the problem?

devtools::install_github("rstats-db/RMySQL@stable")

@krlmlr

@crazybilly
Copy link

crazybilly commented Jul 5, 2017

Ok, an update: with the downgraded DBI, it didn't work. I upgraded DBI to the current cran version (0.7), the reinstalled the stable branch above. Things seem to be working well!

@krlmlr
Copy link
Member

krlmlr commented Jul 5, 2017

@raggaraluz: Can't replicate with RMySQL 0.10.11, neither from RStudio nor from RGui.

@raggaraluz
Copy link

@jeroen: I've tested your update (with libmariadb v2.3.3) and it works right!!!

For double checking, I've downgraded to 0.10.11 afterwards and the error came back.

So this fixes the error (at least for me)

Thanks!

@ofc587a87
Copy link

ofc587a87 commented Jul 6, 2017 via email

@raggaraluz
Copy link

I have DBI 0.7 as well

@gammagandalf
Copy link

gammagandalf commented Jul 6, 2017

@jeroen your solution solves the problem!
Thanks!

@jeroen
Copy link
Member

jeroen commented Jul 6, 2017

OK I have submitted RMySQL 0.10.12 to CRAN. Hopefully it gets published soon and then binaries will appear over the weekend. Let's see after that if there is still a problem.

@granstubbe
Copy link

Can also confirm, had this problem before. The solution @jeroen provided works.

@jeroen
Copy link
Member

jeroen commented Jul 7, 2017

The binaries are now on CRAN. You can upgrade to RMySQL 0.10.12 using

install.packages("RMySQL")

If the problem is not solved for you, please open a new issue with a reproducible example.

@jeroen jeroen closed this as completed Jul 7, 2017
@tjmahr
Copy link
Author

tjmahr commented Jul 7, 2017

I can confirm that the patch fixes the issue I had when I created this issue.

@jrausch12
Copy link

Also confirming that the upgrade allows me to run large queries. Thanks a ton @jeroen :)

@jzhliuran
Copy link

@jeroen Really appreciate !!! it works stably and fast !

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests