Error in unserialize: MultisessionFuture (<none>) failed to receive results from cluster RichSOCKnode #1 (PID 235 on ‘localhost’)

[can-taslicukur]

Hi i am using promises with future in my shiny app to make async queries to MySQL database. This is the code that i use to send asynchronous query to database

library(promises)
library(future)
plan(multisession)

dat <- future_promise({
    con <- dbConnect(MySQL(),
                     user = DB_USER, password = DB_PASSWORD,
                     host = DB_HOST, port = DB_PORT,
                     dbname = DB_NAME, encoding = "latin1")
    dbSendQuery(con, "SET NAMES utf8mb4;")
    dbSendQuery(con, "SET CHARACTER SET utf8mb4;")
    dbSendQuery(con, "SET character_set_connection=utf8mb4;")
    dat <- dbGetQuery(con,query$df_sql)
    dbDisconnect(con)
    return(dat)
})

### after some time
dat

However when i run this code, I randomly get the following error

Warning: Error in unserialize: MultisessionFuture (<none>) failed to receive results from cluster RichSOCKnode #1 (PID 235 on ‘localhost’). The reason reported was ‘error reading from connection’. Post-mortem diagnostic: The total size of the 6 globals exported is 74.26 KiB. The three largest globals are ‘query’ (18.91 KiB of class ‘list’) and ‘dbSendQuery’ (10.30 KiB of class ‘function’)

Why do I get this error at random? Sometimes it works exactly like I wanted but sometimes i get this weird error. How can i fix it?

Session Info:

R version 4.1.1 (2021-08-10)
Platform: aarch64-apple-darwin20 (64-bit)
Running under: macOS Monterey 12.0.1

Matrix products: default
LAPACK: /Library/Frameworks/R.framework/Versions/4.1-arm64/Resources/lib/libRlapack.dylib

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets 
[6] methods   base     

loaded via a namespace (and not attached):
[1] compiler_4.1.1 DBI_1.1.1      tools_4.1.1   

[HenrikBengtsson]

Thanks for reporting. It's hard to say why this happens - to me, the code looks valid and there are no obvious pitfalls as far as I can tell.

FWIW, out of the millions(!) multisession futures, I've created over time, I might have ran into this problem maybe once or twice. What is more common is when one exhaust the worker so it crashes, but then the error message says that the worker is no longer alive - that's not the problem in your case.

The only thing I can imagine right now is that the returned results are really large, and that somehow causes problems for R's serialize/unserialize. What range of objectSize(dat) do you expect here?

