Connection pool unable to recover after database connectivity loss

[manish-in-java]

We are using Bitronix 2.1.4 with MySQL connector 5.1.39 and Spring Data JPA 1.11.4.RELEASE. Once in a while the app server loses network connectivity to the database server. Once this happens, all connections in the Bitronix pool quickly run into errors. However, they continue to throw errors even after the network connectivity is restored. The following stacktrace appears in the logs:

Caused by: java.sql.SQLException: error enlisting a JdbcConnectionHandle of a JdbcPooledConnection from datasource dataSource in state ACCESSIBLE with usage count 1 wrapping com.mysql.jdbc.jdbc2.optional.JDBC4SuspendableXAConnection@65e0ae83 on com.mysql.jdbc.jdbc2.optional.JDBC4ConnectionWrapper@6d4e71b1
    at bitronix.tm.resource.jdbc.JdbcConnectionHandle.enlistResource(JdbcConnectionHandle.java:87)
    at bitronix.tm.resource.jdbc.JdbcConnectionHandle.prepareStatement(JdbcConnectionHandle.java:242)
    at sun.reflect.GeneratedMethodAccessor178.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at bitronix.tm.resource.jdbc.BaseProxyHandlerClass.invoke(BaseProxyHandlerClass.java:64)
    at com.sun.proxy.$Proxy32.prepareStatement(Unknown Source)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)
    ... 133 common frames omitted
Caused by: bitronix.tm.internal.BitronixSystemException: cannot enlist an XAResourceHolderState with uniqueName=dataSource XAResource=com.mysql.jdbc.jdbc2.optional.JDBC4SuspendableXAConnection@65e0ae83 with XID a Bitronix XID [626974726F6E69782D3230313730373234303332303339000000000EA9F59A00001FCC : 626974726F6E69782D3230313730373234303332303339000000000EA9F59C00001FCD], error=XAER_RMFAIL
    at bitronix.tm.BitronixTransaction.enlistResource(BitronixTransaction.java:139)
    at bitronix.tm.resource.common.TransactionContextHelper.enlistInCurrentTransaction(TransactionContextHelper.java:69)
    at bitronix.tm.resource.jdbc.JdbcConnectionHandle.enlistResource(JdbcConnectionHandle.java:85)
    ... 141 common frames omitted
Caused by: com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ROLLBACK ONLY state
    at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:581)
    at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:566)
    at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.start(MysqlXAConnection.java:507)
    at com.mysql.jdbc.jdbc2.optional.SuspendableXAConnection.start(SuspendableXAConnection.java:172)
    at bitronix.tm.internal.XAResourceHolderState.start(XAResourceHolderState.java:220)
    at bitronix.tm.internal.XAResourceManager.enlist(XAResourceManager.java:111)
    at bitronix.tm.BitronixTransaction.enlistResource(BitronixTransaction.java:130)
    ... 143 common frames omitted
Caused by: java.sql.SQLException: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ROLLBACK ONLY state
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:963)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2503)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:839)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:739)
    at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:560)

The JDBC URL is jdbc:mysql://db.domain.com:3306/domain?pinGlobalTxToPhysicalConnection=true. The data source class name is com.mysql.jdbc.jdbc2.optional.MysqlXADataSource.

[lorban]

JDBC connections usually do not automatically heal from network failures, so the connection pool must figure out if a connection is still valid before handing it over.

BTM provides two ways to recover from network failures: enableJdbc4ConnectionTest and testQuery. The former uses the JDBC 4 validation mechanism to test connections and should be preferred, the latter can be used as a fallback when the former doesn't work for whatever reason.