Shiny app disconnecting when uploading to database

Posit Professional & Hosted Products shinyapps.io
1

Hi,

I am a Data Scientist/Developer for a company involved in Environmental Consulting.
For one of our work practice areas, we have built a Shiny app which is hosted on shinyapp.io and reads and write to a MS SQL database. This is a multi-facetted app where users log in and quite a few their reporting tasks are automated via the app.
In one of the tabs, user uploads a .csv file and after making some changes press a "save to database" button to save the data to the database.
Very recently, many users have been complaining that upon saving to database, the app gets disconnected automatically, sort of phases out and the app is deactivated. When this happens, I reset the app on the server and after that the upload goes through.
Now this does not happen all the time, sometimes the upload passes successfully without any issue.
Another observation is that the disconnection only happens to the instance of the app on the server and never to a local instance.
Looking at the log, this is the error message that was spitted out at the time of disconnection a while ago:

2024-09-17T20:43:03.885909+00:00 shinyapps[3594707]: Warning: Error in eval: nanodbc/nanodbc.cpp:4672: 00000: TDSConnectionBroken

Doing some search here and there, I have learned that this issue can be caused by quite a few things either the code or the connection.
We have tried a few solutions, like updating the ODBC package which has a "odbc::dbWriteTable()" call in server.R but the issue is still persisting.
I just wanted to throw this out there and see if anyone has ever experienced this or any suggestion for the fix.
Can provide more info if needed.
Many thanks in advance!

2

If its a case of a database operation in the app failing because it lost its network connection, I think it might be addressable by standard try/repeat type error handling in the app. If the connection is lost, your code should try to re-establish it and try again, before giving up if unable.

consider this code sketch.

library(DBI)

# Function to establish a database connection
connect_to_db <- function() {
  dbConnect(RMySQL::MySQL(), 
            dbname = "your_db_name", 
            host = "your_host", 
            port = 3306, 
            user = "your_username", 
            password = "your_password")
}

# Function to check if the connection is still valid
is_connection_valid <- function(conn) {
  tryCatch({
    dbGetQuery(conn, "SELECT 1")
    TRUE
  }, error = function(e) {
    FALSE
  })
}

max_attempts <- 5
attempt <- 1
success <- FALSE
connection <- connect_to_db()

while(attempt <= max_attempts && !success) {
  if (!is_connection_valid(connection)) {
    message("Connection lost. Reconnecting...")
    connection <- connect_to_db()
  }
  
  tryCatch({
    # Replace this with your database operation
    result <- dbGetQuery(connection, "SELECT * FROM your_table")
    success <- TRUE
  }, error = function(e) {
    message(paste("Attempt", attempt, "failed:", e$message))
    attempt <- attempt + 1
    Sys.sleep(2)  # Wait for 2 seconds before retrying
  })
}

if(success) {
  message("Operation succeeded!")
} else {
  message("Operation failed after", max_attempts, "attempts.")
}

# Don't forget to disconnect when done
dbDisconnect(connection)
1 Like
3

I would also check if this only happens with big csv files (e.g. if the app runs out of memory when trying to handle too much data; or if the database takes too long to process big files).