This guide will cover:

• The load balancing and high availability concepts in Mariadb Connector/J for version before 3.0
• The different options.

Failover and high availability were introduced in 1.2.0.

Load balancing and failover distinction

Failover occurs when a connection to a primary database server fails and the connector opens up a connection to another database server.
For example, server A has the current connection. After a failure (server crash, network down …) the connection will switch to another server (B).

Load balancing allows load (read and write) to be distributed over multiple servers.

Replication cluster type

In MariaDB (and MySQL) replication, there are 2 different replication roles:

• Master role: Database server that permits read and write operations
• Slave role: Database server that permits only read operations

This document describes configuration and implementation for 3 types of clusters:

• Multi-Master replication cluster. All hosts have a master replication role. (example: Galera)
• Master/slaves cluster: one host has the master replication role with multiple hosts in slave replication role.
• Hybrid cluster: multiple hosts in master replication role with multiple hosts in slave replication role.

Load balancing implementation

Random picking

When initializing a connection or after a failed connection, the connector will attempt to connect to a host with a certain role (slave/master). The connection is selected randomly among the valid hosts. Thereafter, all statements will run on that database server until the connection will be closed (or fails).

The load-balancing will includes a pooling mechanism. Example: when creating a pool of 60 connections, each one will use a random host. With 3 master hosts, the pool will have about 20 connections to each host.

Master/slave distributed load

For a cluster composed of masters and slaves on connection initialization, there will be 2 underlying connections: one with a master host, another with a slave host. Only one connection is used at a time.
For a cluster composed of master hosts only, each connection has only one underlying connection.
The load will be distributed due to the random distribution of connections..

Master/slave connection selection

It’s the application that has to decide to use master or slave connection (the master connection is set by default).
Switching the type of connection is done by using JDBC connection.setReadOnly(boolean readOnly) method. Setting read-only to true will use the slave connection, false, the master connection.

Example in standard java:

connection = DriverManager.getConnection("jdbc:mysql:replication://master1,slave1/test");
stmt = connection.createStatement();
stmt.execute("SELECT 1"); // will execute query on the underlying master1 connection
connection.setReadOnly(true);
stmt.execute("SELECT 1"); // will execute query on the underlying slave1 connection

Some frameworks render this kind of operation easier, as for example Spring @transactionnal readOnly parameter (since spring 3.0.1). In this example, setting readOnly to false will call the connection.setReadOnly(false) and therefore use the master connection.

@Autowired
private EntityManager em;

@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
public void createContacts() {
  Contact contact1 = new Contact();
  contact1.setGender("M");
  contact1.setName("JIM");
  em.persist(contact1);
}

Generated Spring Data repository objects use the same logic: the find* method will use the slave connection, other use master connection without having to explicitly set that for each method.

On a cluster with master hosts only, the use of connection.setReadOnly(true) does not change the connection, but if the database version is 10.0.0 or higher, the session is set to readOnly if option assureReadOnly is set to true, which means that any write query will throw an exception.

Failover behaviour

Basic failover

When no failover/high availability parameter is set, the failover support is basic. Before executing a query, if the connection with the host is discarded, the connection will be reinitialized if parameter “autoReconnect” is set to true.

Standard failover

When a failover/high availability parameter is set. Check the configuration section for an overview on how to set the parameters.

There can be multiple fail causes. When a failure occurs many things will be done:

• The fail host address will be put on a blacklist (shared by JVM). This host will not be used for the amount of time defined by the “loadBalanceBlacklistTimeout” parameter (default to 50 seconds). The only time a blacklisted address can be used is if all host of the same type (master/slave) are blacklisted.
• The connector will check the connection (with the mysql ping protocol). If the connection is back, is not read-only, and is in a transaction, the transaction will be rollbacked (there is no way to know if the last query has been received by the server and executed).
• If the failure relates to a slave connection
  • If the master connection is still active, the master connection will be used immediately. The query that was read-only will be relaunched and the connector will not throw any exception. A "failover" thread will be launched to attempt to reconnect a slave host. (if the query was a prepared query, this query will be re-prepared before execution)
  • If the master connection is not active, the driver will attempt to create a new master or slave connection with a connection loop. if any connection is found, the query will be relaunched, if not, an SQLException with sqlState like “08XXX” will be thrown.
• If the failure relates to a master connection, the driver will attempt to create a new master connection with a connection loop, so the connection object will be immediately reusable.<br>
  • on failure, an SQLException with be thrown with SQLState "08XXX". If using a pool, this connection will be discarded.
  • on success,
    • if possible query will be relaunched without throwing error (if was using a slave connection, or was a SELECT query not in a transaction for example).
    • if not possible, an SQLException with be thrown with SQLState "25S03".
• When throwing an SQLException with SQLState "08XXX", the connection will be marked as closed.
• A “failover” thread will be launched to attempt to reconnect failing connection if connection is not closed.

It’s up to the application to take measures to handle SQLException. See details in application concerns.

1. Connection loop When initializing a connection or after a failure, the driver will launch a connection loop the only case when this connection loop will not be executed is when the failure occurred on a slave with an active master. This connection loop will try to connect to a valid host until finding a new connection or until the number of connections exceed the parameter “retriesAllDown” value (default to 120).

This loop will attempt to connect sequentially to hosts in the following order:

For a master connection :

• random connect to master host not blacklisted
• random connect to master blacklisted

For a slave connection :

• random connect to slave host not blacklisted
• random connect to master host not blacklisted (if no active master connection)
• random connect to slave blacklisted
• random connect to master host blacklisted (if no active master connection) The sequence stops as soon as all the underlying needed connections are found. Every time an attempt fails, the host will be blacklisted. If after an entire loop a master connection is missing, the connection will be marked as closed.

Additional threads

Failover reconnection threads

A thread pool is created in case of a master/slave cluster, the size is defined according to the number of connection. After a failure on a slave connection, readonly operations are temporary executed on the master connection. Some “failover threads” will try to reconnect the failed underlying connections. When a new slave connection is retrieved, this one will be immediately used if connection was still in read-only mode.
More details in Failover loop threads.

Connection validation thread

An additional thread is created when setting the option "validConnectionTimeout". This thread will very that connections are all active. This is normally done by pool that call Connection.isValid().

Application concerns

When a failover happen a SQLException with sqlState like "08XXX" or "25S03" may be thrown.

Here are the different connection error codes:

A connection pool will detect connection error in SQLException (SQLState begin with "08"), and this connection will be discarded from pool.

When a failover occur the connector cannot know if the last request has been received by the database server and executed. Applications may have failover design to handle these particular cases: If the application was in autoCommit mode (not recommended), the last query may have been executed and committed. The application will have no possibility to know that but the application will be functional. If not in autoCommit mode, the query has been launched in a transaction that will not be committed. Depending of what caused the exception, the host may have the connection open on his side during a certain amount of time. Take care of transaction isolation level that may lock too much rows.

Configuration

(See About MariaDB java connector for all connection parameters) JDBC connection string format is :

jdbc:(mysql|mariadb):[replication:|sequential:|loadbalance:|aurora:]//<hostDescription>[,<hostDescription>...]/[database][?<key1>=<value1>[&<key2>=<value2>]...]

The standard option "connectTimeout" defines the socket connection timeout. By default, this option is set to 0 (no timeout).
Since there are many servers, setting this option to a small amount of time make sense.
During the connection loop phase, the driver will try to connect to the server sequentially until the creation of an active connection. Set this option to a small value (such as 2000ms - to be set according to your environment) which will permit rejecting a faulty server quickly.

Failover and Load Balancing Modes

Each parameter corresponds to a specific use case:

Failover and Load Balancing Parameters

