any workaround for XA END [SUSPEND] not supported in MySQL 5

[kiwu2201]

I am using JBoss 4.2.GA with two XA datasources and MySQL 5.0.x

The documentation of MqSQL 5 says:

For XA END the SUSPEND [FOR MIGRATE] clause is not supported

So I get the following error when I start a JBPM process that includes a Timer:

13:25:02,468 WARN [TxConnectionManager] Connection error occured: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@4c2683[state=NORMAL mc=org.jboss.resource.adapter.
jdbc.xa.XAManagedConnection@9aa95c handles=0 astUse=1176899102453 permit=true trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$PoolByCri@18a8bfa context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@ec0b80 xaResource=org.jboss.resource.connectionmanager.xa.JcaXAResourceWrapper@1748ba4 txSync=null]com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_INVAL: Invalid arguments (or unsupported command)
 at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:562)

Is there any workaround for that?

When I use two local TX datasources I run into the problem that JBoss 4.2.GA doesn't allow multiple local TX datasource in one transaction.

Any help would be highly appreciated!

Chris

[marklittle]

Upgrade to JBossTS 4.2.3: that allows you to have multiple one-phase participants in the same transaction. Not recommended, but your risk.

[weston.price]

Typically you can correct behavior like this by adding the

<track-connection-by-tx/>

element to your *-ds.xml file. This disables transaction interleaving (which BTW, most DB vendors don't support). Give that a whirl and let us know.

[weston.price]

Upgrade to JBossTS 4.2.3: that allows you to have multiple one-phase participants in the same transaction. Not recommended, but your risk.

And yes, there is that approach as well :-)

[kiwu2201]

Approach 1:

I have downloaded the jbossts-jta-4.2.3.GA.zip file and copied the following jar files into my existing JBoss-4.2.GA installation.

- jbossjta-integration.jar
- jbossjta.jar
- jbossts-common.jar

With two local tx datasources deployed I still get the error:

Could not enlist in transaction on entering meta-aware object!;

Do I have to change any configuration to allow multiple one-phase participants in the same transaction?

Approach 2:

Added

<track-connection-by-tx/>

to my datasources.

Now I get the following error messages during startup:

15:16:35,406 INFO [testQueue] Bound to JNDI name: queue/testQueue
15:16:35,421 INFO [UILServerILService] JBossMQ UIL service available at : localhost/127.0.0.1:8093
15:16:35,453 ERROR [STDERR] Wed Apr 18 15:16:35 BST 2007 DEBUG: Executing XA statement: XA START 0x312d2d35336566666534633a6339363a34363236323833613a3465,0x2d35336566666534633a6339363a34363236323833613a3531,0x20003
15:16:35,453 ERROR [STDERR] Wed Apr 18 15:16:35 BST 2007 DEBUG: Executing XA statement: XA END 0x312d2d35336566666534633a6339363a34363236323833613a3465,0x2d35336566666534633a6339363a34363236323833613a3531,0x20003
15:16:35,453 ERROR [STDERR] Wed Apr 18 15:16:35 BST 2007 DEBUG: Executing XA statement: XA COMMIT 0x312d2d35336566666534633a6339363a34363236323833613a3465,0x2d35336566666534633a6339363a34363236323833613a3531,0x20003 ONE PHASE

After a few lines where my jbpm-enterprise ear file is deployed I get the following:

