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?