Wildfly - Background Validation not working for Data sources during reconnect using JPA-Hibernate

Question

Following is exception:

2020-12-15 05:15:00,338 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 4) SQL Error: 0, SQLState: 08S01
2020-12-15 05:15:00,338 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 4) I/O Error: Connection reset
.
.
Caused by: org.hibernate.exception.JDBCConnectionException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:115)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2167)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1930)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892)
    at org.hibernate.loader.Loader.doQuery(Loader.java:937)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340)
    at org.hibernate.loader.Loader.doList(Loader.java:2689)
    at org.hibernate.loader.Loader.doList(Loader.java:2672)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506)
    at org.hibernate.loader.Loader.list(Loader.java:2501)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:504)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:395)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1507)
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1537)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505)
    ... 192 more
Caused by: java.sql.SQLException: I/O Error: Connection reset
    at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1093)
    at net.sourceforge.jtds.jdbc.TdsCore.microsoftPrepare(TdsCore.java:1219)
    at net.sourceforge.jtds.jdbc.JtdsConnection.prepareSQL(JtdsConnection.java:708)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1028)
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
    ... 207 more
Caused by: java.net.SocketException: Connection reset
    at java.net.SocketInputStream.read(SocketInputStream.java:210)
    at java.net.SocketInputStream.read(SocketInputStream.java:141)
    at java.io.DataInputStream.readFully(DataInputStream.java:195)
    at java.io.DataInputStream.readFully(DataInputStream.java:169)
    at net.sourceforge.jtds.jdbc.SharedSocket.readPacket(SharedSocket.java:850)
    at net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket(SharedSocket.java:731)
    at net.sourceforge.jtds.jdbc.ResponseStream.getPacket(ResponseStream.java:477)
    at net.sourceforge.jtds.jdbc.ResponseStream.read(ResponseStream.java:114)
    at net.sourceforge.jtds.jdbc.ResponseStream.peek(ResponseStream.java:99)
    at net.sourceforge.jtds.jdbc.TdsCore.wait(TdsCore.java:4127)
    at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1086)
    ... 212 more

Earlier we had this frequently.

Following is solution which I found online and implemented for last one month:

                <validation>
                    <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker>
                    <background-validation>true</background-validation>
                    <background-validation-millis>120000</background-validation-millis>
                </validation>

But it is found after 5 days or after 7 days.

The error appears again. (Someone suggest to keep validateopnMatch true but that is not fruitful)

After restart or redeploy integration, Error is gone for few days.

So it worked for somedays but it is not full proof solution

Can someone suggest what changes can be done in code to handle this? If someone can suggest in datasource then also is fine. But it appears Datasource solution is not full proof?

Snippet:

<persistence-unit name="rmPU" transaction-type="JTA">
    <jta-data-source>java:/datasources/edgermDS</jta-data-source>
</persistence-unit>

Snippet Use:

    @PersistenceContext(unitName="rmPU")
    private EntityManager entityManagerrm;
    
    public List<CommonAccount> getLatestAccounts(Date lastExportTime) {
        if(!entityManagerrm.isOpen()) {
            log.warning("Persistence:getLatestAccounts: Connection is Closed");
        }
        return entityManagerrm.createNamedQuery("Account.findCreatedAfter", CommonAccount.class).setParameter("lastExportTime", lastExportTime, TemporalType.TIMESTAMP)
                .getResultList();
    }

Answer 1

You can configure your data source to validate the connection everytime before handling it over to the application:

            <datasource ...>
                ...
                <validation>
                    <validate-on-match>true</validate-on-match>
                </validation>
            </datasource>