MySQL Bugs: #86741: Multi-Host connection with autocommit=0 getAutoCommit maybe wrong

Bug #86741	Multi-Host connection with autocommit=0 getAutoCommit maybe wrong
Submitted:	19 Jun 2017 2:36	Modified:	27 Jul 2019 14:36
Reporter:	Jiajing Zhou	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.31, 5.1.39	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	5.1.31, 5.1.39, jdbc

Description:
Problem:
=======

When MySQL configured with “autocommit=0”, the Connection.getAutoCommit() of Multi-Host connection doesn't equal the value of “select @@autocommit” .

The Multi-Host connection contains all the following models:
 1. failover: jdbc:mysql://[primary host][:port],[secondary host 1][:port][,[secondary host 2][:port]]...[/[database]]
 2. loadbalance:jdbc:mysql:loadbalance://[host1][:port],[host2][:port][,[host3][:port]]...[/[database]] 
  3. replication:jdbc:mysql:replication://[master host][:port],[slave host 1][:port][,[slave host 2][:port]]...[/[database]] 

On “autocommit=0”,  the Single-Host connection executes “SET autocommit=1” as the last step of DriverManage.getConnection(), but the Multi-Host  connection  miss this.

How to repeat:
1. Configure MySQL with autocommit=0

2. Run java code: create connection, then query "select @@autocommit"

3. Notice: Multi-connection vs Single-connection

Jave code
---------------------------
public class Multi_host_bug_1 {
 String url= "jdbc:mysql://xxx.xxx.xxx.xxx:7001,xxx.xxx.xxx.xxx:7002/test; 
 //String url= "jdbc:mysql:loadbalance://xxx.xxx.xxx.xxx:7001,xxx.xxx.xxx.xxx:7002/test;
 //String url= "jdbc:mysql:replication://xxx.xxx.xxx.xxx:7001,xxx.xxx.xxx.xxx:7002/test;
 //String url= "jdbc:mysql://xxx.xxx.xxx.xxx:7001/test;

 public static void main(String[] args) throws SQLException {
  Connection conn = DriverManager.getConnection(url1);
          PreparedStatement stmt = null;
  try {
       stmt = conn.prepareStatement(“select @@autocommit”);
       ResultSet resultSet = stmt.executeQuery();
   while(resultSet.next()) {
    System.out.println(resultSet.getString(1))
   }
   System.out.println(conn.getAutoCommit());
   //when multi-host, resultSet.getString(1) is 0 (means false), but conn.getAutoCommit() is true
   resultSet.close();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   try {
    stmt.close();
    conn.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
  }
 }
}

Suggested fix:
Just For Multi-Host:
  Creating a host-connection should execute “set autocommit =1” or “set autocommit =0”, depending on the result of [Multi-Host connection].getAutoCommit().

Hello Jiajing,

Thank you for the bug report and test case.
Verified this behavior on MySQL Connector / J 5.1.39.

Thanks,
Chiranjeevi.

output:
run:
0
true
BUILD SUCCESSFUL (total time: 0 seconds)

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

" For multi-host connections, when a MySQL Server was configured with autocommit=0, Connection.getAutoCommit() did not return the correct value. This was because useLocalSessionState=true was assumed for multi-host connections, which might not be the case, resulting thus in inconsistent session states.

With the fix, by default, Connector/J executes some extra queries in the connection synchronization process to guarantee consistent session states between the client and the server at any connection switch. This would mean, however, that when none of the hosts are available during an attempted server switch, an exception for closed connection will be thrown immediately while, in earlier Connector/J versions, there would be a connection error thrown first before a closed connection error. Error handling in some applications might need to be adjusted accordingly.

Applications can skip the new session state synchronization mechanism by having useLocalSessionState=true. "

Thanks.