R / RMariaDB: How to avoid "Lost connection to server during query" in a loop

Question

I use RMariaDB::dbConnect() to establish a connection to my MySQL database in R.

It works well, but during a long for-loop where I insert values into the database with each iteration (using dbSendStatement()), it happens every so often that I get the error:

Error: Lost connection to server during query [2013]

Is there any way to avoid it?

Are there any (dis-)advantages when I simply include a RMariaDB::dbConnect() in the beginning of every single iteration?

score 0 · Accepted Answer · answered Oct 20 '22 at 18:12

I found an answer based on the %%-operator.

I basically disconnect and reconnect every nth (here: 15th) iteration:

for(i in 1:5000) {

   if (i %% 15 == 0) {
    
       print("Reconnecting to MySQL...")
    
       dbDisconnect(stuffDB)
    
       stuffDB <- dbConnect(MariaDB(), user = "x", password = "x", dbname = "x", host = "x")
    
     }

}

R / RMariaDB: How to avoid "Lost connection to server during query" in a loop

1 Answers1