MySQL Bugs: #67803: XA commands sent twice to MySQL server

Bug #67803	XA commands sent twice to MySQL server
Submitted:	4 Dec 2012 12:17	Modified:	14 May 2014 19:26
Reporter:	Andrej Golovnin (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S5 (Performance)
Version:	5.1.22	OS:	Any
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	jdbc, SqlException, xa

Description:
When using XA transaction with MySQL JDBC driver and following URL to connect to the MySQL Server:

jdbc:mysql://localhost:3306/myschema?useConfigs=maxPerformance&useLocalTransactionState=true&rewriteBatchedStatements=true&useCursorFetch=true&defaultFetchSize=50&maintainTimeStats=false&useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=true&dumpQueriesOnException=true

we see that every XA command is first sent as server prepared statement to the MySQL server. This leads to following behavior:

1. MySQL server answers that the prepared statement protocol does not support XA commands yet.
2. MySQL JDBC driver throws SQLException (and InvocationTargetException as the constructor of JDBC4ServerPreparedStatement is called using reflection API).
3. the thrown SQLException is catched and then the XA command is sent as plain statement.

MySQL JDBC driver should be changed to not send the XA commands as prepared statement at all as they are not yet supported by the prepared statement protocol.

How to repeat:
Create an application which connects to the MySQL server using the above URL and performs some XA transactions and use profiler to see generated SQLExceptions. Additionally you ca use Wireshark to monitor network traffic between application and MySQL server to see that every XA command is sent twice to the server.

Suggested fix:
=== modified file 'src/com/mysql/jdbc/ConnectionImpl.java'
--- src/com/mysql/jdbc/ConnectionImpl.java	2012-07-11 14:22:24 +0000
+++ src/com/mysql/jdbc/ConnectionImpl.java	2012-12-04 11:46:56 +0000
@@ -1177,6 +1177,8 @@
 			}
 
 			canHandleAsStatement = !foundLimitWithPlaceholder;
+		} else if (StringUtils.startsWithIgnoreCase(sql, "XA ")) {
+			canHandleAsStatement = false;
 		} else if (StringUtils.startsWithIgnoreCaseAndWs(sql, "CREATE TABLE")) {
 			canHandleAsStatement = false;
 		} else if (StringUtils.startsWithIgnoreCaseAndWs(sql, "DO")) {

Patch for this issue.

Attachment: ConnectionImpl.patch (application/octet-stream, text), 555 bytes.

Fixed typo in synopsis.

Hi Andrej and thanks for your report.

The sequence of events is this:
  o You forced usage of PS in connection string by putting useServerPrepStmts=true
  o Code goes into canHandleAsServerPreparedStatement
  o There is no check for XA resulting in canHandleAs(Prepared)Statement = true

I am not sure about the patch, although it is correct per-se, since once the XA starts supporting PS someone will have to remember we removed it from there and fix again.

Let me check with colleagues.

A warning to anyone trying to use XA with MySQL at the moment,  2PC commit is broken on loss of connection after prepare() - you'll end up with heuristic failures.

Patch for this issue. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: ConnectionImpl.patch (application/octet-stream, text), 555 bytes.

Thanks Andrej! Accepted.

Hi,

any plans to fix this issue in the next release of Connector/J?

Best regards,
Andrej Golovnin

Hi Andrej,

Could you provide an example of code which produce prepared statements with XA commands? I don't see how it's possible with our API for that commands generated and sent under the hood, so I guess it's something manual, right?

Hi Alexander,

> Could you provide an example of code which produce prepared statements with XA commands? 

I'm sorry I can't. We use Connector/J with JBoss and we are using XA transactions. The transaction manager of JBoss just calls the methods of MysqlXAConnection.

> I don't see how it's possible with our API for that commands generated and sent under the hood, so I guess it's something manual, right?

No and it is possible. :-) It depends on the configuration of the Connector/J. Please take look at the URL (you can find it in the description of this issue) we use to connect to MySQL. We have following properties in the URL useCursorFetch=true and defaultFetchSize=50. So now take look at the class StatementImpl lines 879-880:

879:  if (useServerFetch()) {
880:      rs = createResultSetUsingServerFetch(sql);

The method StatementImpl#useServerFetch() returns true in our case. The method StatementImpl#createResultSetUsingServerFetch calls Connection#prepareStatement in the line 705.

Hope this helps.

Best regards,
Andrej Golovnin

Ah! I missed that, thank you very much :)

Added the following entry to the Connector/J 5.1.31 changelog:

"When useCursorFetch was set to true, Connector/J would attempt to send XA commands as server prepared statements, which were unsupported, and the commands would have to be resent as plain statements. This fix stops Connector/J from sending XA commands as server prepared statements."