Bug #89948 | Batched statements are not committed for useLocalTransactionState=true | ||
---|---|---|---|
Submitted: | 7 Mar 2018 19:16 | Modified: | 31 Jul 2018 22:02 |
Reporter: | Jonas Boethius | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | Connector / J | Severity: | S1 (Critical) |
Version: | 5.1.45 | OS: | Any |
Assigned to: | Filipe Silva | CPU Architecture: | Any |
Tags: | REWRITEBATCHEDSTATEMENTS, useLocalTransactionState |
[7 Mar 2018 19:16]
Jonas Boethius
[8 Mar 2018 7:25]
Chiranjeevi Battula
Hello Jonas Boe, Thank you for the bug report and testcase. Verified this behavior on MySQL Connector / J 5.1.45. Thanks, Chiranjeevi.
[8 Mar 2018 7:25]
Chiranjeevi Battula
run: Row count was: 10, we expect: 10 Row count was: 10, we expect: 0 BUILD SUCCESSFUL (total time: 0 seconds)
[8 Mar 2018 15:08]
Mark Matthews
Have you tried using autogenerateTestcaseScript=true as a JDBC URL parameter to see that the expected COMMIT doesn't actually happen? This could also be the server misrepresenting the current transaction state (which has happened multiple times before). It could also be an interaction at the server for REPEATABLE READ and a connection that is in auto-commit, and one that is not (that is, if the COMMIT actually happens). useLocalTransactionState really should get deprecated in favor of useLocalSessionState, which doesn't trust the server's flags it sends back for transactions in progress (because they're sometimes subtly wrong), but instead will always commit or rollback, but *won't* go back to the server to see what the isolation level or auto-commit states are (which are usually more 'no-op' due to how connection pools and frameworks call those methods too often).
[8 Mar 2018 17:32]
Jonas Boethius
Hi Mark, If the question was directed to me I have not used autogenerateTestcaseScript specifically. I have though been doing debugging and also using connectionLifecycleInterceptors and logger hooks. It is clear to me that the commit is never written to the server. It seems that it is the method calls to enableMultiQueries and disableMultiQueries in MysqlIO class that messes up the local value of MysqlIO.serverStatus. My server has autocommit=ON as default value. The same problem has been reproduced independently of transaction isolation (at least for repeatable read and read committed which I've tested). Personally I think the intention of useLocalTransactionState=true makes a lot of sense in many cases.
[3 Apr 2018 16:29]
Filipe Silva
Hi Jonas, You are quite right. This is a bug in the internal multi-queries enabling methods. Until this is not fixed, as a workaround, you can turn on multi-queries manually with the connection option "allowMultiQueries=true". This avoids the wrong behavior and actually performs better since it skips a couple of round-trips to the server just to turn the multi-queries on and off on some query executions.
[11 Apr 2018 11:31]
Jonas Boethius
Hi Filipe, I have applied the config you recommended and it works as it should now. Thanks /Jonas
[31 Jul 2018 22:02]
Daniel So
Posted by developer: Added the following entry to the Connector/J 5.147 changelog: "Setting rewriteBatchedStatements=true and useLocalTransactionState=true caused transactions to be uncommitted for batched UPDATE and DELETE statements. It was due to the intermediate queries for enabling multiquery support on the server resetting the local transaction state as a side effect. With this fix, the local transaction state is preserved when the intermediate queries are executed."
[1 Aug 2018 15:47]
Daniel So
Posted by developer: Added chnagelog entry also for Connector/J 8.0.13.