Description:
In some case Connector/J's round-robin function doesn't work.
I had 2 mysqld, node1 "localhost:3306" and node2 "localhost:3307".
1. node1 is up, node2 is up
2. java-program connect to node1 by using properties "autoRecconect=true","roundRobinLoadBalance=true","failOverReadOnly=false".
3. node1 is down, node2 is up
4. java-program execute a query and fail, but Connector/J's round-robin fashion failover work and if java-program retry a query it can succeed (connection is change to node2 by Connector/j)
5. node1 is up, node2 is up
6. node1 is up, node2 is down
7. java-program execute a query, but this time Connector/J doesn't work althought node1 is up and usable.
Thanks.
How to repeat:
Run this code.
-----------------------------------------------------------
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306,localhost:3307/test";
Properties props = new Properties();
props.put("autoReconnect", "true");
props.put("roundRobinLoadBalance", "true");
props.put("failOverReadOnly", "false");
props.put("user", "root");
props.put("password", "");
Connection con = DriverManager.getConnection(url, props);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("show variables like 'port'");
rs.next();
System.out.println("connected to port:" + rs.getString(2));
rs = stmt.executeQuery("select connection_id()");
rs.next();
String originalConnectionId = rs.getString(1);
stmt.executeUpdate("kill " + originalConnectionId);
try {
rs = stmt.executeQuery("show variables like 'port'");
} catch (SQLException ex) {
// failover and retry
rs = stmt.executeQuery("show variables like 'port'");
}
rs.next();
System.out.println("connected to port:" + rs.getString(2));
rs = stmt.executeQuery("select connection_id()");
rs.next();
originalConnectionId = rs.getString(1);
stmt.executeUpdate("kill " + originalConnectionId);
try {
rs = stmt.executeQuery("show variables like 'port'");
} catch (SQLException ex) {
// failover and retry
rs = stmt.executeQuery("show variables like 'port'");
}
rs.next();
System.out.println("connected to port:" + rs.getString(2));
con.close();
-----------------------------------------------------------
You will see log like this.
-----------------------------------------------------------
connected to port:3306
connected to port:3307
Exception in thread "main" java.sql.SQLException: Server connection failure during transaction. Due to underlying exception: 'null'.
** BEGIN NESTED EXCEPTION **
** END NESTED EXCEPTION **
Attempted reconnect 3 times. Giving up.
at com.mysql.jdbc.Connection.createNewIO(Connection.java:1954)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2358)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1183)
at net.ikda.ConnectorJFailOverTest.main(ConnectorJFailOverTest.java:50)
Suggested fix:
I found com.mysql.jdbc.Connection#getNextRoundRobinHostIndex.
Please see Connection.java line 2550.
if (index[0] > hostList.size()) {
index[0] = 0;
}
This should be...
if (index[0] >= hostList.size()) {
index[0] = 0;
}
When I modified this and re-run that code, I got right log like this.
connected to port:3306
connected to port:3307
connected to port:3306
Description: In some case Connector/J's round-robin function doesn't work. I had 2 mysqld, node1 "localhost:3306" and node2 "localhost:3307". 1. node1 is up, node2 is up 2. java-program connect to node1 by using properties "autoRecconect=true","roundRobinLoadBalance=true","failOverReadOnly=false". 3. node1 is down, node2 is up 4. java-program execute a query and fail, but Connector/J's round-robin fashion failover work and if java-program retry a query it can succeed (connection is change to node2 by Connector/j) 5. node1 is up, node2 is up 6. node1 is up, node2 is down 7. java-program execute a query, but this time Connector/J doesn't work althought node1 is up and usable. Thanks. How to repeat: Run this code. ----------------------------------------------------------- Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://localhost:3306,localhost:3307/test"; Properties props = new Properties(); props.put("autoReconnect", "true"); props.put("roundRobinLoadBalance", "true"); props.put("failOverReadOnly", "false"); props.put("user", "root"); props.put("password", ""); Connection con = DriverManager.getConnection(url, props); Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("show variables like 'port'"); rs.next(); System.out.println("connected to port:" + rs.getString(2)); rs = stmt.executeQuery("select connection_id()"); rs.next(); String originalConnectionId = rs.getString(1); stmt.executeUpdate("kill " + originalConnectionId); try { rs = stmt.executeQuery("show variables like 'port'"); } catch (SQLException ex) { // failover and retry rs = stmt.executeQuery("show variables like 'port'"); } rs.next(); System.out.println("connected to port:" + rs.getString(2)); rs = stmt.executeQuery("select connection_id()"); rs.next(); originalConnectionId = rs.getString(1); stmt.executeUpdate("kill " + originalConnectionId); try { rs = stmt.executeQuery("show variables like 'port'"); } catch (SQLException ex) { // failover and retry rs = stmt.executeQuery("show variables like 'port'"); } rs.next(); System.out.println("connected to port:" + rs.getString(2)); con.close(); ----------------------------------------------------------- You will see log like this. ----------------------------------------------------------- connected to port:3306 connected to port:3307 Exception in thread "main" java.sql.SQLException: Server connection failure during transaction. Due to underlying exception: 'null'. ** BEGIN NESTED EXCEPTION ** ** END NESTED EXCEPTION ** Attempted reconnect 3 times. Giving up. at com.mysql.jdbc.Connection.createNewIO(Connection.java:1954) at com.mysql.jdbc.Connection.execSQL(Connection.java:2358) at com.mysql.jdbc.Connection.execSQL(Connection.java:2297) at com.mysql.jdbc.Statement.executeQuery(Statement.java:1183) at net.ikda.ConnectorJFailOverTest.main(ConnectorJFailOverTest.java:50) Suggested fix: I found com.mysql.jdbc.Connection#getNextRoundRobinHostIndex. Please see Connection.java line 2550. if (index[0] > hostList.size()) { index[0] = 0; } This should be... if (index[0] >= hostList.size()) { index[0] = 0; } When I modified this and re-run that code, I got right log like this. connected to port:3306 connected to port:3307 connected to port:3306