[R-sig-DB] RMySQL "lost connection" during dbWriteTable()

[Krzysztof Sakrejda]

I've had this problem before, writing over a slow internet connection
and I believe it would be nice to make the dbWriteTable command do
this... but I haven't had the chance to do anything useful about it,
other than write this quick and dirty function for myself.  This
function carelessly re-uses dbConnect which can exhaust available
connections, but it could be adapted to do a check, like the appended
dbReconnect function.  Hope that helps...  Krzysztof

dbRobustWriteTable <- function(conn, user, password, host, dbname,
name, value, tries) {
    numFullChunks <- nrow(value)%/%100
    lengthLastChunk <- nrow(value)%%100
    if (numFullChunks >= 1) {
        writeSeqFullChunks <- data.frame(Start =
seq(0,numFullChunks-1,1)*100+1, Stop = seq(1,numFullChunks,1)*100)
    }
    writeSeqLastChunk <- data.frame(Start = numFullChunks*100+1, Stop
= numFullChunks*100+lengthLastChunk)
    if (numFullChunks >= 1) {
        writeSeqAllChunks <- rbind(writeSeqFullChunks,writeSeqLastChunk)
    } else { writeSeqAllChunks <- writeSeqLastChunk }

    for(i in 1:nrow(writeSeqAllChunks)) {
            try <- 0
            rowSeq <-
seq(writeSeqAllChunks$Start[i],writeSeqAllChunks$Stop[i],1)
            while (!dbWriteTable(conn = conn, name = name, value =
value[rowSeq,], overwrite = FALSE, append = TRUE) & try < tries) {
                conn  <-
dbConnect(MySQL(),user=user,password=password,host=host,dbname=dbname)
                try <- try + 1
                if (try == tries) { stop("EPIC FAIL") }
                print(paste("Fail number",try,"epical fail
at",tries,"tries.",sep = " "))
            }
    }
}


dbReconnect <- function(
  conn = NULL,
  idFile = NULL,
  drv = MySQL()
) {
    if (  is.null(idFile) ) {
        stop('Can\'t reconnect, no id.')
    }
    if ( !is.null(conn) ) {
      tryCatch(
        expr = dbDisconnect( conn = conn ),
        error = function(cond) {
          print(cond)
        }
      )
    }
    ev = new.env()
    load( file = idFile, envir = ev )
    ev$drv <- drv
    conn <- with(
        data = ev,
        expr = dbConnect(
            drv = drv,
            user = user,
            password = pass,
            dbname = dbname,
            host = host
        )
    ); rm(ev)
    return(conn)

}



On Tue, Oct 1, 2013 at 8:45 AM, Magnus Thor Torfason
<zulutime.net using gmail.com> wrote:
> When doing dbWriteTable() to write my (admittedly large) data.frame to a
> MySQL database I randomly get the following error:
>
>>     dbWriteTable(conn, "tablename", data)
> Error in mysqlExecStatement(conn, statement, ...) :
>   RS-DBI driver: (could not run statement: Lost connection to MySQL server
> during query)
>
> Googling on MySQL in general suggests that this can be the result of sending
> too big of a chunk of data to MySQL at the same time.
>
> But the data.frame sits comfortably in memory and the table sits comfortably
> in the database. So it is disappointing that what I thought was the
> recommended way to achieve this task (dbWriteTable), should be a bottleneck.
>
> I suppose I could write a loop around dbWriteTable(), writing only a few
> rows of the data.frame at the same time, but that seems like something that
> dbWriteTable() should be doing internally.
>
> Are there any good suggestions on what I should do?
>
> Best,
> Magnus
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using r-project.org
> https://stat.ethz.ch/mailman/listinfo/r-sig-db



-- 

Krzysztof Sakrejda

Organismic and Evolutionary Biology
University of Massachusetts, Amherst
319 Morrill Science Center South
611 N. Pleasant Street
Amherst, MA 01003

work #: 413-325-6555
email: sakrejda using cns.umass.edu