How to turn connection pool fault-tolerant in Quarkus?

Question

I'm working on a quarkus (2.16.5) application with MSSqlserver and Jtds driver.

Unfortunately, although I've configured validation-query and background-validation properties, I'm still getting connections in invalid state when network goes down and recovered few moments later.

In order to improve the reliability, There is a way (config) to prevent quarkus application from using invalid connections and remove them from the pool?

Error

Caused by: java.sql.SQLException: Invalid state, the Connection object is closed.
    at net.sourceforge.jtds.jdbc.JtdsConnection.checkOpen(JtdsConnection.java:1744)
    at net.sourceforge.jtds.jdbc.JtdsConnection.prepareStatement(JtdsConnection.java:2448)
    at io.agroal.pool.wrapper.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:658)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)

DataSource configuration:

quarkus.datasource.jdbc.min-size=5
quarkus.datasource.jdbc.max-size=20
quarkus.datasource.jdbc.initial-size=5
quarkus.datasource.jdbc.acquisition-timeout=10
quarkus.datasource.jdbc.background-validation-interval=5
quarkus.datasource.jdbc.validation-query-sql=SELECT 1
quarkus.transaction-manager.default-transaction-timeout=600

quarkus.datasource.db-kind=other 
quarkus.datasource.username=sa
quarkus.datasource.password=123456
quarkus.datasource.jdbc.url=jdbc:jtds:sqlserver://sqlserver-local/MYDB;
quarkus.datasource.jdbc.driver=net.sourceforge.jtds.jdbc.Driver
quarkus.hibernate-orm.dialect=org.hibernate.dialect.SQLServerDialect

POM's dependencies

<dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-resteasy-reactive-jsonb</artifactId>
        </dependency>
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-arc</artifactId>
        </dependency>
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-resteasy-reactive</artifactId>
        </dependency>
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-elytron-security-ldap</artifactId>
        </dependency>
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-hibernate-orm</artifactId>
        </dependency>
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-narayana-jta</artifactId>
        </dependency>
        <dependency>
            <groupId>net.sourceforge.jtds</groupId>
            <artifactId>jtds</artifactId>
            <version>1.3.1</version>
        </dependency>

Answer 1

The JDBC extension for the official driver sets an ExceptionSorter to flush those connections faster. That mechanism could be use in an extension for the custom JTDS driver

<dependency>
    <groupId>io.quarkus</groupId>
    <artifactId>quarkus-jdbc-mssql</artifactId>
</dependency>

Otherwise, you can reduce the background validation interval.

Please note that there is no way to ensure 100% reliability in these scenarios, as a connection can pass validation and still fail right after that.

Answer 2

I have the following settings in production and it works when our db cluster fails sometimes:

quarkus.datasource.jdbc.max-lifetime=5M
quarkus.datasource.jdbc.idle-removal-interval=3M
quarkus.datasource.jdbc.validation-query-sql=select 1

You can also test it by yourself, by starting the application and the db via docker, then kill the db and restart the db and see if your application still works.