MySQL Bugs: #55723: Support load-balancing of connections where auto-commit is enabled

Bug #55723	Support load-balancing of connections where auto-commit is enabled
Submitted:	3 Aug 2010 21:54	Modified:	12 Aug 2010 14:38
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S4 (Feature request)
Version:	5.1.13	OS:	Any
Assigned to:	Todd Farmer	CPU Architecture:	Any

Description:
Connector/J's load-balancing functionality currently allows the following events (only) to trigger failover:

* transaction commit/rollback
* CommunicationExceptions
* Matches to user-defined Exceptions via the loadBalanceSQLStateFailover, loadBalanceSQLExceptionSubclassFailover or loadBalanceExceptionChecker properties.

This means that connections where auto-commit is enabled are not balanced, except for Exceptions, and this is particularly problematic in the case of distribution of read-only work across slaves in a replication deployment.

How to repeat:
Deploy in a load-balanced environment, use Connection.setAutoCommit(true), observe no load-balancing.

Suggested fix:
Add support for load-balancing while auto-commit is enabled.

Added ability to load-balance while auto-commit is enabled.  This introduces two new properties:
	  
loadBalanceAutoCommitStatementThreshold - defines the number of matching statements which will trigger the driver to (potentially) swap physical server connections.  The default value (0) retains the previously-established behavior that connections with auto-commit enabled are never balanced.

loadBalanceAutoCommitStatementRegex - the regular expression against which statements must match.  The default value (blank) matches all statements.

Load-balancing will be done after the statement is executed, before control is returned to the application.  If rebalancing fails, the driver will silently swallow the resulting Exception (as the statement itself completed successfully).

Patched in r960 and r961.

An entry has been added to the 5.1.14 changelog:

Connector/J's load-balancing functionality only allowed the following events to trigger failover:

Transaction commit/rollback

CommunicationExceptions

Matches to user-defined Exceptions via the loadBalanceSQLStateFailover, loadBalanceSQLExceptionSubclassFailover or loadBalanceExceptionChecker properties.

This meant that connections where auto-commit was enabled were not balanced, except for Exceptions, and this was problematic in the case of distribution of read-only work across slaves in a replication deployment.

The ability to load-balance while auto-commit is enabled has now been added to Connector/J. This introduces two new properties:

loadBalanceAutoCommitStatementThreshold - defines the number of matching statements which will trigger the driver to (potentially) swap physical server connections. The default value (0) retains the previously-established behavior that connections with auto-commit enabled are never balanced.

loadBalanceAutoCommitStatementRegex - the regular expression against which statements must match. The default value (blank) matches all statements.

Load-balancing will be done after the statement is executed, before control is returned to the application. If rebalancing fails, the driver will silently swallow the resulting Exception (as the statement itself completed successfully).