| 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: | |
| 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 | ||
[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.
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 ...