MySQL Bugs: #70785: MySQL Connector/J inconsistent init state for autocommit

Bug #70785	MySQL Connector/J inconsistent init state for autocommit
Submitted:	31 Oct 2013 12:39	Modified:	7 Feb 2017 19:52
Reporter:	Ivan Prisyazhniy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.26	OS:	Linux (x86_64, ubuntu/debian)
Assigned to:	Filipe Silva	CPU Architecture:	Any
Tags:	autocommit cache variables init elide

Description:
After connector driver initialization any new connections but first got invalid autocommit flag set to FALSE (while it's select @@autocommit = 1;), in a case when mysql server autocommit is TRUE (by default).

Those you've got inconsistent state inside your connections thinking it's ready for transactions, but it's not. you will not be able to check something is wrong expecting it's initialized to false after your connection pool/orm configuration setup. connection.getAutoCommit() will return FALSE as you expected to, to use it with your ORM with beginTransaction/commit/rollback. And on every query ran in a transaction you will get non-transactional unisolated execution without any warnings. 

This inconsistent state is only achievable with following 3 flags enabled simultaneously:
- cacheServerConfiguration=true
- useLocalSessionState=true
- elideSetAutoCommits=true

Why it is happening?
--------------------

Because you use strange way to process MysqlIO.isSetNeededForAutoCommitMode (wanted) based on MysqlIO.serverStatus (actual only for elide = true) variable that is last server response result. And the autocommit flag as the second bit of int32 is returned only by SHOW VARIABLES query that is ran from loadServerVariables from ConnectionImpl in a case when it is a first run or server variables caching is disabled.

So for any further connections loadServerVariables will not ask mysql for SHOW VARIABLES reusing what is already loaded from previous executions and thus MysqlIO.serverStatus will be left with it's default value 0. And after a while initializePropsFromServer will got overrideDefaultAutocommit = true, and autocommit = false and this is the point where unsync state introduced into an initialization.

How to repeat:
1) Use this connection string:

jdbc:mysql://127.0.0.1:3306/database?cacheServerConfiguration=true&amp;useLocalSessionState=true&amp;elideSetAutoCommits=true

2) Get second connection from driver.

3) You must use default mysql server autocommit variable configuration = 1.
   Check connection.getAutoCommit() == false.
   Check connection.createSqlQuery("SELECT @@autocommit") == true

Suggested fix:
I believe you must not use MysqlIO.serverStatus (last request server status response flags) to read autocommit server state flag for MysqlIO.isSetNeededForAutoCommitMode. Try read it and cache explicitly from SHOW VARIABLES request or replace it with something else.

a little bit wrong with use of @@autocommit.

you must use SHOW SESSION VARIABLES LIKE 'autocommit' everywhere instead of @@autocommit in my description of bug

init-connect was set to 'SET NAMES utf8'

Hi Ivan,

Thanks for you report.
Verified as described.

The problem with serverStatus is that although set correctly during handshake, it's later reset to 0 by checkErrorPacket(), so it's 0 in initializePropsFromServer. And the actual value of "init_connect" variable is not checked in isAutoCommitNonDefaultOnServer. 

I have no idea why checkErrorPacket drops serverStatus, so in my project I worked the issue around by just checking serverStatus via "SELECT @@session.autocommit" even in case of elideSetAutoCommits set to true and checking the value of "init_connect".

Do plan to fix serverState workflow or should I submit a patch with the workaround? The issue is pretty serious and it's gonna be 3yrs old next week.

Posted by developer:
 
Added the following entry to the Connector/J 5.1.41 changelog:

"With the connection properties cacheServerConfiguration=true, useLocalSessionState=true, and elideSetAutoCommits=true, any new connection to the server obtained after the first connection was established had the variable autoCommit equaled false, even if the value of the variable was true on the server. That was because the value of autoCommit was not properly initialized when a new connection was established, and this fix corrects that."