How to programmatically detect auto failover on AWS mysql aurora?

Question

Our stack is nodejs with MySQL we're using MySQL connections pooling our MySQL database is managed on AWS aurora . in case of auto failover the master DB is changed the hostname stays the same but the connections inside the pool stays connected to the wrong DB. The only why we found in order to reset the connection is to roll our servers.

this is a demonstration of a solution I think could solve this issue but I prefer a solution without the set interval

const mysql = require('mysql');


class MysqlAdapter {
    constructor() {
        this.connectionType = 'MASTER';
        this.waitingForAutoFaileOverSwitch = false;
        this.poolCluster = mysql.createPoolCluster();
        this.poolCluster.add(this.connectionType, {
            host: 'localhost',
            user: 'root',
            password: 'root',
            database: 'app'
        });

        this.intervalID = setInterval(() => {
            if(this.waitingForAutoFaileOverSwitch) return;
            this.excute('SHOW VARIABLES LIKE \'read_only\';').then(res => {
                // if MASTER is set to read only is on then its mean a fail over is accoure and swe need to switch all connection in poll to secondry database
                if (res[0].Value === 'ON') {
                    this.waitingForAutoFaileOverSwitch = true
                    this.poolCluster.end(() => {
                        this. waitingForAutoFaileOverSwitch = false
                    });
                };
            });
        }, 5000);

    }
    async excute(query) {
        // delay all incoming request until pool kill all connection to read only database
        if (this.waitingForAutoFaileOverSwitch) {
            return new Promise((resolve, reject) => {
                setTimeout(() => {
                    this.excute(query).then(res => {
                        resolve(res);
                    });
                }, 1000);
            });
        }
        return new Promise((resolve, reject) => {

            this.poolCluster.getConnection(this.connectionType, (err, connection) => {
                if (err) {
                    reject(err);
                }
                connection.query(query, (err, rows) => {
                    connection.release();
                    if (err) {
                        reject(err);
                    }
                    resolve(rows);
                });
            });
        });
    }
}



const adapter = new MysqlAdapter();

Is there any other programmable way to reset the connection inside the pool?

Is there any notification we can listing to In case of auto-failover?

Answer 1

Instead of manually monitoring the DB health, as you have also hinted, ideally we subscribe to failover events published by AWS RDS Aurora.

There are multiple failover events listed here for the DB cluster: Amazon RDS event categories and event messages

You can use and test to see which one of them is the most reliable in your use case for triggering poolCluster.end() though.