Bug #19993 ReplicationDriver always connects to a same slave server.

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;
    }

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.

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-'23.3.3.6. 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?

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:

  http://lists.mysql.com/commits/9316