To see this, or if this happens for specific SQL query (don't see why though), you could try to write debug info to a file. (We can't use cat() and message() here, because those won't be relayed due to this "infrastructure" error.). Something like what I've added to the example at the very end.

Another immediate thing you can do is to try with another, completely different, parallel backend, e.g.

plan(future.callr::callr)

That transfers data back and forth between the main R session and the workers differently from multisession (which rely on parallel's PSOCK framework, where this unserialize error comes from). The callr backend has more overhead, but if you cannot reproduce this problem with it, then that would help point toward stability issues with the PSOCK-based backend.

Since you're on macOS, you could of course also try with plan(multicore). Again, the purpose is to figure out if this is backend specific or not.

Beyond that, I'd suggest you try to reproduce the issue without using shiny and promises. For example, you could stress test this with something like:

library(future)
plan(multisession)

log <- function(..., logfile = "troubleshoot.log") {
  msg <- sprintf(...)
  cat(msg, "\n", sep = "", file = logfile, append = TRUE)
}

query <- "<a typical SQL query>"

repeat({
  f <- future({
      log("Connecting to DB")
      con <- dbConnect(MySQL(),
                       user = DB_USER, password = DB_PASSWORD,
                       host = DB_HOST, port = DB_PORT,
                       dbname = DB_NAME, encoding = "latin1")
      log("Initiate DB query")
      dbSendQuery(con, "SET NAMES utf8mb4;")
      dbSendQuery(con, "SET CHARACTER SET utf8mb4;")
      dbSendQuery(con, "SET character_set_connection=utf8mb4;")
      log("Querying DB: %s", sQuote(query))
      dat <- dbGetQuery(con, query)
      log("Disconnecting from DB")
      dbDisconnect(con)
      log("Returning results of size: %g bytes", objectSize(dat))
      return(dat)
  })
  v <- value(f)
  str(v)
})

If you can reproduce it this way, that would also help narrow in on the problem.

PS. Your session info lists neither future or promises.
PS 2. I'll migrate this to 'Discussions', because at the moment I don't think it's a bug in the future framework per se.

[HenrikBengtsson]

... could that also be the reason why errors happen like in cycles (every nth iteration) rather than random?

Yes, it could possibly be correlate with nbrOfWorkers(), which is another reason why I think it's better to troubleshoot with a single worker to rule that out.

[HenrikBengtsson]

FYI, in contrast to plan(multisession, workers = 1), plan(multisession, workers = 1) falls back to plan(sequential).

[HenrikBengtsson]

... with plan(cluster, workers = 1) ... Result : No errors using RMariaDB

That's interesting. That could suggest that RMariaDB has problems if running in parallel. Can you replicate the error with plan(cluster, workers = 2), or how many workers do you need?

[can-taslicukur]

I have increased workers up to 8, but got no errors, also tried using plan(multisession) again and got no errors too. To be frank, i have no idea what is going on :) . Because [first script] (#570 (reply in thread)) gets error but this script somehow works (we only added seed = T and some logging)

This is the current code:

library(RMariaDB)
library(RMySQL)
library(DBI)
library(future)
plan(multisession)

log <- function(..., logfile = "troubleshoot.log") {
    msg <- sprintf(...)
    cat(msg, "\n", sep = "", file = logfile, append = TRUE)
}

query <- "SELECT id FROM user LIMIT 5"


# MYSQL Test ========
replicate(n = 30,expr = {
    f <- future({
        log("Connecting to DB",logfile = "MySQL.log")
        log(paste(utils::capture.output(showConnections()), collapse = "\n"),logfile = "MySQL.log")
        con <- dbConnect(MySQL(),
                         user = Sys.getenv("DB_USER"), password = Sys.getenv("DB_PASSWORD"),
                         host = Sys.getenv("DB_HOST"), port = as.numeric(Sys.getenv("DB_PORT")),
                         dbname = Sys.getenv("DB_NAME_YSI"), encoding = "latin1")
        log("Initiate DB query",logfile = "MySQL.log")
        log(paste(utils::capture.output(showConnections()), collapse = "\n"),logfile = "MySQL.log")
        dbSendQuery(con, "SET NAMES utf8mb4;")
        dbSendQuery(con, "SET CHARACTER SET utf8mb4;")
        dbSendQuery(con, "SET character_set_connection=utf8mb4;")
        log("Querying DB: %s", sQuote(query),logfile = "MySQL.log")
        dat <- dbGetQuery(con, query)
        log("Disconnecting from DB",logfile = "MySQL.log")
        dbDisconnect(con)
        log(paste(utils::capture.output(showConnections()), collapse = "\n"),logfile = "MySQL.log")
        log("Returning results of size: %g bytes", object.size(dat),logfile = "MySQL.log")
        return(dat)
    },seed = T)
    v <- value(f)
    str(v)
})


# MariaDB Test =======
replicate(n = 30, expr = {
    f <- future({
        log("Connecting to DB",logfile = "MariaDB.log")
        log(paste(utils::capture.output(showConnections()), collapse = "\n"),logfile = "MariaDB.log")
        con <- dbConnect(MariaDB(),
                         user = Sys.getenv("DB_USER"), password = Sys.getenv("DB_PASSWORD"),
                         host = Sys.getenv("DB_HOST"), port = as.numeric(Sys.getenv("DB_PORT")),
                         dbname = Sys.getenv("DB_NAME_YSI"), encoding = "latin1")
        log("Initiate DB query",logfile = "MariaDB.log")
        log(paste(utils::capture.output(showConnections()), collapse = "\n"),logfile = "MariaDB.log")
        dbSendQuery(con, "SET NAMES utf8mb4;")
        dbSendQuery(con, "SET CHARACTER SET utf8mb4;")
        dbSendQuery(con, "SET character_set_connection=utf8mb4;")
        log("Querying DB: %s", sQuote(query),logfile = "MariaDB.log")
        dat <- dbGetQuery(con, query)
        log("Disconnecting from DB",logfile = "MariaDB.log")
        dbDisconnect(con)
        log(paste(utils::capture.output(showConnections()), collapse = "\n"),logfile = "MariaDB.log")
        log("Returning results of size: %g bytes", object.size(dat),logfile = "MariaDB.log")
        return(dat)
    },seed = T)
    v <- tryCatch(value(f),error = function(e){log(as.character(e),logfile = "MariaDB.log");return("Error!")})
    str(v)
})

[HenrikBengtsson]

The inconsistent behavior could indicate a low-level bug in some native code, but there could also be a simple reason, which we're overseeing now. The goal to "fix" this is to get to a reproducible example. Right now, it connects to your DB, so it's only you that can troubleshoot it. I also don't have MariaDB, or MySQL, set up so I cannot try myself.

It's good that you could reproduce it without shiny and promises - that removed a lot from the equation. If you can get a reproducible example using just futures, which is not clear to me whether or not you can. If you can get to that point with a fixed number of parallel workers, that would remove the number of workers from the equation too.

With such a reproducible example, my next proposal will/would be to try to reproduce it with just the parallel package, e.g. res <- clusterEvalQ(cl, { ... }). My gut feeling will be that it'll happening there too, because I don't see anything specific to futures per se here (other than it gives more informative error messages). If we get to this point, I think it's in a position where it can be brought up on https://github.com/r-dbi/RMariaDB/issues.

I recommend that you drop v <- tryCatch(value(f), ...) and just use v <- value(f). That way you'll get an error asap.

[SimoLong1998]

#check the current active plan
plan()
plan("multiprocess", workers = 1 )
#I try it , if you change into "1",maybe no error, hope can help you