It seems to me that
Pool should have an option to always validate objects on
fetch(), regardless of the
lastValidated time. Otherwise, the
Pool will continue to return dead connections until enough time has elapsed such that the validation can finally be triggered on
fetch(). Consider the following reprex:
library(pool) library(RMariaDB) pool <- dbPool(MariaDB(), host = "127.0.0.1", username = "root", password = "", dbname = "test") # Set the `wait_timeout` to 5 seconds for this session invisible(dbExecute(pool, "SET SESSION wait_timeout = 5")) # Wait longer than `wait_timeout` to trigger a disconnect Sys.sleep(6) # Fails dbExecute(pool, "SELECT 1") #> Error in result_create(conn@ptr, statement, is_statement): Lost connection to MySQL server during query  pool$validationInterval <- 0 # Since `validationInterval` is now 0, `onValidate` will be # called and catch the dead connection dbExecute(pool, "SELECT 1") #> Warning: It wasn't possible to activate and/or validate the object. Trying #> again with a new object. #>  0 poolClose(pool)
Created on 2019-05-30 by the reprex package (v0.3.0)
Pool is created with the default settings, there will always be at least 1 connection in the pool, with a
validationInterval of 10 minutes. After the
wait_timeout expires for the connection in the pool, the MySQL instance will close the connection. Now, there is a dead connection in the pool, which will remain there until 10 minutes since the time of the connection's
lastValidated value has elapsed.
As shown, setting the
0 will ensure that
onValidate() gets triggered in the
fetch() call, which consequently causes the dead connection to be destroyed and replaced with a new one. This is not included as a proposal for a viable solution, but rather as a demonstration that validating objects as they are checked out, regardless of the
lastValidated time, ensures that dead connections can never be checked out and are removed from the pool.
I realize that the documentation for
validationInterval states that it is
The minimum number of seconds that
poolwill wait before running a validation check on the next checked out object. By not necessarily validating every checked out object, there can be substantial performance gains (especially if the interval between checking out new objects is very small).
However, if this value is set to
0 for the entire
Pool, then it will also be used as the
delay value in the call to
release(), which means that the validation check will constantly be running for free objects.