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
Tags: jdbc, SqlException, xa

[4 Dec 2012 12:17] Andrej Golovnin
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")) {
[4 Dec 2012 12:18] Andrej Golovnin
Patch for this issue.

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

[5 Dec 2012 10:33] Andrej Golovnin
Fixed typo in synopsis.
[14 Dec 2012 8:39] Tonci Grgin
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.
[15 Dec 2012 2:07] Mark Matthews
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.
[18 Feb 2014 13:51] Andrej Golovnin
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.

[21 Feb 2014 11:23] Alexander Soklakov
Thanks Andrej! Accepted.
[14 Apr 2014 8:20] Andrej Golovnin
Hi,

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

Best regards,
Andrej Golovnin
[16 Apr 2014 12:33] Alexander Soklakov
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?
[16 Apr 2014 13:11] Andrej Golovnin
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
[16 Apr 2014 13:23] Alexander Soklakov
Ah! I missed that, thank you very much :)
[14 May 2014 19:26] Daniel So
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."