-
Notifications
You must be signed in to change notification settings - Fork 107
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
Comments
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
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:
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)
|
I have the same problem on Windows and rolling back to R 3.3.1; DBI 0.5.1 did not help.... |
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. |
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. |
I had this problem with a large database query (7 million records). Rolling back to RMySQL 0.10.9 worked. Thanks for the suggestion. My session info for the working version:
|
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.
|
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. |
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). |
Will this problem be solved? |
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? |
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) |
I have had the same issue with a nested query, which was fixed by applying the modification made by @raggaraluz . |
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 infodevtools::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) |
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:
|
I have upgraded devtools::install_github("rstats-db/RMySQL@stable") |
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! |
@raggaraluz: Can't replicate with RMySQL 0.10.11, neither from RStudio nor from RGui. |
@jeroen: I've tested your update (with 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! |
It works for me too, although I have tested it after updating also DBI to
0.7.
Thanks!
El jue., 6 jul. 2017 a las 8:51, raggaraluz (<notifications@github.com>)
escribió:
… @jeroen <https://github.com/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!
—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
<#190 (comment)>,
or mute the thread
<https://github.com/notifications/unsubscribe-auth/AAJ93Wh7JA-ouqV2MkVddRgCxXThXzYvks5sLIPVgaJpZM4MoWDR>
.
|
I have DBI 0.7 as well |
@jeroen your solution solves the problem! |
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. |
Can also confirm, had this problem before. The solution @jeroen provided works. |
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. |
I can confirm that the patch fixes the issue I had when I created this issue. |
Also confirming that the upgrade allows me to run large queries. Thanks a ton @jeroen :) |
@jeroen Really appreciate !!! it works stably and fast ! |
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).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.
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
Session info for successful code
The text was updated successfully, but these errors were encountered: