MySQL Bugs: #56429: Failover functionality establishes useless connections to non-primary servers

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

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.

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?

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

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.