Cannot allocate a new connection: 16 connections already opened RMySQL

Question

I am very new to shiny and R but using shiny I am trying to connect to a database fetch the data from there. When I try to access my RShiny work on browser continuously I got an error like Cannot allocate a new connection: 16 connections already opened. How can I overcome this error or RShiny only expecting 16 users at a time? I have got another stack post here RStudio Shiny Error mysqlNewConnection maximum of 16 connections but the explanation was not clear on the above URL.

Solution 1

Maybe you open a new DB connection with obj <- dbConnect(...) every time you send a query in your code. You can simply call dbDisconnect(obj) on the object you created to kill the respective connection everytime after your query executed.

Also you can use this function kill all open connections at once:

library(RMySQL)  

killDbConnections <- function () {

  all_cons <- dbListConnections(MySQL())

  print(all_cons)

  for(con in all_cons)
    +  dbDisconnect(con)

  print(paste(length(all_cons), " connections killed."))

}

I'd recommed to write a small function outside shiny that handles the whole opening and closing thing:

library(RMySQL)

sqlQuery <- function (query) {

  # creating DB connection object with RMysql package
  DB <- dbConnect(MySQL(), user="youruser", password='yourpassword', dbname='yourdb', host='192.168.178.1')

  # close db connection after function call exits
  on.exit(dbDisconnect(DB))

  # send Query to btain result set
  rs <- dbSendQuery(DB, query)

  # get elements from result sets and convert to dataframe
  result <- fetch(rs, -1)

  # return the dataframe
  return(result)
}

Hope that helps!

Solution 2

Fast solution

If you get this issue, you should first run the following code interactively to disconnect all your connexions to the MySQL database:

lapply(dbListConnections(MySQL()), dbDisconnect)

(Note that you can replace MySQL() by another DBI Driver, if you use another database management system).

Faster solution

Just restart R session : command/ctrl + shift + F10 on RStudio

How to avoid this issue

You need to tell shiny how to disconnect properly. That part depends on the use case. If you start a connexion each time you start shiny, you could add inside server.ui:

session$onSessionEnded(function(){
    dbDisconnect(con)
}

Where con is your connexion. If you start a connexion each time you run a query, you must disconnect immediately after the query has run.

You should also take a look at the pool package which is suggested by the shiny team to manage connexions. There is also a very helpful section on Shiny Articles on database.

Comments

[nrussell]

Do you need 16+ connections open at the same time, or are you looking for a way to close unused connections? If it is the former, this blog post seems to provide a solution.

[Pork Chop]

Open one connection before shinyServer and use this one connection all the time.

[anoop]

Thanks @ThankGoat. This was really helpful and fixed the issue. But can you please explain from where i got the proper documentation for all these.

[ThankGoat]

It's all in the documentation of the RMySQL package. Just type ?RMySQL in the console and start reading ;)

[Marek]

I strongly advice use of on.exit(dbDisconnect(DB)) just after DB <- dbConnect. Otherwise query syntax errors will leave connection open.

[ThankGoat]

Nice one @Marek. Just edited the answer accordingly!

[Paul Rougieux]

The killDBconnections function is nice. In case you want a quick fix. This solution proposed in a comment here will also disconnects all MySQL connections: RMySQL::dbDisconnect(RMySQL::dbListConnections(RMySQL::MySQL‌​())[[1]]). To disconnect all connections, run it interactively several times.

[Jealie]

The "Faster" solution is actually very slow if you have a large dataset loaded in memory...