15:16:35,781 INFO [EARDeployer] Init J2EE application: file:/C:/servers/jboss-4.2.0.CR2/server/default/deploy/jbpm-enterprise-mysql.ear
15:16:37,937 INFO [EjbModule] Deploying CommandServiceBean
15:16:37,968 INFO [EjbModule] Deploying TimerServiceBean
15:16:37,984 INFO [EjbModule] Deploying CommandListenerBean
15:16:38,000 INFO [EjbModule] Deploying JobListenerBean
15:16:38,312 INFO [BaseLocalProxyFactory] Bound EJB LocalHome 'CommandServiceBean' to jndi 'CommandServiceBean'
15:16:38,328 INFO [ProxyFactory] Bound EJB Home 'CommandServiceBean' to jndi 'ejb/CommandServiceBean'
15:16:38,343 INFO [BaseLocalProxyFactory] Bound EJB LocalHome 'TimerServiceBean' to jndi 'local/TimerServiceBean@31725673'
15:16:38,406 ERROR [STDERR] Wed Apr 18 15:16:38 BST 2007 DEBUG: Executing XA statement: XA START 0x312d2d35336566666534633a6339363a34363236323833613a3630,0x2d35336566666534633a6339363a34363236323833613a3633,0x20003
15:16:38,406 WARN [loggerI18N] [com.arjuna.ats.internal.jta.transaction.arjunacore.enliststarterror] [com.arjuna.ats.internal.jta.transaction.arjunacore.enliststarterror] TransactionImple.enlistResource - XAResource.start returned: XAException.XAER_OUTSIDE for < 131075, 27, 25, 1--53effe4c:c96:4626283a:60-53effe4c:c96:4626283a:63
15:16:38,406 ERROR [STDERR] com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_OUTSIDE: Some work is done outside global transaction
15:16:38,406 ERROR [STDERR] at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:562)
15:16:38,406 ERROR [STDERR] at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:545)
15:16:38,406 ERROR [STDERR] at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.start(MysqlXAConnection.java:485)
15:16:38,406 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.xa.XAManagedConnection.start(XAManagedConnection.java:121)

Do you have any idea what I am doing wrong?

[weston.price]

Could you please post the contents of your *-ds.xml file(s) as well as your MySQL driver version.

[weston.price]

One more thing to try if you don't mind, could you set the following in your XA datasource *-ds.xml file:

<isSameRM-override-value>false</isSameRM-override-value>

[kiwu2201]

MySQL driver version: mysql-connector-java-5.0.4-bin.jar

The datasources:

1:

