Bug #49537 driver.connect is connecting just to first slave, and if it is down it hangs
Submitted: 8 Dec 2009 16:39 Modified: 16 Dec 2009 10:03
Reporter: zohar aharoni Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.10 OS:Any
Assigned to: Mark Matthews CPU Architecture:Any
Tags: connector, java, loadbalance, replication
Triage: D2 (Serious)

[8 Dec 2009 16:39] zohar aharoni
Description:
Hi,
I'm trying to use the replication awareness of mysql driver.
I took your general example (see "how to repeat" section).
Than I stopped the first slave in the list and ran it to see if it will connect the next slave in the list.
driver.connect hung until I started the slave again...

Also - when I ran it in a loop with readonly = true. I always got connection to the first slave and not to the others. I would expect:

1st connection - 1st slave
2st connection - 2st slave
3st connection - 3st slave
4st connection - 1st slave
...

Thanks,
Zohar

How to repeat:
package com.myApp.CJTest;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Properties;
import com.mysql.jdbc.ReplicationDriver;

public class ReplicationDriverDemo {

	public static void main(String[] args) throws Exception 
	{
	
		ReplicationDriver driver = new ReplicationDriver();
		Properties props = new Properties();
		// We want this for failover on the slaves
		props.put("autoReconnect", "true");
		// We want to load balance between the slaves
		props.put("roundRobinLoadBalance", "true");
		props.put("user", "root");
		props.put("password", "");
	
		// Looks like a normal MySQL JDBC url, with a
		// comma-separated list of hosts, the first
		// being the 'master', the rest being any number
		// of slaves that the driver will load balance against
		Connection conn = 
			driver.connect("jdbc:mysql://m1,s1,s2,s3/test",
				props);
	
	
		// Perform read/write work on the master
		// by setting the read-only flag to "false"
		conn.setReadOnly(false);
		conn.setAutoCommit(false);
		ResultSet rs = null;
		
		rs = conn.createStatement().executeQuery("SELECT host FROM test.hostname");
		
		if (rs.next())
			System.out.println(rs.getString(1));
	
		// Now, do a query from a slave, the driver automatically picks one
		// from the list
		conn.setReadOnly(true);
	
		rs = conn.createStatement().executeQuery("SELECT host FROM test.hostname");
		if (rs.next())
			System.out.println(rs.getString(1));
	}
}

Suggested fix:
driver.connect hung until I started the slave again...
-- I would expect it to connect to the next available slave.

Also - when I ran it in a loop with readonly = true. I always got connection to the first slave and not to the others. 

I would expect:
  1st connection - 1st slave
  2st connection - 2st slave
  3st connection - 3st slave
  4st connection - 1st slave
  ...
[11 Dec 2009 0:16] Todd Farmer
Proposed patch

Attachment: diff.txt (text/plain), 1011 bytes.

[11 Dec 2009 6:31] Tonci Grgin
Pending Mark's review and based on Todd's test I'm marking this verified as described with workaround being "use most trusted box for first slave".
[14 Dec 2009 16:42] Mark Matthews
Fixed for 5.1.11, from the changelog:

- "Replication" connections (those with URLs that start with 
      jdbc:mysql:replication) now use a jdbc:mysql:loadbalance connection
      under the hood for the slave "pool". This also means that one can set
      load balancing properties such as "loadBalanceBlacklistTimeout" and
      "loadBalanceStrategy" to choose a mechanism for balancing the load and
      failover/fault tolerance strategy for the slave pool. This work was done
      in order to fix Bug#49537.
[16 Dec 2009 10:03] Tony Bedford
A 'change' entry has been added to the 5.2.11 changelog:

Replication connections, those with URLs that start with jdbc:mysql:replication, now use a jdbc:mysql:loadbalance connection for the slave pool. This means that it is possible to set load balancing properties such as loadBalanceBlacklistTimeout and loadBalanceStrategy in order to choose a mechanism for balancing the load, and failover or fault tolerance strategy for the slave pool.