BTM

Handle MySQL XA semantics

Details

  • Type: Bug Bug
  • Status: Closed Closed
  • Priority: Major Major
  • Resolution: Won't Fix
  • Affects Version/s: 1.2
  • Fix Version/s: 1.2
  • Labels:
    None
  • Environment:
    JDK 1.5, all operating systems
  • Number of attachments :
    1

Description

I am encountering an issue when trying to use Hibernate, Bitronix, and MySQL. The problem can probably be blamed on MySQL, but since MySQL has not seen fit to fix the bug in over a two years it is reasonable for transaction managers such as Bitronix to provide an option to handle the MySQL XA sematics.

The essence of the problem is summarized here: http://bugs.mysql.com/bug.php?id=17343

mysql> xa start '1234', '56';
Query OK, 0 rows affected (0.01 sec)

mysql> xa end '1234', '56';
Query OK, 0 rows affected (0.00 sec)

mysql> xa start '1234', '78';
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state

In MySQL, a transaction with the same XID cannot be "restarted" on the same connection. Attached is a log of a persistence operation in Hibernate, using Bitronix TM to manage transactions against a MySQL InnoDB database. Because of the use of table-generated IDs in Hibernate, Hibernate suspends and resumes the transaction in order to generate and update IDs in an independently managed table. After these series of suspend and resume calls, Bitronix attempts to restart the original global transaction – which it previously ended – and at this point encounters the above error condition from MySQL.

What is needed is a flag for Bitronix to tell it not to (re)start a transaction using an identical XID but to always use new XIDs, if this is possible.

In the attached log, you can see at line 40 the original transaction being started:

08-05-11 23:48:13,250 [XAResourceHolderState ] [btpool0-2 ] DEBUG - starting an XAResourceHolderState with uniqueName=ziptie-ds XAResource=com.mysql.jdbc.jdbc2.optional.MysqlXAConnection@d4e99c with XID a Bitronix XID [7A69707469652D3100000119D87408B900000036 : 7A69707469652D3100000119D87408C100000038]
Sun May 11 14:48:13 GMT+00:00 2008 DEBUG: Executing XA statement: XA START 0x7a69707469652d3100000119d87408b900000036,0x7a69707469652d3100000119d87408c100000038,0x42746e78

Followed by it's ending, due to suspension (line 209-212):

08-05-11 23:48:13,424 [itronixTransactionManager] [btpool0-2 ] DEBUG - suspending transaction a Bitronix Transaction with GTRID [7A69707469652D3100000119D87408B900000036], status=ACTIVE, 1 resource(s) enlisted (started Sun May 11 14:48:13 GMT+00:00 2008)
08-05-11 23:48:13,431 [XAResourceManager ] [btpool0-2 ] DEBUG - suspending an XAResourceHolderState with uniqueName=ziptie-ds XAResource=com.mysql.jdbc.jdbc2.optional.MysqlXAConnection@d4e99c (started) with XID a Bitronix XID [7A69707469652D3100000119D87408B900000036 : 7A69707469652D3100000119D87408C100000038]
08-05-11 23:48:13,431 [XAResourceHolderState ] [btpool0-2 ] DEBUG - ending an XAResourceHolderState with uniqueName=ziptie-ds XAResource=com.mysql.jdbc.jdbc2.optional.MysqlXAConnection@d4e99c (started) with XID a Bitronix XID [7A69707469652D3100000119D87408B900000036 : 7A69707469652D3100000119D87408C100000038]
Sun May 11 14:48:13 GMT+00:00 2008 DEBUG: Executing XA statement: XA END 0x7a69707469652d3100000119d87408b900000036,0x7a69707469652d3100000119d87408c100000038,0x42746e78

And finally the attempt to restart it (line 1200-):

