[SOLVED] Cannot allocate a new connection: 16 connections already opened RMySQL

Cannot allocate a new connection: 16 connections already opened RMySQL

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

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!