Bug #104067 No reset autoCommit after unknown issue occurs
Submitted: 21 Jun 8:47 Modified: 30 Jul 15:59
Reporter: Tingyu Wei (OCA) Email Updates:
Status: Need Feedback Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:8.0.25 OS:Any
Assigned to: CPU Architecture:Any

[21 Jun 8:47] Tingyu Wei
If an unknown exception other than a network exception occurs when executing setAutoCommit SQL, the autoCommit value of server session should be reset to the previous value.

Otherwise, the connection was picked by other threads, and the autoCommit value of server session is not equals to the value of autoCommit from DB, which cause the transaction enter to a wrong status after doBegin: The app server is autoCommit = false, and the DB server id autoCommit = true. In this case, the DB wouldn't exec SQLs in the method as a transaction.

How to repeat:
1. Start a HTTP service with a transaction API, and the service use the following frameworks: 
  a. mysql-connector-j
  d. HikariCP:2.7.6
  e. spring-framework:4.3.18.RELEASE
2. Mysql DB Server throw a unknown issue if App server exec the SQL: "set autoCommit = false"

3. we can see the current thread can throw an exception. 
In the case, the autoCommit value is true in DB connector. while the autoCommit value in the connector session still keep false. 
And the next thread who get the DB connector will inherit the autoCommit = false. 

4. The result: The transactional of the next thread has not take effective in DB.

Suggested fix:
Reset the autoCommit value if unknown issue occurs when setting autoCommit.
And here is my PR, please help to review, thanks.
[28 Jun 16:20] MySQL Verification Team

I'm verifying this as FR, not as a bug. Thanks for the report and for the patch.

all best
Bogdan Kecman
[29 Jun 13:54] Tingyu Wei
Dear MySQL Verification Team,
Please double check the case and reopen it.

This case is indeed a bug. 
If the DB connection set autocommit fails, and I use a connection pool, the next thread will get the error autocommit value from connection in pool locally.

This has caused transaction failures and business losses in our company's business.

Expected: if set autocommit fails, the local autocommit should be reset to old value. Pls ref the link: https://github.com/mysql/mysql-connector-j/pull/66/files

Thanks a ton!
[29 Jun 13:56] MySQL Verification Team

Bug is now in the hands of Connector/J development team. There is no "reopening" as bug is already in "Verified", from there we are now waiting for "Closed" when the fix is implemented
[29 Jun 14:12] Tingyu Wei
Hi MySQL Verification Team,

Could you please tell me how the Connector/J development team will handle the bug?
And Will my PR be accepted?

Best Regards
[29 Jun 16:02] OCA Admin
Contribution submitted via Github - reset autoCommit after unknown issue occurs 
(*) Contribution by tingyu wei (Github weitingyuk, mysql-connector-j/pull/66#issuecomment-867643652): @mysql-oca-bot I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Here is my bug report. please help to review.

Contribution: git_patch_663614465.txt (text/plain), 1.93 KiB.

[30 Jun 5:36] Tingyu Wei
Dear OCAAdmin,
Thanks for attach my contribution, I am not sure what I need to do in the next step for fixing the bug.

If any question, pls connect me any time.

[7 Jul 9:48] Tingyu Wei
Hi MySQL Verification Team,

Could you please kindly tell me when the bug fix will be merged in to the master branch? since out company looking forward to use the new feature.

Thanks very much.
Best Regards.

By Tingyu
[30 Jul 15:58] Filipe Silva
Hi Tingyu,

Thank you for your interest in MySQL and Connector/J.

Please help me better understand your problem: What exceptions are you expecting from executing a statement like `set autoCommit = false`? In what situation is this throwing an exception that is not network/connectivity issue or something that entirely invalidates the connection object?

Can you please provide a test case using just Connector/J?