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 the my RShiny work on browser continously i got an error like Cannot allocate a new connection: 16 connections already opened. How can i overcome this error or Rshine only expecting 16 users at a time?. I have got another stack post here RStudio Shiny Error mysqlNewConnection maxinum of 16 connections but the explanaation was not clear on the above url.

Answers


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')

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

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

  # close db connection
  dbDisconnect(DB)

  # return the dataframe
  return(result)
}

Hope that helps!


Need Your Help

Creating an empty file in Ruby: “touch” equivalent?

ruby file

What is the best way to create an empty file in Ruby?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.