08-05-11 23:48:14,011 [ResourceRegistrar ] [btpool0-2 ] DEBUG - XAResource com.mysql.jdbc.jdbc2.optional.MysqlXAConnection@d4e99c belongs to a JdbcPooledConnection from datasource ziptie-ds in state ACCESSIBLE wrapping com.mysql.jdbc.jdbc2.optional.MysqlXAConnection@d4e99c that itself belongs to a PoolingDataSource containing an XAPool of resource ziptie-ds with 2 connection(s) (1 still available)
08-05-11 23:48:14,011 [XAResourceManager ] [btpool0-2 ] DEBUG - resource already enlisted but has been ended: an XAResourceHolderState with uniqueName=ziptie-ds XAResource=com.mysql.jdbc.jdbc2.optional.MysqlXAConnection@d4e99c (ended) with XID a Bitronix XID [7A69707469652D3100000119D87408B900000036 : 7A69707469652D3100000119D87408C100000038]
08-05-11 23:48:14,011 [XAResourceManager ] [btpool0-2 ] DEBUG - join disabled on resource an XAResourceHolderState with uniqueName=ziptie-ds XAResource=com.mysql.jdbc.jdbc2.optional.MysqlXAConnection@d4e99c with XID null
08-05-11 23:48:14,012 [XAResourceManager ] [btpool0-2 ] DEBUG - creating new branch with a Bitronix XID [7A69707469652D3100000119D87408B900000036 : 7A69707469652D3100000119D8740BBC00000078]
08-05-11 23:48:14,012 [XAResourceHolderState ] [btpool0-2 ] DEBUG - assigning <a Bitronix XID [7A69707469652D3100000119D87408B900000036 : 7A69707469652D3100000119D8740BBC00000078]> to <an XAResourceHolderState with uniqueName=ziptie-ds XAResource=com.mysql.jdbc.jdbc2.optional.MysqlXAConnection@d4e99c with XID null>
08-05-11 23:48:14,012 [XAResourceHolderState ] [btpool0-2 ] DEBUG - starting an XAResourceHolderState with uniqueName=ziptie-ds XAResource=com.mysql.jdbc.jdbc2.optional.MysqlXAConnection@d4e99c with XID a Bitronix XID [7A69707469652D3100000119D87408B900000036 : 7A69707469652D3100000119D8740BBC00000078]
Sun May 11 14:48:14 GMT+00:00 2008 DEBUG: Executing XA statement: XA START 0x7a69707469652d3100000119d87408b900000036,0x7a69707469652d3100000119d8740bbc00000078,0x42746e78
08-05-11 23:48:14,033 [JDBCExceptionReporter ] [btpool0-2 ] DEBUG - could not initialize a collection: org.ziptie.credentials.CredentialConfig.credentialSets#1 [select credential0_.fkCredentialConfigId as fkCreden4_1_, credential0_.id as id1_, credential0_.id as id3_0_, credential0_.credSetName as credSetN2_3_0_, credential0_.priority as priority3_0_ from cred_set credential0_ where credential0_.fkCredentialConfigId=?]
java.sql.SQLException: error enlisting a JdbcConnectionHandle of a JdbcPooledConnection from datasource ziptie-ds in state ACCESSIBLE wrapping com.mysql.jdbc.jdbc2.optional.MysqlXAConnection@d4e99c on com.mysql.jdbc.jdbc2.optional.ConnectionWrapper@a943af
at bitronix.tm.resource.jdbc.JdbcConnectionHandle.enlistResource(JdbcConnectionHandle.java:61)
at bitronix.tm.resource.jdbc.JdbcConnectionHandle.prepareStatement(JdbcConnectionHandle.java:194)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.loadCollection(Loader.java:1994)
at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:36)
at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:565)
at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:63)
at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1716)
at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:344)
at org.hibernate.collection.PersistentSet.clear(PersistentSet.java:299)
at org.hibernate.type.CollectionType.replaceElements(CollectionType.java:479)
at org.hibernate.type.CollectionType.replace(CollectionType.java:552)
at org.hibernate.type.TypeFactory.replace(TypeFactory.java:482)
at org.hibernate.event.def.DefaultMergeEventListener.copyValues(DefaultMergeEventListener.java:340)
at org.hibernate.event.def.DefaultMergeEventListener.entityIsDetached(DefaultMergeEventListener.java:267)
at org.hibernate.event.def.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:120)
at org.hibernate.event.def.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:53)
at org.hibernate.impl.SessionImpl.fireMerge(SessionImpl.java:677)
at org.hibernate.impl.SessionImpl.merge(SessionImpl.java:661)
at org.hibernate.impl.SessionImpl.merge(SessionImpl.java:665)
at org.ziptie.provider.credentials.DatabaseCredentialsPersister.saveOrUpdate(DatabaseCredentialsPersister.java:479)
at org.ziptie.provider.credentials.DatabaseCredentialsPersister.saveDefaultCredentialConfig(DatabaseCredentialsPersister.java:334)
at org.ziptie.credentials.AbstractCredentialsManager.saveDefaultCredentialConfig(AbstractCredentialsManager.java:121)
at org.ziptie.provider.credentials.CredentialsProvider.saveDefaultCredentialConfig(CredentialsProvider.java:106)
at org.ziptie.provider.credentials.CredentialsDelegate.saveDefaultCredentialConfig(CredentialsDelegate.java:100)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.sun.xml.ws.api.server.InstanceResolver$1.invoke(InstanceResolver.java:246)
at com.sun.xml.ws.server.InvokerTube$2.invoke(InvokerTube.java:146)
at com.sun.xml.ws.server.sei.EndpointMethodHandler.invoke(EndpointMethodHandler.java:257)
at com.sun.xml.ws.server.sei.SEIInvokerTube.processRequest(SEIInvokerTube.java:93)
at com.sun.xml.ws.api.pipe.Fiber.__doRun(Fiber.java:595)
at com.sun.xml.ws.api.pipe.Fiber._doRun(Fiber.java:554)
at com.sun.xml.ws.api.pipe.Fiber.doRun(Fiber.java:539)
at com.sun.xml.ws.api.pipe.Fiber.runSync(Fiber.java:436)
at com.sun.xml.ws.server.WSEndpointImpl$2.process(WSEndpointImpl.java:243)
at com.sun.xml.ws.transport.http.HttpAdapter$HttpToolkit.handle(HttpAdapter.java:444)
at com.sun.xml.ws.transport.http.HttpAdapter.handle(HttpAdapter.java:244)
at com.sun.xml.ws.transport.http.servlet.ServletAdapter.handle(ServletAdapter.java:135)
at com.sun.xml.ws.transport.http.servlet.WSServletDelegate.doGet(WSServletDelegate.java:129)
at com.sun.xml.ws.transport.http.servlet.WSServletDelegate.doPost(WSServletDelegate.java:160)
at org.ziptie.zap.metro.ZwsServlet.doPost(ZwsServlet.java:227)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
at org.ziptie.zap.security.ZHttpContext.doFilter(ZHttpContext.java:80)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at org.ziptie.zap.metro.ZThreadContextFilter.doFilter(ZThreadContextFilter.java:34)
at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
at org.ops4j.pax.web.service.internal.HttpServiceServletHandler.handle(HttpServiceServletHandler.java:56)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:722)
at org.ops4j.pax.web.service.internal.HttpServiceContext.handle(HttpServiceContext.java:107)
at org.ops4j.pax.web.service.internal.JettyServerHandlerCollection.handle(JettyServerHandlerCollection.java:64)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:139)
at org.mortbay.jetty.Server.handle(Server.java:324)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:842)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:648)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
at org.mortbay.jetty.bio.SocketConnector$Connection.run(SocketConnector.java:228)
at org.mortbay.jetty.security.SslSocketConnector$SslConnection.run(SslSocketConnector.java:620)
at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
Caused by: bitronix.tm.internal.BitronixSystemException: cannot enlist an XAResourceHolderState with uniqueName=ziptie-ds XAResource=com.mysql.jdbc.jdbc2.optional.MysqlXAConnection@d4e99c with XID a Bitronix XID [7A69707469652D3100000119D87408B900000036 : 7A69707469652D3100000119D8740BBC00000078], error=XAER_RMFAIL
at bitronix.tm.BitronixTransaction.enlistResource(BitronixTransaction.java:68)
at bitronix.tm.resource.common.TransactionContextHelper.enlistInCurrentTransaction(TransactionContextHelper.java:51)
at bitronix.tm.resource.jdbc.JdbcConnectionHandle.enlistResource(JdbcConnectionHandle.java:59)
... 72 more
Caused by: com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state
at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:602)
at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:585)
at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.start(MysqlXAConnection.java:525)
at bitronix.tm.internal.XAResourceHolderState.start(XAResourceHolderState.java:147)
at bitronix.tm.internal.XAResourceManager.enlist(XAResourceManager.java:84)
at bitronix.tm.BitronixTransaction.enlistResource(BitronixTransaction.java:66)
... 74 more

  1. Text File
    MySQL_XA.txt
    11/May/08 10:38 AM
    293 kB
    Brett Wooldridge

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Brett Wooldridge added a comment -

