quarkus.datasource.validation-query-sql

[famod]

Hi, I think this is mainly for @Sanne @yrodiere @gsmet and/or maybe @barreiro:

does it make sense to set e.g. SELECT 1 for quarkus.datasource.validation-query-sql these days?
I stumbled upon https://stackoverflow.com/a/35270023 and now I'm unsure whether it makes sense at all.

Related: #7275

Thanks!

PS: It's MariaDB in my case.

[Sanne]

Good point, but I'm not sure all JDBC drivers support JDBC v4 yet :(

[famod]

Yeah, maybe not. But https://mariadb.com/kb/en/about-mariadb-connector-j/ does, so it doesn't make sense to set this config property, right?

Thing is that I was asked by Ops to make our app more "resilient" in terms of database connection dropouts due to auto-renwal of HashiCorp Consul sidecar TLS certificates (something they have little control over).
Currently I don't see what we can improve, except elaborate custom compensations (e.g. when the tx dies because of the above and we have already called another system).

[barreiro]

The only way I see to improve it is to use quarkus.datasource.jdbc.foreground-validation-interval. Setting a really low value is a trade-off in performance as validation will be executed much more often.

At the end of the day, there can always be connections that are invalid even if validation has just been successfully performed.

Can you also share the SQLException you get in that case, in particular the SQL error code, to make sure that Agroal is flushing those connections as soon as possible?

[famod]

Can you also share the SQLException you get in that case, in particular the SQL error code, to make sure that Agroal is flushing those connections as soon as possible?

I'm seeing (not necessarily in this order):

Caused by: java.sql.SQLException: Deferred enlistment not supported
	at io.agroal.pool.ConnectionHandler.verifyEnlistment(ConnectionHandler.java:359)
	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)

2021-11-19 13:53:45,395 INFO  [io.sma.health] (executor-thread-15747) SRHCK01001: Reporting health down status: {"status":"DOWN","checks":[{"name":"Database connections health check","status":"DOWN","data":{"default":"validation check failed for the default DataSource"}}]}

2021-11-19 13:53:50,004 WARN  [com.arj.ats.jta] (executor-thread-15745) ARJUNA016061: TransactionImple.enlistResource - XAResource.start returned: XAException.XAER_RMERR for < formatId=131077, gtrid_length=35, bqual_length=36, tx_uid=0:ffffac1a40a3:9ff2:61895af6:13502a, node_name=quarkus, branch_uid=0:ffffac1a40a3:9ff2:61895af6:13510e, subordinatenodename=null, eis_name=0 >: javax.transaction.xa.XAException: Error trying to start local transaction: (conn=242815) (conn=242815) Connection is closed
	at io.agroal.narayana.LocalXAResource.xaException(LocalXAResource.java:140)
	at io.agroal.narayana.LocalXAResource.xaException(LocalXAResource.java:134)
	at io.agroal.narayana.LocalXAResource.start(LocalXAResource.java:50)
	at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:661)
	at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:422)
	at io.agroal.narayana.NarayanaTransactionIntegration.associate(NarayanaTransactionIntegration.java:92)
	at io.agroal.pool.ConnectionPool.getConnection(ConnectionPool.java:244)
	at io.agroal.pool.DataSource.getConnection(DataSource.java:81)
	at io.quarkus.hibernate.orm.runtime.customized.QuarkusConnectionProvider.getConnection(QuarkusConnectionProvider.java:23)
	at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:38)
	at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:108)
	at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:138)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:50)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
	at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:151)
	at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2122)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2059)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037)
	at org.hibernate.loader.Loader.scroll(Loader.java:2945)

[barreiro]

@famod In Quarkus, the default is to use connetion.isValid(0) for validation[1]. validation-query-sql overrides that.

[1] https://github.com/quarkusio/quarkus/blob/main/extensions/agroal/runtime/src/main/java/io/quarkus/agroal/runtime/DataSources.java#L275

[famod]

@famod In Quarkus, the default is to use connetion.isValid(0) for validation[1].

So it doesn't time out and waits "forever" (unless a "hard" error occurs)?

Would it make sense to introduce a Quarkus property to set that timeout value? E.g. so that io.agroal.api.configuration.AgroalConnectionPoolConfiguration.ConnectionValidator.defaultValidatorWithTimeout(int) is used?

validation-query-sql overrides that.

Ok. Looking at https://github.com/quarkusio/quarkus/blob/main/extensions/agroal/runtime/src/main/java/io/quarkus/agroal/runtime/DataSources.java#L285-L300 it seems that in this case there is no such timeout?

[famod]

@barreiro friendly reminder ⬆️ 🙂

[famod]

@barreiro another friendly reminder 🙂

[PhilippKemkes]

An option to set defaultValidatorWithTimeout sounds very useful. Are there any plans to implement this?

[yrodiere]

#47233 ?