<xa-datasource>
 <jndi-name>DefaultDS</jndi-name>
 <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
 <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/jbossdb</xa-datasource-property>

 <user-name>jboss</user-name>
 <password>jboss</password>
 <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

 <!-- This disables transaction interleaving (which BTW, most DB vendors don't support) -->
 <track-connection-by-tx/>
 <isSameRM-override-value>false</isSameRM-override-value>

 <!--pooling parameters-->
 <min-pool-size>5</min-pool-size>
 <max-pool-size>20</max-pool-size>
 <blocking-timeout-millis>5000</blocking-timeout-millis>
 <idle-timeout-minutes>15</idle-timeout-minutes>

 <!-- pooling criteria. USE AT MOST ONE-->
 <!-- If you don't use JAAS login modules or explicit login
 getConnection(usr,pw) but rely on user/pw specified above,
 don't specify anything here -->

 <!-- If you supply the usr/pw from a JAAS login module -->
 <security-domain/>

 <!-- if your app supplies the usr/pw explicitly getConnection(usr, pw) -->
 <application-managed-security/>
 <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
 <metadata>
 <type-mapping>mySQL</type-mapping>
 </metadata>
 </xa-datasource>

2:

<xa-datasource>
 <jndi-name>JbpmDS</jndi-name>
 <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
 <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/jbpm</xa-datasource-property>

 <user-name>jbpm</user-name>
 <password>jbpm</password>
 <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

 <!-- This disables transaction interleaving (which BTW, most DB vendors don't support) -->
 <track-connection-by-tx/>
 <isSameRM-override-value>false</isSameRM-override-value>

 <!--pooling parameters-->
 <min-pool-size>5</min-pool-size>
 <max-pool-size>20</max-pool-size>
 <blocking-timeout-millis>5000</blocking-timeout-millis>
 <idle-timeout-minutes>15</idle-timeout-minutes>

 <!-- pooling criteria. USE AT MOST ONE-->
 <!-- If you don't use JAAS login modules or explicit login
 getConnection(usr,pw) but rely on user/pw specified above,
 don't specify anything here -->

 <!-- If you supply the usr/pw from a JAAS login module -->
 <security-domain/>

 <!-- if your app supplies the usr/pw explicitly getConnection(usr, pw) -->
 <application-managed-security/>
 <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
 <metadata>
 <type-mapping>mySQL</type-mapping>
 </metadata>
 </xa-datasource>

I also added

<isSameRM-override-value>false</isSameRM-override-value>

But get the same errors as before:

XAER_OUTSIDE: Some work is done outside global transaction

[weston.price]

And more to the point:

http://bugs.mysql.com/bug.php?id=17734

It' looks like MySQL needs the

<no-tx-separate-pools/>

element. This basically segrates connection pools based on whether or not the connection is used in a transaction and outside of a transaction. Some DBs (most notably Oracle) have shown this issue.

[marklittle]

To allow multiple one-phase resources in the same transaction you need to read page 18 of the JTA programmers guide.

[kiwu2201]

Finally I added also

<no-tx-separate-pools/>

to my datasources and now it seems to be working.

But I still get the following errors when an XA command is executed:

16:13:14,890 ERROR [STDERR] Wed Apr 18 16:13:14 BST 2007 DEBUG: Executing XA statement: XA START 0x312d2d35336566666534633a3538313a34363236333435653a313166,0x2d35336566666534633a3538313a34363236333435653a313234,0x20003
16:13:14,890 INFO [STDOUT] Hibernate:
 /* criteria query */ select
 this_.ID_ as ID1_20_1_,
 this_.VERSION_ as VERSION2_20_1_,
 this_.KEY_ as KEY3_20_1_,
 this_.START_ as START4_20_1_,
 this_.END_ as END5_20_1_,
 this_.ISSUSPENDED_ as ISSUSPEN6_20_1_,
 this_.PROCESSDEFINITION_ as PROCESSD7_20_1_,
 this_.ROOTTOKEN_ as ROOTTOKEN8_20_1_,
 this_.SUPERPROCESSTOKEN_ as SUPERPRO9_20_1_,
 processdef1_.ID_ as ID1_4_0_,
 processdef1_.NAME_ as NAME3_4_0_,
 processdef1_.DESCRIPTION_ as DESCRIPT4_4_0_,
 processdef1_.VERSION_ as VERSION5_4_0_,
 processdef1_.ISTERMINATIONIMPLICIT_ as ISTERMIN6_4_0_,
 processdef1_.STARTSTATE_ as STARTSTATE7_4_0_
 from
 JBPM_PROCESSINSTANCE this_
 inner join
 JBPM_PROCESSDEFINITION processdef1_
 on this_.PROCESSDEFINITION_=processdef1_.ID_
 where
 this_.END_ is null limit ?
16:13:14,921 ERROR [STDERR] Wed Apr 18 16:13:14 BST 2007 DEBUG: Executing XA statement: XA END 0x312d2d35336566666534633a3538313a34363236333435653a313166,0x2d35336566666534633a3538313a34363236333435653a313234,0x20003
16:13:14,921 ERROR [STDERR] Wed Apr 18 16:13:14 BST 2007 DEBUG: Executing XA statement: XA COMMIT 0x312d2d35336566666534633a3538313a34363236333435653a313166,0x2d35336566666534633a3538313a34363236333435653a313234,0x20003 ONE PHASE

Should I be concerned about that?

[weston.price]

As near as I can tell from the messages they are simply at DEBUG level. I am not sure why this is going to STDERR (or being flagged as such). The lack of an explicit exception would lead me to believe that this is simply some sort of trace mechanism in the MySQL driver code that, hopefully, can be turned off or at the very least be turned down.

[kiwu2201]

Great. Thanks a lot for your help!

Cheers
Chris

[jbalint]

If you upgrade to the latest JDBC driver (5.0.5 *1), this won't be printed directly to STDERR anymore. There is a connection property called logXaCommands *2 that will send them as DEBUG messages.

Jess

*1 Connector/J 5.0.5 Release Notes
http://dev.mysql.com/doc/refman/5.0/en/cj-news-5-0-5.html
*2 Connector/J Connection Properties
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html