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.

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!

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.

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.