My Shiny app keeps getting disconnected from the server.
This is the error I keep getting on the Posit logs:
Warning: Error in eval: nanodbc/nanodbc.cpp:4616: 00000: [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure
My app works just fine locally. I really don’t think this is caused by code in my app, but if it is, then what kind of coding mistake would cause this?
Also to give full context whenever I update my app in Posit Connect it actually works for about an hour and then crashes. As a temporary solution I keep adding in comments in my code and pushing to GitLab as an excuse to update the app.
4
Here is my way used for many years in my projects to reestablish SQL Server global connection with R Shiny app.
library(shiny)
...
library(DBI)
library(odbc)
database_encoding = "latin1"
shinyServer(function(input, output, session) {
react <- reactiveValues(
my_connection_db1= 0
)
connection_react <- function () {
# react$my_connection_db1 is set at the beginning to 0 instead of NULL,
# for next connections, to know if there was an initial connection
if (!is.null(react$my_connection_db1)) {
tryCatch(
{
DBI::dbGetInfo(react$my_connection_db1)
},
error=function(e) {
print("Lost of connection my_connection_db1, try to reconnect (Error)")
react$my_connection_db1<- tryCatch(
DBI::dbConnect(odbc::odbc(),
.connection_string=my_connection_db1_odbc_string,
encoding = database_encoding
)
, error=function(e) { NULL}
)
},
warning=function(w) {
print("Lost of my_connection_db1 (Warning)")
}
)
}
return(react$my_connection_db1)
}
...
}
Used everywhere in reactive components
Mydata <- reactive({
DBI::dbGetQuery(
connection_react(),
iconv(
paste0("SELECT * FROM TOTO ... WHERE ..."
"
),"UTF-8","latin1"
)
)
})
My initial need was for switch between 2 database (archive, actual) with a similar implementation but with 2 connections.
I’m using a similar code snippet as @phili_b to ensure I’m always using a valid Postgres connection.
Note that for the Postgres driver, DBI::dbGetInfo always returns data even if the connection is closed by the server.
A workaround is to use a function that effectively requests data from the server. DBI::dbListTables is a good candidate as the result does not need to be fetched.
# Initialize first database connection
database_keepalive <- reactiveValues(
link = tryCatch(
dbConnect(RPostgres::Postgres(),
"postgres",
"localhost",
5432,
"postgres",
""),
error=function(e){
print("Error connecting to the database. Are you sure your Postgres database is running ? That your credentials are correct ?")
print("Full Stack Trace")
print(e)
NULL
}
)
)
# Function to call to ensure to always receive a valid database connection
session_db <- function() {
tryCatch({
DBI::dbListTables(database_keepalive$link)
},
error=function(e) {
print("Lost of connection database link, reconnecting...")
database_keepalive$link <- tryCatch(
dbConnect(RPostgres::Postgres(),
"postgres",
"localhost",
5432,
"postgres",
""),
error=function(e){
print("Error connecting to the database. Are you sure your Postgres database is running ? That your credentials are correct ?")
print("Full Stack Trace")
print(e)
NULL
}
)
})
return(database_keepalive$link)
}
# PostgreSQL Connection with keep alive usage
dbSendQuery(session_db(), sql("SELECT 1")