Could not run statement: Lost connection to MySQL server during query

Question

Transferring an R data frame to a MySQL (MariaDB) database table, I get the following error: Lost connection to MySQL server during query

Example data can be loaded in R with this command

cntxt <- read.delim("http://ec.europa.eu/eurostat/estat-navtree-portlet-prod/BulkDownloadListing?sort=1&file=comext%2FCOMEXT_METADATA%2FCLASSIFICATIONS_AND_RELATIONS%2FENGLISH%2FCN.txt", header = FALSE, quote = "", stringsAsFactors = FALSE)

I use the RMySQL package to transfer the data frame to the the database:

con <-  RMySQL::dbConnect(RMySQL::MySQL(), dbname = "test")    
RMySQL::dbWriteTable(con, "cntxt", cntxt, row.names = FALSE, overwrite = TRUE)

The database write operation works fine on my laptop for tables of any size. But on a server it returns an error. The error only appears for sufficiently large tables (above 1000 rows):

dbWriteTable() succeeds for 1000 lines of data

RMySQL::dbWriteTable(con, "cntxt", head(cntxt,1000), row.names = FALSE, overwrite = TRUE)
# [1] TRUE

dbWriteTable() fails for 2000 lines of data

RMySQL::dbWriteTable(con, "cntxt", head(cntxt,2000), row.names = FALSE, overwrite = TRUE)
# Error in .local(conn, statement, ...) : 
#  could not run statement: Lost connection to MySQL server during query

Based on related questions, I have checked the value of max_allowed_packet:

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
| max_allowed_packet | 16777216 |

16Mb should be more than enough for 2000 lines of data. Where is the error coming from? There is nothing visible in mysql the error log /var/log/mysql/error.log.

Server version: 10.1.26-MariaDB-0+deb9u1 Debian 9.1

I can't honestly say I've ever used MySQL very often from R, but what I can suggest is that you just loop and insert 1000 R data frame rows at a time. You know the call will work, and since SQL tables are inherently unordered, it makes no difference in which order you insert all your data, so long as all your data in fact gets inserted into MySQL. — Tim Biegeleisen, May 28 '18 at 13:43
@TimBiegeleisen thanks, I have already edited part of the project to load data from files directly from the mysql client. This R code used to work on other laptops and servers. I am struggling to find a more relevant error message. In this case, I use R to modify the data before saving it back to mysql. — Paul Rougieux, May 28 '18 at 13:52

Paul Rougieux · Accepted Answer · 2018-05-29T10:15:38.797

Replace the RMySQL package with the newer RMariaDB package

install.packages("RMariaDB")

Then tables over 2000 rows can be transferred again.

con <- RMariaDB::dbConnect(RMariaDB::MariaDB(), dbname="test")
cntxt <- read.delim("http://ec.europa.eu/eurostat/estat-navtree-portlet-prod/BulkDownloadListing?sort=1&file=comext%2FCOMEXT_METADATA%2FCLASSIFICATIONS_AND_RELATIONS%2FENGLISH%2FCN.txt", header = FALSE, quote = "", stringsAsFactors = FALSE)
RMariaDB::dbWriteTable(con, "cntxt", cntxt, row.names = FALSE, overwrite = TRUE)

Could not run statement: Lost connection to MySQL server during query

1 Answers1