MySQL Bugs: #51783: Load-balanced Connection throws Exception incorrectly on commit()

Bug #51783	Load-balanced Connection throws Exception incorrectly on commit()
Submitted:	5 Mar 2010 20:47	Modified:	9 Mar 2010 11:38
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.12	OS:	Any
Assigned to:	Todd Farmer	CPU Architecture:	Any

Description:
A loadbalanced Connection object with multiple open underlying physical connections will rebalance at commit() or rollback() or a communication exception without validating the existing connection.  This can be a problem when there is no pinging of the physical connections (queries starting with "/* ping */") to ensure they remain alive.  The end result is that calls to Connection.commit() may throw a SQLException.  This doesn't happen when the transaction is actually commited; it happens when the new connection is chosen and the driver attempts to set the auto-commit or transaction isolation state on the newly chosen physical connection.

How to repeat:
Test case to be provided, but:

1.  Start a load-balanced connection.
2.  Ensure it has connected to multiple servers.
3.  Kill one of the servers it is connected to, but idle
4.  Commit until the killed server is chosen and commit() throws an Exception.

Suggested fix:
Add configuration option to explicitly ping the physical server connection being made active, and if it fails, try again (up to number of configured servers).

Patch for 51783 and 51776

Attachment: diff.txt (text/plain), 19.30 KiB.

Pushed in r911.

An entry has been added to the 5.1.13 changelog:

A load balanced Connection object with multiple open underlying physical connections rebalanced on commit(), rollback(), or on a communication exception, without validating the existing connection. This caused a problem when there was no pinging of the physical connections, using queries starting with “/* ping */”, to ensure they remained alive. This meant that calls to Connection.commit() could throw a SQLException. This did not occur when the transaction was actually committed; it occurred when the new connection was chosen and the driver attempted to set the auto-commit or transaction isolation state on the newly chosen physical connection.