RStudio Shiny Error mysqlNewConnection maximum of 16 connections

Question

I got a shiny server running that connects to a MySQL database. The page that was so far working fine is giving me this error now:

Error in mysqlNewConnection(drv, ...) : RS-DBI driver: 
(cannot allocate a new connection -- maximum of 16 connections already opened)

Which makes me wonder how should I be handling open mysql connections in an interactive webpage.

Firstly, should the dbConnect(MySQL(),...) statement be before the shinyServer method or inside?

If I add a dbDisconnect(dbcon) at the end of server.R, then I get an Error: expired MysqLConnection error from the page, and does not show any data.

I tried with this as well inside or before the shinyServer method:

 on.exit(dbDisconnect(dbcon), add=TRUE)

or

 on.exit(dbDisconnect(dbcon))

So the code for the page itself only works for me if I leave the connection open, which I suppose then can cause the maximum connections error above.

How to handle these situations in Shiny?

Answer 1

You can set the connection in the global.R file or outside shinyServer an example from https://groups.google.com/forum/#!topic/shiny-discuss/0VjQc2a6z3M is:

library(RMySQL)

getConnection <- function(group) {

  if (!exists('.connection', where=.GlobalEnv)) {
    .connection <<- dbConnect(MySQL(), group=group)
  } else if (class(try(dbGetQuery(.connection, "SELECT 1"))) == "try-error") {
    dbDisconnect(.connection)
    .connection <<- dbConnect(MySQL(), group=group)
  }

  return(.connection)
}

This defines a functions that checks for a connection in the global env. If one is not found it creates one. If one is found but cannot be connected to then the connection is restarted. No explicit disconnect is given so I guess the connection is just allowed to timeout eventually.