Bug #19993 ReplicationDriver always connects to a same slave server.
Submitted: 22 May 2006 8:35 Modified: 26 Jul 2006 18:17
Reporter: 俊夫 大矢 Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S2 (Serious)
Version:3.1.12 OS:Any(Java)
Assigned to: CPU Architecture:Any

[22 May 2006 8:35] 俊夫 大矢
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: " 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:

// 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);
        ResultSet rs = conn.createStatement().executeQuery(
             "SHOW VARIABLES LIKE "PORT");
        if (rs.next())
           System.out.println("Connection[" + i + "]: " + rs.getString(2));
    } catch (Exception e) (


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://");

        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;
[3 Jul 2006 7:45] Sveta Smirnova
Thank you for the report.

Verified as reporter described using Connector/J 3.1.13. You can turn off last 2 slaves to be convinced what connection to first slave happens never.
[7 Jul 2006 4:50] 俊夫 大矢
Thank you for verifing this bug.

I want to realize below behavior with MySQL Connector/J.
1. Setting
URL: jdbc:mysql://master,slave1,slave2,slave3/db
Driver: com.mysql.jdbc.ReplicationDriver
Props: roundRobinLoadBalance=true, autoReconnect=true

2. Expect Behavior
First ReadOnly connection:  connecting to 'slave1'.
Second ReadOnly conncetion: connecting to 'slave2'.
Third ReadOnly connection: connecting to 'slave3'.
Forth ReadOnly connection: connecting to 'slave1'.

This behavior is explained in MySQL Ref. Manual-' Using Master/Slave Replication with ReplicationConnection'.

If that manual is correct, this bug should be fixed.
If not, the manual should be fixed.

Do you have any plan for this bug?
[19 Jul 2006 2:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from: