I am using the pool package to connect a Shiny application to a fairly large SQLite database (3Gb, 70M rows).
I create a pool using:
pool <- dbPool(
drv = RSQLite::SQLite(),
dbname = "mydb.db")
Everything works perfectly locally, but when I put it on my server (I am using a DigitalOcean droplet running Shiny server), the pool expires very quickly after ~15 seconds, whether I am active or not on the app.
In the logs I see
Error in pool$fetch: This pool is no longer valid. Cannot fetch new objects.
I have tried changing the idleTimeout and minSize parameters when creating the pool, to no avail.
How can I prevent this? Is there a way I can check whether the pool is still valid and if not reconnect to the DB?
Also, it would be good if someone could give some insights on why this could be happening.
Just in case someone else bumps into this problem, the way I have solved the issue is to check the valid attribute of the pool connection. So I can prepend queries with something like:
if (!conn$valid) # pool has expired
connect_to_db() # This re-connects to the DB
<do query>
This seems to have solved the issue