Description:
Using the ReplicationDriver in Connector/J 3.1.12, it seems to send sqlqueries to a same slave server for setReadOnly(true).
Referring to the MYSQL 5.0 reference Manual: "23.3.3.6. UsingMaster/Slave Replication with ReplicationConnection", we tested this feature. Using jdbc:mysql://master,slave1,slave2,slave3/db" with options "autoReconnect = true" and "roundRobinLoadBalance = true", this driver sent all read-only queries to ONLY slave2.
How to repeat:
Sample code:
// JDBC URL:
// Master: localhost:3306
// Slave1: localhost:3307
// Slave2: localhost:3307
// Slave3: localhost:3307
String jdbcUrl = "jdbc:mysql://localhost:3306,"
+ "localhost:3307,localhost:3308,localhost:3309/db";
// Properties:
Properties props = new Properties();
props.put("roundRobinLoadBalance", "true");
props.put("autoReconnect", "true");
// other options...
for (int i = 0; i < 4; i ++)
try {
Connection conn = new ReplicationDriver().connect(jdbcUrl, props);
conn.setReadOnly(false);
ResultSet rs = conn.createStatement().executeQuery(
"SHOW VARIABLES LIKE "PORT");
if (rs.next())
System.out.println("Connection[" + i + "]: " + rs.getString(2));
conn.close();
} catch (Exception e) (
e.printStackTrace();
}
Result:
Connection[0]: 3308
Connection[1]: 3308
Connection[2]: 3308
Connection[3]: 3308
Suggested fix:
I've looked into the Connector/J source code with a debugger, and found the following points.
ReplicationConnection has two - master and slave connections for queries. These connections are created with a same jdbc url - "jdbc:mysql:///". This url is used for a key of "roundRobinStateMap" HashMap in com.mysql.jdbc.Connection#getNextRoundRobinHostIndex().
This HashMap keeps indexes for selecting a next host from its connection's hostlist.
Because a master connection has only one host in its hostlist, "roundRobinStateMap" is always set "0" for "jdbc:mysql:///". And then, a slave connection gets always "1" from "roundRobinStateMap".
I think ReplicationConnection should set different jdbc urls for these two connections. For example, one is "jdbc:mysql://master/db", the other is "jdbc:mysq://slave1,salve2,slave3/db".
Sample fix:
public ReplicationConnection(Properties masterProperties,
Properties slaveProperties) throws SQLException {
Driver driver = new Driver();
// Create JDBC URLs for Master Connection and Slave Connection.
StringBuffer masterUrl = new StringBuffer("jdbc:mysql://");
StringBuffer slaveUrl = new StringBuffer("jdbc:mysql://");
masterUrl.append((String)masterProperties
.getProperty(NonRegisteringDriver.HOST_PROPERTY_KEY));
slaveUrl.append((String)slaveProperties
.getProperty(NonRegisteringDriver.HOST_PROPERTY_KEY));
masterUrl.append("/").append((String)masterProperties
.getProperty(NonRegisteringDriver.DBNAME_PROPERTY_KEY));
slaveUrl.append("/").append((String)slaveProperties
.getProperty(NonRegisteringDriver.DBNAME_PROPERTY_KEY));
this.masterConnection = (com.mysql.jdbc.Connection) driver.connect(
masterUrl.toString(), masterProperties);
this.slavesConnection = (com.mysql.jdbc.Connection) driver.connect(
slaveUrl.toString(), slaveProperties);
this.currentConnection = this.masterConnection;
}
Description: Using the ReplicationDriver in Connector/J 3.1.12, it seems to send sqlqueries to a same slave server for setReadOnly(true). Referring to the MYSQL 5.0 reference Manual: "23.3.3.6. UsingMaster/Slave Replication with ReplicationConnection", we tested this feature. Using jdbc:mysql://master,slave1,slave2,slave3/db" with options "autoReconnect = true" and "roundRobinLoadBalance = true", this driver sent all read-only queries to ONLY slave2. How to repeat: Sample code: // JDBC URL: // Master: localhost:3306 // Slave1: localhost:3307 // Slave2: localhost:3307 // Slave3: localhost:3307 String jdbcUrl = "jdbc:mysql://localhost:3306," + "localhost:3307,localhost:3308,localhost:3309/db"; // Properties: Properties props = new Properties(); props.put("roundRobinLoadBalance", "true"); props.put("autoReconnect", "true"); // other options... for (int i = 0; i < 4; i ++) try { Connection conn = new ReplicationDriver().connect(jdbcUrl, props); conn.setReadOnly(false); ResultSet rs = conn.createStatement().executeQuery( "SHOW VARIABLES LIKE "PORT"); if (rs.next()) System.out.println("Connection[" + i + "]: " + rs.getString(2)); conn.close(); } catch (Exception e) ( e.printStackTrace(); } Result: Connection[0]: 3308 Connection[1]: 3308 Connection[2]: 3308 Connection[3]: 3308 Suggested fix: I've looked into the Connector/J source code with a debugger, and found the following points. ReplicationConnection has two - master and slave connections for queries. These connections are created with a same jdbc url - "jdbc:mysql:///". This url is used for a key of "roundRobinStateMap" HashMap in com.mysql.jdbc.Connection#getNextRoundRobinHostIndex(). This HashMap keeps indexes for selecting a next host from its connection's hostlist. Because a master connection has only one host in its hostlist, "roundRobinStateMap" is always set "0" for "jdbc:mysql:///". And then, a slave connection gets always "1" from "roundRobinStateMap". I think ReplicationConnection should set different jdbc urls for these two connections. For example, one is "jdbc:mysql://master/db", the other is "jdbc:mysq://slave1,salve2,slave3/db". Sample fix: public ReplicationConnection(Properties masterProperties, Properties slaveProperties) throws SQLException { Driver driver = new Driver(); // Create JDBC URLs for Master Connection and Slave Connection. StringBuffer masterUrl = new StringBuffer("jdbc:mysql://"); StringBuffer slaveUrl = new StringBuffer("jdbc:mysql://"); masterUrl.append((String)masterProperties .getProperty(NonRegisteringDriver.HOST_PROPERTY_KEY)); slaveUrl.append((String)slaveProperties .getProperty(NonRegisteringDriver.HOST_PROPERTY_KEY)); masterUrl.append("/").append((String)masterProperties .getProperty(NonRegisteringDriver.DBNAME_PROPERTY_KEY)); slaveUrl.append("/").append((String)slaveProperties .getProperty(NonRegisteringDriver.DBNAME_PROPERTY_KEY)); this.masterConnection = (com.mysql.jdbc.Connection) driver.connect( masterUrl.toString(), masterProperties); this.slavesConnection = (com.mysql.jdbc.Connection) driver.connect( slaveUrl.toString(), slaveProperties); this.currentConnection = this.masterConnection; }