9.1 Configuring Server Failover for Connections Using JDBC

MySQL Connector/J supports server failover. A failover happens when connection-related errors occur for an underlying, active connection. The connection errors are, by default, propagated to the client, which has to handle them by, for example, recreating the working objects (Statement, ResultSet, etc.) and restarting the processes. Sometimes, the driver might eventually fall back to the original host automatically before the client application continues to run, in which case the host switch is transparent and the client application will not even notice it.

A connection using failover support works just like a standard connection: the client does not experience any disruptions in the failover process. This means the client can rely on the same connection instance even if two successive statements might be executed on two different physical hosts. However, this does not mean the client does not have to deal with the exception that triggered the server switch.

The failover is configured at the initial setup stage of the server connection by the connection URL (see explanations for its format here):

jdbc:mysql://[primary host][:port],[secondary host 1][:port][,[secondary host 2][:port]]...[/[database]]»
[?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

The host list in the connection URL comprises of two types of hosts, the primary and the secondary. When starting a new connection, the driver always tries to connect to the primary host first and, if required, fails over to the secondary hosts on the list sequentially when communication problems are experienced. Even if the initial connection to the primary host fails and the driver gets connected to a secondary host, the primary host never loses its special status: for example, it can be configured with an access mode distinct from those of the secondary hosts, and it can be put on a higher priority when a host is to be picked during a failover process.

The failover support is configured by the following connection properties (their functions are explained in the paragraphs below):

• failOverReadOnly

• secondsBeforeRetrySource

• queriesBeforeRetrySource

• retriesAllDown

• autoReconnect

• autoReconnectForPools

Configuring Connection Access Mode

As with any standard connection, the initial connection to the primary host is in read/write mode. However, if the driver fails to establish the initial connection to the primary host and it automatically switches to the next host on the list, the access mode now depends on the value of the property failOverReadOnly, which is “true” by default. The same happens if the driver is initially connected to the primary host and, because of some connection failure, it fails over to a secondary host. Every time the connection falls back to the primary host, its access mode will be read/write, irrespective of whether or not the primary host has been connected to before. The connection access mode can be changed any time at runtime by calling the method Connection.setReadOnly(boolean), which partially overrides the property failOverReadOnly. When failOverReadOnly=false and the access mode is explicitly set to either true or false, it becomes the mode for every connection after a host switch, no matter what host type are being connected to; but, if failOverReadOnly=true, changing the access mode to read/write is only possible if the driver is connecting to the primary host; however, even if the access mode cannot be changed for the current connection, the driver remembers the client's last intention and, when falling back to the primary host, that is the mode that will be used. For an illustration, see the following successions of events with a two-host connection.

• Sequence A, with failOverReadOnly=true:

  1. Connects to primary host in read/write mode

  2. Sets Connection.setReadOnly(true); primary host now in read-only mode

  3. Failover event; connects to secondary host in read-only mode

  4. Sets Connection.setReadOnly(false); secondary host remains in read-only mode

  5. Falls back to primary host; connection now in read/write mode

• Sequence B, with failOverReadOnly=false

  1. Connects to primary host in read/write mode

  2. Sets Connection.setReadOnly(true); primary host now in read-only mode

  3. Failover event; connects to secondary host in read-only mode

  4. Set Connection.setReadOnly(false); connection to secondary host switches to read/write mode

  5. Falls back to primary host; connection now in read/write mode

The difference between the two scenarios is in step 4: the access mode for the secondary host in sequence A does not change at that step, but the driver remembers and uses the set mode when falling back to the primary host, which would be read-only otherwise; but in sequence B, the access mode for the secondary host changes immediately.

Configuring Fallback to Primary Host

As already mentioned, the primary host is special in the failover arrangement when it comes to the host's access mode. Additionally, the driver tries to fall back to the primary host as soon as possible by default, even if no communication exception occurs. Two properties, secondsBeforeRetrySource and queriesBeforeRetrySource, determine when the driver is ready to retry a reconnection to the primary host (the Source in the property names stands for the primary host of our connection URL, which is not necessarily a source host in a replication setup):

• secondsBeforeRetrySource determines how much time the driver waits before trying to fall back to the primary host

• queriesBeforeRetrySource determines the number of queries that are executed before the driver tries to fall back to the primary host. Note that for the driver, each call to a Statement.execute*() method increments the query execution counter; therefore, when calls are made to Statement.executeBatch() or if allowMultiQueries or rewriteBatchStatements are enabled, the driver may not have an accurate count of the actual number of queries executed on the server. Also, the driver calls the Statement.execute*() methods internally in several occasions. All these mean you can only use queriesBeforeRetrySource only as a coarse specification for when to fall back to the primary host.

In general, an attempt to fallback to the primary host is made when at least one of the conditions specified by the two properties is met, and the attempt always takes place at transaction boundaries. However, if auto-commit is turned off, the check happens only when the method Connection.commit() or Connection.rollback() is called. The automatic fallback to the primary host can be turned off by setting simultaneously secondsBeforeRetrySource and queriesBeforeRetrySource to “0”. Setting only one of the properties to “0” only disables one part of the check.

Configuring Reconnection Attempts

When establishing a new connection or when a failover event occurs, the driver tries to connect successively to the next candidate on the host list. When the end of the list has been reached, it restarts all over again from the beginning of the list; however, the primary host is skipped over, if (a) NOT all the secondary hosts have already been tested at least once, AND (b) the fallback conditions defined by secondsBeforeRetrySource and queriesBeforeRetrySource are not yet fulfilled. Each run-through of the whole host list, (which is not necessarily completed at the end of the host list) counts as a single connection attempt. The driver tries as many connection attempts as specified by the value of the property retriesAllDown.

Seamless Reconnection

Although not recommended, you can make the driver perform failovers without invalidating the active Statement or ResultSet instances by setting either the parameter autoReconnect or autoReconnectForPools to true. This allows the client to continue using the same object instances after a failover event, without taking any exceptional measures. This, however, may lead to unexpected results: for example, if the driver is connected to the primary host with read/write access mode and it fails-over to a secondary host in read-only mode, further attempts to issue data-changing queries will result in errors, and the client will not be aware of that. This limitation is particularly relevant when using data streaming: after the failover, the ResultSet looks to be alright, but the underlying connection may have changed already, and no backing cursor is available anymore.

Configuring Server Failover Using JDBC with DNS SRV

See Section 6.14, “Support for DNS SRV Records” for details.