MySQL Bugs: #78498: XA issue or limitation with 5.6.19 engine

Bug #78498	XA issue or limitation with 5.6.19 engine
Submitted:	21 Sep 2015 9:40	Modified:	21 Sep 2015 13:41
Reporter:	Francois Courtault	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: XA transactions	Severity:	S4 (Feature request)
Version:	5.6.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hello,

When migrating one of our solution based on Weblogic 10.3.6 or 12.1.3 from Oracle DB to MySQL we have the following issue:

Exception: code: 0 msg: Unexpected exception while enlisting XAConnection java.sql.SQLException: XA error: XAResource.XAER_INVAL start() failed on resource 'gemalto_xa_failover.ds_gemalto_domain': XAER_INVAL : Invalid arguments were given
com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_INVAL: Invalid arguments (or unsupported command)

I have talked to some Oracle/ Weblogic guy who, at the ned, told me that it is more probably a MySQL/Connector /J issue with XA management. The analysis was:

Xid:bea1-0001e1c7904375b56b2b Resource: gemalto_xa_failover.ds
XAResource.start(bea1-0001e1c7904375b56b2b-67656d616c746f5f78615f6661696c6f7665722e64735f67656d616c746f5f646f6d61696e, XAResource.TMNOFLAGS) for 
XAResource.end(bea1-0001e1c7904375b56b2b-67656d616c746f5f78615f6661696c6f7665722e64735f67656d616c746f5f646f6d61696e, XAResource.TMSUCCESS)
XAResource.start(bea1-0001e1c7904375b56b2b-67656d616c746f5f78615f6661696c6f7665722e64735f67656d616c746f5f646f6d61696e, XAResource.TMJOIN)
####<2015-06-29_13:39:58,100> <Debug> <JTAXA> <rhel65> <managed1> <[ACTIVE] ExecuteThread: '2' for queue: 'weblogic.kernel.Default (self-tuning)'> <<anonymous>> <BEA1-0001E1C7904375B56B2B> <> <1435556398100> <BEA-000000> <BEA1-0001E1C7904375B56B2B: null: XA.start FAILED (rm=gemalto_xa_failover.ds_gemalto_domain, xar=gemalto_xa_failover.ds
com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_INVAL: Invalid arguments (or unsupported command)
    at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:585)
    at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:568)
    at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.start(MysqlXAConnection.java:508)
    at weblogic.jdbc.jta.DataSource.start(DataSource.java:811)

If you look at the JTA specs in Table 1 : "Transaction Association," it says that to do a TMJOIN the transaction must be "Not Associated", that is true in this case because the TransactionManager called delist with TMSUCCESS.  It also states that "If TMJOIN is specified, the start is for joining an existing transaction branch
xid. " which in this case is true, look at the bolded part of the xids above.

Best Regards.

How to repeat:
I have a basic Java program which demontrates the issue but I don't know how to upload it. Could you provide me please the way to do so ?

The steps are:
    - Start MySQL engine (5.6.19) 
    - Modify the Java source in order to target your MySQL engine (line 173 to 175)
    - Run the Java test program with either mysql-connector-java-commercial-5.1.27-bin.jar or mysql-connector-java-commercial-5.1.35-bin.jar in the classpath.

You will get this output (with mysql-connector-java-commercial-5.1.35-bin.jar):
db product name= MySQL
Got a connection
Starting XA tx...
Doing a query...
Ending first XA tx...
com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_INVAL: Invalid arguments (or unsupported command)
	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.gemalto.test.mysql.xa.TestMySQLXAConnectorJ.run(TestMySQLXAConnectorJ.java:66)
	at com.gemalto.test.mysql.xa.TestMySQLXAConnectorJ.main(TestMySQLXAConnectorJ.java:192)
Caused by: java.sql.SQLException: XAER_INVAL: Invalid arguments (or unsupported command)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2489)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:848)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:742)
	at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:560)
	... 3 more

Suggested fix:
None

The Java program to reproduce the issue

Attachment: TestMySQLXAConnectorJ.java (application/octet-stream, text), 11.38 KiB.

Hello,

The issue was first reported in Category  Connector J: Issue # 77573

In order to help you to move forward you may contact Monica Riccelli from Oracle who help me to diagnose this issue. 

Regards

Hello again,

According to the Connect/J support guy:
"The problem is caused by MySQL server limitation of XA support. Please look into http://dev.mysql.com/doc/refman/5.7/en/xa-statements.html, "For XA START, the JOIN and RESUME clauses are not supported"."

Is this feature available in MySql version greater than 5.6.19 ?

Best Regards.

Hello Francois,

Thank you for the report.
This is a known and documented limitation and explained here http://dev.mysql.com/doc/refman/5.6/en/xa-statements.html

As suggested in Bug #77573 [21 Sep 9:00] Alexander Soklakov, converting this to FR.

Thanks,
Umesh

Hello,

Thanks but could you give please a high priority to this FR as it prevents us to use MySQL with Weblogic on 2 phase commit scenarii and so in production :-(

Best Regards.