Here is some MySQL doc on their transaction semantics:

http://dev.mysql.com/doc/refman/5.0/en/xa-statements.html

Note that MySQL does not support XA END SUSPEND or XA START JOIN. In not sure how to work around this if the gtrid needs to remain constant across (end/suspend) and (start/resume) calls. It is possible for two gtrid's to be bound in the same transaction? If so, unique gtrid's could ALWAYS be used in the case of MySQL rather than using a constant gtrid and unique branch (gqual) IDs.

Show
Brett Wooldridge added a comment - Here is some MySQL doc on their transaction semantics: http://dev.mysql.com/doc/refman/5.0/en/xa-statements.html Note that MySQL does not support XA END SUSPEND or XA START JOIN. In not sure how to work around this if the gtrid needs to remain constant across (end/suspend) and (start/resume) calls. It is possible for two gtrid's to be bound in the same transaction? If so, unique gtrid's could ALWAYS be used in the case of MySQL rather than using a constant gtrid and unique branch (gqual) IDs.
Hide
Permalink
Brett Wooldridge added a comment -

Final comment, Atomikos TM has encountered this same issue and has "some kind" of workaround ... I don't know whether it works, but it might provide guidance to a similar workaround in Bitronix:

http://atomikos.com/Documentation/KnownProblems#MySQL_XA_bug

