Shinyapps error: Failed to connect to the database, too many connections

shinyappsio
pool

#1

My application works localy. When I deploy it on shinyapps.io it seems to work but sometimes I get an error:

It can't connect to the database, too many connections. Then if I try in a few minutes it works. Then I try again and it doesn't work. My code looks like this

library(shiny)
library(shinydashboard)
library(formattable)
library(dplyr)

library(DBI)
library(pool)

pool <- dbPool(
  drv = RMySQL::MySQL(),
  dbname = "my_db_name",
  host = "my_db_host",
  username = "my_username",
  password = "my_password",
  port = 3306
)

onStop(function() {
  poolClose(pool)
})

ui <- dashboardPage(
  # Code here
)

server <- function(input, output) {
  
  data_all = pool %>% 
    tbl('table_name') %>% 
    as.data.frame()
  
  # Code here
}

# Run the application 
shinyApp(ui = ui, server = server)

As I understand, when I deploy an app pool object is created. When new users opens an app it gives him a connection (creates new if required). My app is used only by me so I don't understand this error. Can someone help me explain why is this happening and how to fix it?


#2

Hi @ebartos! Welcome!

I’m afraid the error messages in your screenshot are impossible to read. Can you please copy and paste the text instead? When asking questions here, we prefer that people only use screenshots for things that can’t be shown any other way (such as user interface problems, or other graphical issues).


#3

Thank you jcblum.

The error part of the message says:
"Error in value[3L]:
Failed to connect to database: Error: Too many connections
Calls: local ... tryCatch -> tryCatchList -> tryCatchOne ->
Execution halted"


#4

Any info about this error? Thank you!


#5

Hi,

Have you tried to set the maxSize of the pool to match you MySQL configuration?

Regards,

jm


#6

I didn't. I will try that. From MySQL configuration i saw that variable max_connections is set to 151, so I will try to set the maxSize of pool element to that value. But it seems odd that I have that large number of connections.

Thanks I will try to do as you said!


#7

Let us know your findings.

If you expect some minimum usage of the apps and you want some low latency on the first connections, you should also set minSize. That way the pool is created with some waiting connections.

Moreover, I guess you can try some to testload your shiny app. So that you can check if you can reproduce the error from your local machine. See:

Regards,

jm