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.

Brandi

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. – Hooded

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

Answer 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!

Cheer

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. – Brandi

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

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

Nice one @Marek. Just edited the answer accordingly! – Cheer

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. – Cleaner

works perfectly, thanks, but whats the meaning of the + in this code for(con in all_cons) + dbDisconnect(con)? – Orgulous

Answer 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.

Zymogen

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

Answer 3

dbDisconnect() was not working in my case. So I had stopped MySQL server from terminal and again started using

sudo service mysql stop

sudo service mysql start

Then I ran the code with using dbDisconnect() now its working for me.

Crosspollination