Show
Brett Wooldridge added a comment - Final comment, Atomikos TM has encountered this same issue and has "some kind" of workaround ... I don't know whether it works, but it might provide guidance to a similar workaround in Bitronix: http://atomikos.com/Documentation/KnownProblems#MySQL_XA_bug
Hide
Permalink
Ludovic Orban added a comment -

Brett,

This is a Mysql bug and there is absolutely nothing I can do about it. Once the transaction is suspended, there is no way to resume it and no way around that.

GTRID stands for Global TRansaction ID, two different GTRIDs are always referring to two different transactions as per specification. It is not possible to restart using another GTRID after suspension as the TM must interpret that as a separate transaction.

Mysql's XA support is far from being usable and it looks like they don't care much about it:

JDBC4PreparedStatementWrapper: NPE on close() - http://bugs.mysql.com/bug.php?id=35489
-> latest 5.1 driver's XA support is completely broken

XAConnection doesn't allow second XA START - http://bugs.mysql.com/bug.php?id=17343
-> the bug you're facing

Xa recovery and client disconnection - http://bugs.mysql.com/bug.php?id=12161
-> makes recovery useless

I know it would be nice to have a workaround to still allow it to work but alas, this is not possible at all (not counting that it would be pointless as the DB cannot recover). Atomikos has a workaround to make it work for simple usage but suffers from the same problem as BTM when you try to suspend.

The only workaround I can suggest is to use Last Resource Commit or to generate your PK using auto-incremented fields.

