Bug #56429 Failover functionality establishes useless connections to non-primary servers
Submitted: 31 Aug 2010 22:36 Modified: 28 Sep 2010 10:56
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.13 OS:Any
Assigned to: Tony Bedford CPU Architecture:Any

[31 Aug 2010 22:36] Todd Farmer
Description:
When using Connector/J configured for failover (jdbc:mysql://host1,host2,... URLs), the non-primary servers re-balance when the transactions on the master were committed or rolled-back.

How to repeat:
See above.

Suggested fix:
Don't open new connections to non-primary servers when transactions on primary complete.
[22 Sep 2010 6:42] Gary Feltham
Found the same issue when testing failover on 2 hosts. The primary was running, the secondary was not.

A connection was made to the primary server correctly on the first call to DriverManager.getConnection. Noticed that, FailoverConnectionProxy#pickNewConnection established the proxy.

The resultant connection was called to setAutoCommit(false) and a commit() made. 

The stack trace resulted:
Thread [main] (Suspended (breakpoint at line 166 in FailoverConnectionProxy))	
	FailoverConnectionProxy.pickNewConnection() line: 166	
	FailoverConnectionProxy(LoadBalancingConnectionProxy).invoke(Object, Method, Object[]) line: 507	
	FailoverConnectionProxy.invoke(Object, Method, Object[]) line: 127	
	$Proxy7.commit() line: not available	
	... rest removed as internal code

Seemingly, the commit is run on the connection and then a new call to pickNewConnection is called within the finally block of the LoadBalancingConnectionProxy.

This results in a failOver() being called and for my non available server, an endless attempt to connect to the secondary server.

Should the fix be not to call return super.invoke(proxy, method, args) for a commit or rollback and to implement the logic in the FailoverConnectionProxy class?
[22 Sep 2010 19:06] Todd Farmer
Patch was pushed to tree a while back, but this bug report was not updated.  It will be available in 5.1.14, and is already part of recent nightly snapshot builds:

http://downloads.mysql.com/snapshots.php
[28 Sep 2010 10:56] Tony Bedford
An entry has been added to the 5.1.14 changelog:

When using Connector/J configured for failover (jdbc:mysql://host1,host2,... URLs), the non-primary servers re-balanced when the transactions on the master were committed or rolled-back.