Friday, February 15, 2013

MySQL Connector/J throws too many SQLExceptions

Some time ago while profiling the product I'm working on, I saw in the profiler that a lot of SQLExceptions are thrown during the execution of one of our use cases. But none of them shows up in the log files. We use the latest version of MySQL Connector/J with XA transactions. The URL we use to connect to the database looks like this:
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
The majority of the SQLExceptions were thrown on closing of already closed ResultSets. After looking into the bug database I found that this problem were already reported, s. the bug #67318. The fix for this bug is very easy. You can find the patch attached to the bug report. Fixing this bug helped me to reduce the number of thrown SQLExceptions from roughly 420000 (yep, so many exceptions) to ca. 25000 exceptions.

Profiling again the application I found that other SQLExceptions were thrown when executing the XA commands. I found it very, very strange. I mean, all transactions are processed without problems. In the URL above we force the JDBC driver to use server prepared statements. But the current implementation of the prepared statement protocol does not support XA commands yet. So the Connector/J sends a XA command first as a prepared statement. The server rejects it with the message:
This command is not supported in the prepared statement protocol yet
The server rejection results in an SQLException which we see in the profiler. The JDBC driver catches the exception and resends the XA command as a plain SQL statement. That is every XA command is sent twice to the MySQL server. I have reported this behavior in the bug report #67803. The fix for this issue is also very easy - only two additional lines of code. The patch is attached to the bug report. After fixing this bug we see no SQLExceptions anymore in the profiler. The best impact of fixing this bug was the performance improvement in our application, up to 15%, and the reduced network traffic.

So if you use MySQL with server prepared statements and XA protocol, then you should consider patching the MySQL Connector/J using the patches attached to the bug reports as long as Oracle has not yet integrated them into their code base.