MySQL Bugs: #32877: loadBalanceStrategy=bestResponseTime hangs forever on a single unreachable host

Bug #32877	loadBalanceStrategy=bestResponseTime hangs forever on a single unreachable host
Submitted:	30 Nov 2007 15:40	Modified:	25 Feb 2008 12:14
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1-20071127	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If loadBalanceStrategy=bestResponseTime is used and an unreachable host is attempted to connect to, conn/j will repeatedly try to connect to it (probably because response time is not initialized...). 

How to repeat:
Connection string:

jdbc:mysql:loadbalance://192.168.1.5:3306,localhost:3306/test?failOverReadOnly=false&loadBalanceStrategy=bestResponseTime&autoReconnectForPools=true&connectTimeout=100

.1.5 is down in this case. 

if bestResponseTime is not used, connectTimeout works properly, otherwise JVM attempts reconnects to same host every 100ms or so. 

Suggested fix:
do not retry same host in case of bestResponseTime strategy, especially if it fails.

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/39010

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/39227

A note has been added to the 5.1.6 changelog: 

Load balancing connection using best response time would incorrectly "stick" to hosts that were down when the connection was first created.

We solve this problem with a black list that is used during the picking of new hosts. If the black list ends up including all configured hosts, the driver will retry for a configurable number of times (the retriesAllDown configuration property, with a default of 120 times), sleeping 250ms between attempts to pick a new connection.

We've also went ahead and made the balancing strategy extensible. To create a new strategy, implement the interface com.mysql.jdbc.BalanceStrategy (which also includes our standard "extension" interface), and tell the driver to use it by passing in the class name via the loadBalanceStrategy configuration property.