3

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
719016
  • 9,922
  • 20
  • 85
  • 158
  • 1
    Inside a reactive function, connect ,get data and disconnect. So this function will be called when there an event(it uses lazy loading mechanism). This is how I have configured, not sure its a good method or not. – on_the_shores_of_linux_sea Jul 09 '14 at 10:02

1 Answers1

8

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.

jdharrison
  • 30,085
  • 4
  • 77
  • 89
  • it seems to work if I put it outside `shinyServer`, although I guess there is no way to know how many connections is still leaving open. – 719016 Jul 09 '14 at 13:24
  • 1
    It has one connection. `global.R` and whats outside `shinyServer` are across clients – jdharrison Jul 09 '14 at 13:29