Connection lost when connecting R to MySQL using collection

Question

I would like to use dplyrit RMySQLto work with my big data. No problem with the code dplyr. The problem (I think) is exporting data from MySQLto R. My connection is disconnected every time, even if I use n=Infto collect. Theoretically, my data should have more than 50 thousand lines, but I can only get about 15 KB. Any suggestions are welcome.

Approach 1

library(dplyr)
library(RMySQL)

# Connect to a database and select a table 
my_db <- src_mysql(dbname='aermod_1', host = "localhost", user = "root", password = "")   
my_tbl <- tbl(my_db, "db_table") 
out_summary_station_raw <- select(my_tbl, -c(X, Y, AVERAGE_CONC))
out_station_mean_local <- collect(out_summary_station_raw)

Approach 2: use Pool

library(pool)
library(RMySQL)
library(dplyr)

pool <- dbPool(
  drv = RMySQL::MySQL(),
  dbname = "aermod_1",
  host = "localhost",
  username = "root",
  password = ""
)

out_summary_station_raw <- src_pool(pool) %>% tbl("aermod_final") %>% select(-c(X, Y, AVERAGE_CONC))

out_station_mean_local <- collect(out_summary_station_raw, n = Inf)

Warning message (both approaches):

Warning messages:
1: In dbFetch(res, n) : error while fetching rows
2: Only first 15,549 results retrieved. Use n = Inf to retrieve all. 

Update:

, . slow-log Query_time: 79.348351 Lock_time: 0.000000 Rows_sent: 15552 Rows_examined: 16449696, collect . , MySQL Bench.

Answer 1

RMySQL , collect() , . .

.

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

Answer 2

https://github.com/tidyverse/dplyr/issues/1968, https://github.com/tidyverse/dplyr/blob/addb214812f2f45f189ad2061c96ea7920e4db7f/NEWS.md https://github.com/tidyverse/dplyr/commit/addb214812f2f45f189ad2061c96ea7920e4db7f .

dplyr ?