When trying to balance workload between multiple servers, the
driver has to determine when it is safe to swap servers, doing
so in the middle of a transaction, for example, could cause
problems. It is important not to lose state information. For
this reason, Connector/J will only try to pick a new server when
one of the following happens:

At transaction boundaries (transactions are explicitly
committed or rolled back).

A communication exception (SQL State starting with "08") is
encountered.

When a SQLException matches conditions
defined by user, using the extension points defined by the
loadBalanceSQLStateFailover,
loadBalanceSQLExceptionSubclassFailover
or loadBalanceExceptionChecker
properties.

The third condition revolves around three new properties
introduced with Connector/J 5.1.13. It allows you to control
which SQLExceptions trigger failover.

loadBalanceExceptionChecker - The
loadBalanceExceptionChecker property is
really the key. This takes a fully-qualified class name
which implements the new
com.mysql.jdbc.LoadBalanceExceptionChecker
interface. This interface is very simple, and you only need
to implement the following method:

public boolean shouldExceptionTriggerFailover(SQLException ex)

A SQLException is passed in, and a
boolean returned. A value of true
triggers a failover, false does not.

You can use this to implement your own custom logic. An
example where this might be useful is when dealing with
transient errors when using MySQL Cluster, where certain
buffers may become overloaded. The following code snippet
illustrates this:

The code above extends
com.mysql.jdbc.StandardLoadBalanceExceptionChecker,
which is the default implementation. There are a few
convenient shortcuts built into this, for those who want to
have some level of control using properties, without writing
Java code. This default implementation uses the two
remaining properties:
loadBalanceSQLStateFailover and
loadBalanceSQLExceptionSubclassFailover.

loadBalanceSQLStateFailover - allows you
to define a comma-delimited list of
SQLState code prefixes, against which a
SQLException is compared. If the prefix
matches, failover is triggered. So, for example, the
following would trigger a failover if a given
SQLException starts with "00", or is
"12345":

loadBalanceSQLStateFailover=00,12345

loadBalanceSQLExceptionSubclassFailover -
can be used in conjunction with
loadBalanceSQLStateFailover or on its
own. If you want certain subclasses of
SQLException to trigger failover, simply
provide a comma-delimited list of fully-qualified class or
interface names to check against. For example, if you want
all SQLTransientConnectionExceptions to
trigger failover, you would specify:

While the three failover conditions enumerated earlier suit most
situations, if autocommit is enabled,
Connector/J never re-balances, and continues using the same
physical connection. This can be problematic, particularly when
load-balancing is being used to distribute read-only load across
multiple slaves. However, Connector/J can be configured to
re-balance after a certain number of statements are executed,
when autocommit is enabled. This
functionality is dependent upon the following 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 behavior that
connections with autocommit enabled are
never balanced.

loadBalanceAutoCommitStatementRegex –
the regular expression against which statements must match.
The default value, blank, matches all statements. So, for
example, using the following properties will cause
Connector/J to re-balance after every third statement that
contains the string “test”:

loadBalanceAutoCommitStatementRegex can
prove useful in a number of situations. Your application may
use temporary tables, server-side session state variables,
or connection state, where letting the driver arbitrarily
swap physical connections before processing is complete
could cause data loss or other problems. This allows you to
identify a trigger statement that is only executed when it
is safe to swap physical connections.