MySQL Server Failover Support

[niveathika]

Summary

Currently, the ballerina SQL client can only connect to one MySQL server. The user has to handle connection errors by switching to the secondary replicated database or retrying. We can support MySQL server failovers so that the user does not need to maintain multiple clients to switch between secondary databases.

Goals

Allow ballerina client to connect to secondary databases in case of connection errors using the MySQL server Failover feature.

Motivation

The user can easily connect between database replicas without using complex logic.

Description

FailoverConfig configuration will be introduced to mysql:Options.

# Configuration for failover servers
#
# + host - Hostname of the secondary database to be connected
# + port - Port of the secondary database to connect
public type FailoverServer record {|
    string host;
    int port;
|};

# Configuration to be used for Server Failover.
#
# + secondaries - Array of host & port tuple for the secondary databases
# + timeBeforeRetry - Time the driver waits before trying to fall back to the primary host
# + queriesBeforeRetry - Number of queries that are executed before the driver tries to fall back to the primary host
# + failoverReadOnly - Open connection to secondary host with READ ONLY mode.
public type FailoverConfig record {|
    FailoverServer[] failoverServers;
    int timeBeforeRetry?;
    int queriesBeforeRetry?;
    boolean failoverReadOnly = true;
|};

public type Options record {|
    ....
    FailoverConfig failoverConfig?;
|};

Failover configuration usage will be,

Options option = {
    failoverConfig: {
        failoverServers: [
            {
                host: "localhost"
                port: 5506
            },
            {
                host: "localhost",
                port: 3305
            }
        ],
        timeBeforeRetry: 10,
        queriesBeforeRetry: 10,
        failoverReadOnly: false
    }
};
Client dbClient = check new (host, "root", "111", "mydb", 4406, option);

Testing

Since a replication MySQL server is needed as a prerequisite for testing, this feature is manually tested. A docker-based MySQL replication is used for testing. Prerequisite Reference

After the docker images are set up following code can be used for testing,

import ballerina/test;
import ballerina/sql;
import ballerina/io;
import ballerina/lang.runtime;

@test:Config {
    groups: ["query-1"]
}
function queryReplica() returns error? {
    sql:ParameterizedQuery sqlQuery = `SELECT * from Code;`;

    Options option = {
        failoverConfig: {
            failoverServers: [
                {
                    host: "localhost",
                    port: 5506
                }
            ],
            timeBeforeRetry: 10,
            queriesBeforeRetry: 10,
            failoverReadOnly: false
        }
    };

    Client dbClient = check new (host, "root", "111", "mydb", 4406, option);
    int code = check dbClient->queryRow(sqlQuery);

    test:assertEquals(code, 100);

    io:println("Sleep");
    runtime:sleep(30);

    code = check dbClient->queryRow(sqlQuery);
    test:assertEquals(code, 100);
}

While the program is sleeping master node should be shut down to verify the failover.

Reference

MySQL Server Failover

[BuddhiWathsala]

LGTM. The only concern I have is the name Option is too generic. Do we follow a similar naming convention in other DB modules?. For instance, in I/O we follow a specific naming for each option as in here.

[niveathika]

There is only one option as there is only one SQL client, if we introduce more options, the init functions will have a lot of optional records configurations.

[anupama-pathirage]

Shouldn't we allow to define the access mode for failover? Whether the failover is readonly or read/write both?

[niveathika]

@anupama-pathirage Seems I missed it in the initial proposal. I have now updated it to include the failOveryReadOnly flag.

[anupama-pathirage]

Few suggestions on naming convenntion.

1. Change ServerFailover record name to FailoverConfig - Seems it is matching with current standard lib conventions like http:CacheConfig , http:RetryConfig` etc.

2. Instead of [string, int] [] define a record as FailoverServer and use record arrays?

3. Change the field name of Options as failoverConfig instead of failover?

4. Change the failOverReadOnly to failoverReadOnly

Definitions

public type FailoverConfig record {|
    FailoverServer[] failoverServers;
    int timeBeforeRetry?;
    int queriesBeforeRetry?;
    boolean failoverReadOnly = true;
|};

public type FailoverServer record {|
    string host;
    int port;
|};

public type Options record {|
    FailoverConfig failoverConfig?;
|};

Usage

Options option = {
    failoverConfig: {
        failoverServers: [
            {
                host: "localhost",
                port: 5506
            },
            {
                host: "x.x.x.x", 
                port: 5506
            }
        ],
        queriesBeforeRetry: 100,
        timeBeforeRetry: 100,
        failOverReadOnly: true
    }
};

[niveathika]

@anupama-pathirage I have updated the proposal to include all the naming streamlining as suggested.

[niveathika]

As per the offline discussion with @anupama-pathirage, I am going ahead with the latest design.

[niveathika]

Accepted proposal added in https://github.com/ballerina-platform/module-ballerinax-mysql/pull/268