Show
Ludovic Orban added a comment - Brett, This is a Mysql bug and there is absolutely nothing I can do about it. Once the transaction is suspended, there is no way to resume it and no way around that. GTRID stands for Global TRansaction ID, two different GTRIDs are always referring to two different transactions as per specification. It is not possible to restart using another GTRID after suspension as the TM must interpret that as a separate transaction. Mysql's XA support is far from being usable and it looks like they don't care much about it: JDBC4PreparedStatementWrapper: NPE on close() - http://bugs.mysql.com/bug.php?id=35489 -> latest 5.1 driver's XA support is completely broken XAConnection doesn't allow second XA START - http://bugs.mysql.com/bug.php?id=17343 -> the bug you're facing Xa recovery and client disconnection - http://bugs.mysql.com/bug.php?id=12161 -> makes recovery useless I know it would be nice to have a workaround to still allow it to work but alas, this is not possible at all (not counting that it would be pointless as the DB cannot recover). Atomikos has a workaround to make it work for simple usage but suffers from the same problem as BTM when you try to suspend. The only workaround I can suggest is to use Last Resource Commit or to generate your PK using auto-incremented fields.
Hide
Permalink
Brett Wooldridge added a comment -

Will LRC work even without auto-incremented fields? What does BTM do with suspend/resume on a non-transactional DataSource?

Show
Brett Wooldridge added a comment - Will LRC work even without auto-incremented fields? What does BTM do with suspend/resume on a non-transactional DataSource?
Hide
Permalink
Ludovic Orban added a comment -

Yes, LRC should work even without auto-incremented fields, the only limitation should be that you can only have one LRC resource participating in a transaction.

The LRC datasource does its best to emulate what an XA datasource does but obviously there are some caveats. The XA start / end won't do anything, they're just used to ensure the TM does a good job at calling them in order so you'd better no try to use a single connection in multiple transactions. The emulation also depends on TM_JOIN usage so you have to leave useTmJoin to true or the datasource will throw an exception when you try to use it (yet another consistency check).

This is not perfect but that's the best that can be done.

Show
Ludovic Orban added a comment - Yes, LRC should work even without auto-incremented fields, the only limitation should be that you can only have one LRC resource participating in a transaction. The LRC datasource does its best to emulate what an XA datasource does but obviously there are some caveats. The XA start / end won't do anything, they're just used to ensure the TM does a good job at calling them in order so you'd better no try to use a single connection in multiple transactions. The emulation also depends on TM_JOIN usage so you have to leave useTmJoin to true or the datasource will throw an exception when you try to use it (yet another consistency check). This is not perfect but that's the best that can be done.
Hide
Permalink
Brett Wooldridge added a comment -

That's awesome, man. Thanks. It's not perfect, but it gets me around MySQL's limitations for now. Our application (also open source – www.ziptie.org) supports Derby, MySQL, and PostgreSQL. Surprisingly, Derby and PostgreSQL have better XA support than MySQL. Unfortunately, being an open source project also means that we have user's demanding MySQL support. We were basically "auto-commit" before, but when I moved everything to JTA, I busted our MySQL support.

Show
Brett Wooldridge added a comment - That's awesome, man. Thanks. It's not perfect, but it gets me around MySQL's limitations for now. Our application (also open source – www.ziptie.org) supports Derby, MySQL, and PostgreSQL. Surprisingly, Derby and PostgreSQL have better XA support than MySQL. Unfortunately, being an open source project also means that we have user's demanding MySQL support. We were basically "auto-commit" before, but when I moved everything to JTA, I busted our MySQL support.
Hide
Permalink
Ludovic Orban added a comment -

Another bug:
java.sql.PreparedStatement.close() causes IllegalArgumentException with MySQL set up as an XA datasource - http://bugs.mysql.com/bug.php?id=37645

Show
Ludovic Orban added a comment - Another bug: java.sql.PreparedStatement.close() causes IllegalArgumentException with MySQL set up as an XA datasource - http://bugs.mysql.com/bug.php?id=37645

People

  • Assignee:
    Ludovic Orban
    Reporter:
    Brett Wooldridge
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: