I have built an application that is meant for monitoring a dataset 24/7/7. The app is deployed on RStudio Connect and is set not to time out. The data is invalidated every five minutes which triggers a call to the database to re-pull the updated dataset. As we have rolled out the application I still have people reporting that the app is disconnecting even when a DB restart hasn't occurred. I've had no luck finding any meaningful errors in the connect logs. I have the runtime settings setup so that the app doesn't timeout, the automated DB call every five minutes should be keeping the connection timeout from being hit but I even have set this to zero as well because the app was still disconnecting eventually. You can see my current application settings, maybe there is something else to change here?
My only thought is that something is happening to the database connection and the app tries to make a database call when the DB connection has been compromised somehow. I can't find any good information on how to check if my connection is still good before making the DB call. If the connection is bad I'd like to re-connect first. I was going to try using a pooled connection instead to see if that changed anything but I couldn't find any good information outlining what happens to the pool if for instance the database is down or restarted, would my app still get disconnected?
Ideally I can get my app to remain running even during downtime and a restart and just have the app try to re-connect to the db every 5 minutes. If the DB is down I'd like to capture that and display a message to the user. Are there any good examples out there of handling this in a shiny app?
This is a really interesting setup!! Thanks for reaching out!!
I have seen strange issues like this as well with connections going "stale" or getting broken somehow (network blips and whatnot). A connection pool is definitely the route you want to go (the pool package, for instance!), and it should have connection retries and such built in to establish new connections, etc.
However, I regret to say that I have seen these issues with pool as well. Of course, not with enough frequency to get it nailed down and fixed, but I am hopeful that we will do that!!
In any case, a couple of thoughts:
wrap your query / connection in a tryCatch / loop that tries to reconnect if it encounters an error
maybe look at seeing if you can do some type of aggregated logging? You could use the logger package with some centralized logging source, or do a simple POST to a logging API. In any case, you could use the tryCatch to get a better picture of which (1) R processes, (2) Shiny sessions are failing / when / how often.
I didn't completely follow whether the "disconnection" is a Shiny type of disconnect that requires a refresh to get the page working again, or a "database" disconnect where the data just silently goes stale
If you want to get really crazy on the monitoring dealio, you could always find a way to log / make noise / alert if the data is more than X minutes old in the application. I'm not 100% sure how you would set that up, but I presume updating a reactiveVal with the current time each time the data is updated would be a good start!
I hope that is helpful! I'm really curious to see whether we can improve this situation!!
I have implemented a second version of the application with pool and the same app settings, but the pool version still seems to disconnect at the same time as the non pool version. When I say "disconnect" I am referencing the shiny app disconnect where the screen greys out and you need to reload.
Currently the application is showing a timestamp when the data was last updated. The app uses autoInvalidate to invalidate the data every five minutes and re-query the database to see if there's new data to show. So I can already see "how old" the data is. From what I can tell the data is still updating regularly when the app disconnects.
I am working changing the app to a one page app so I can at least try to reconnect to the app with the right inputs already selected as described here. That would simplify the issue slightly for the end user.
I will look at the tryCatch loop to see if I can try a db reconnect if there's an issue. If you have an example of how to set that up I haven't found a good example of doing that. For instance if the database is down for an hour, can I try reconnecting every 5 minutes and leave a message on my app page that says, "The database is currently unavailable, will retry a connection in five minutes." So that the app page stays up and "available" without disconnecting.
I will also checkout the logger package and see if I can potentially capture some more useful information.
I'll post again when I have more info, thank you @cole for some ideas!
I will add, I have been watching the console in developer tools. When a disconnect happens I see the following errors. "Session no longer exists on the server" is the message I'm most focused on, it seems the session is being removed potentially. I found some related posts but most referred to using a proxy server which we are not, we're using RStudio Connect.
Interesting!! Thanks for the extra background there! Do you have a load balancer or other proxy in front of Connect? Is it possible that the load balancer could have a connection timeout that restricts how long sessions are allowed to exist for?
Hi @cole - we do have a load balancer in front of connect so that was another thing I was trying to research, if there are settings specifically within AWS that may be limiting it.
In the meantime, implementing the auto reconnect has worked and my app has been up and running as expected all weekend. I think for now that works as a solution but I would still like to make sure to handle any other things that could be causing disconnections if possible. I'll still look at some additional logging. I'll post additional information here.
Glad to hear the auto-reconnect has worked! Apologies for not yet providing an example of the tryCatch with database reconnection and notification. I like that idea a lot! I suspect it is quite tricky to implement in practice, although perhaps pool has an appropriate hook to make it possible.
I definitely hope to take a look more deeply! And please do share what you find! I am hopeful this thread can be a valuable source of information for others trying to implement this pattern!
Of course, not with enough frequency to get it nailed down and fixed, but I am hopeful that we will do that!!