Specifics for Amazon Aurora

Amazon Aurora is a Master/Slaves cluster composed of one master instance with a maximum of 15 slave instances. Amazon Aurora includes automatic promotion of a slave instance in case of the master instance failing. The MariaDB connector/J implementation for Aurora is specific to handle this automatic failover.

To permit development/integration on a single-node cluster, only one host can be defined. In this case, the driver behaves as for the configuration failover.

Aurora failover implementation

Aurora failover management steps :

• Instance A is in write replication mode, instance B and C are in read replication mode.
• Instance A fails.
• Aurora detects A failure, and promote instance B in write mode. Instance C will change his master to use B.
• Cluster end-point will change to instance B end-point.
• Instance A will recover and be in read replication mode.

Aurora configuration

Aurora endpoints and discovery

Every aurora instance has a specific endpoint, i.e. an URL that identify the host. Those endpoints look like `xxx.yyy.zzz.rds.amazonaws.com`.

There is another endpoint named "cluster endpoint" (format `xxx.cluster-yyy.zzz.rds.amazonaws.com`) which is assigned to the current master instance and will change when a new master is promoted.

In versions before 1.5.1, cluster endpoint use was discouraged, since when a failover occurs, this cluster endpoint can point for a limited time to a host that isn't the current master any more. The old recommendation was to list all specific end-points. This kind of url string will still work, but now, recommended url string has to use only cluster endpoint.

Driver will automatically discover master and slaves of this cluster from current cluster end-point during connection time. This permits adding new replicas to the cluster instance which will be discovered without changing driver configuration.

This discovery appends at connection time, so if you are using pool framework, check if this framework as a property that controls the maximum lifetime of a connection in the pool, and set a value to avoid infinite lifetime. When this lifetime is reached, pool will discard the current connection, and create a new one (if needed). New connections will use the new replicas. (If connections are never discarded, new replicas will begin to be used only when a failover occur)

JDBC connection string

The implementation is activated by specifying the “aurora” failover parameter. Recommended connection string is using cluster end-point:

jdbc:(mysql|mariadb):aurora://[clusterInstanceEndPoint[:port]]/[database][?<key1>=<value1>[&<key2>=<value2>]...]

Before driver version 1.5.1, connection string has to list all end-points:

jdbc:(mysql|mariadb):aurora://[instanceEndPoint[:port]][,instanceEndPoint[:port]...]/[database][?<key1>=<value1>[&<key2>=<value2>]...]

If setting many endpoints, the replication role of each instance must not be defined for Aurora, because the role of each instance changes over time. The driver will check the instance role after connection initialization.

Example of connection string

    jdbc:mysql:aurora://cluster.cluster-xxxx.us-east-1.rds.amazonaws.com/db

Another difference is the option "socketTimeout" that defaults to 10 seconds, meaning that - if not changed - queries exceeding 10 seconds will throw exceptions.

Aurora connection loop

When searching for the master instance and connecting to a slave instance, the connection order will be:

• Every Aurora instance knows the hostname of the current master. If the host has been described using their instance endpoint, that will permit knowing the master instance and connecting directly to it.
• If this isn’t the current master (because using IP, or possibly after a failover between step 2 and 3), the loop will connect randomly the other not blacklisted instance (minus the current slave instance).
• Connect randomly to a blacklisted instance.

When searching for a slave instance, the loop connection order will be:

• random not blacklisted instances (excluding the current host if connected).
• random blacklisted instances . The loop will retry until the connections are found or the value of the “retriesAllDown” parameter is exceeded.

Aurora master verification

Without any query during the time defined by the validConnectionTimeout parameter (defaults to 120s) and if not set to 0, a verification will be done that the replication role of the underlying connections hasn't changed.

Aurora connection validation thread

Aurora as a specific connection validation thread implementation. Since the role of each instance can change over time, this will validate that connections are active AND roles have not changed.