MySQL Connector/J 5.1 Developer Guide
Connector/J provides a useful load-balancing implementation for
MySQL Cluster or multi-source deployments, as explained in
Section 8.2, “Configuring Load Balancing with Connector/J”
and Support for Multiple-Source Replication Topographies.
As of Connector/J 5.1.12, this same implementation is used for
balancing load between read-only replicas with
ReplicationDriver
.
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 SQLException
s 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:
public class NdbLoadBalanceExceptionChecker extends StandardLoadBalanceExceptionChecker { public boolean shouldExceptionTriggerFailover(SQLException ex) { return super.shouldExceptionTriggerFailover(ex) || checkNdbException(ex); } private boolean checkNdbException(SQLException ex){ // Have to parse the message since most NDB errors // are mapped to the same DEMC. return (ex.getMessage().startsWith("Lock wait timeout exceeded") || (ex.getMessage().startsWith("Got temporary error") && ex.getMessage().endsWith("from NDB"))); } }
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:
loadBalanceSQLExceptionSubclassFailover=java.sql.SQLTransientConnectionException
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 replicas. 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”:
loadBalanceAutoCommitStatementThreshold=3 loadBalanceAutoCommitStatementRegex=.*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.