While doing some tests for the recently released generic JCA adapter which is capable of binding remote calls to microservices (as well as other things) into JTA transactions, I discovered a bug in Mysql 5.6 which has been around for nearly ten years.
The test scenario was a crash after the “prepare” phase of the XA transaction, after both the database and the generic connector vote to commit the transaction. After crashing the database or the application server, the transaction manager will try to recover and commit the transaction in the database. But while testing the crashes with Mysql 5.6 rather than Mysql 5.7, I was having the problem that the database never actually committed the transaction, meaning that the system as a whole was in an inconsistent state. There were absolutely no problems with the generic connector, just the database. The application server continuously logged that there was an incomplete transaction but was unable to complete the transaction in the database. During the simulated database crash, the application returned a HeuristicMixedException to indicate to the user that something is not and will not ever be consistent. The error logged by JBoss was:
...WARN [com.arjuna.ats.jta] (Periodic Recovery) ARJUNA016037: Could not find new XAResource to use for recovering non-serializable XAResource XAResourceRecord < resource:null, txid: <...eis_name=unknown eis name >, heuristic: TwoPhaseOutcome.FINISH_OK ...>
I spent time debugging in the Mysql driver code but eventually came across MySQL Bug #12161 which has only just been closed after being open for nearly 10 years! It is clearly a point where the database is not two phase commit compatible, because remember what Wikipedia states: “… Log records, which are typically slow to generate but survive failures, are used by the protocol’s recovery procedures.” In the case of Mysql 5.6 and previous versions, its log records do not survive failures, as documented in the bug report. Additionally, section 7.6.2.8 of the JCA spec 1.6 says we must not erase knowledge of the transaction branch until commit or rollback is called, again which Mysql is not adhering to.
Unfortunately the fix is not yet in the 5.6 GA version of Mysql, rather only available in the 5.7 DMR version. But that is due to become a GA release soon, and other databases like Postgres and Oracle do not have this issue. The problem is also clearly described in the JBoss manual (RTFM!), which also provides tips on getting XA recovery working with Oracle where special access needs to be granted to the appropriate user (which has caught us out in the past). More information on this problem is located here. Further tests with the H2 database sadly showed that it too does not support recovery of XA transactions.
| Reference: | Mysql versions prior to 5.7 do not fully support two phase commit from our JCG partner Ant Kutschera at the The Kitchen in the Zoo blog. |
