RStudio Shiny Error mysqlNewConnection maximum of 16 connections

mysql, r, shiny

Solution

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.

Problem

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?

Original source