Pool.query doesn't connect to a new writer in the event of a failover in a cluster

[cwatton-wolf]

Hi there,

Sorry in advance for any missing or irrelevant information, first time creating an issue.

Issue

We've identified that when connecting to a clustered database, 1 writer and multiple readers, behind a FQDN that will update in the event of a failover of the current writer the pool.query method will keep trying to use existing connections to the database after receiving the error Error: The MySQL server is running with the --read-only option so it cannot execute this statement until the application is restarted. However, using pool.getConnection, connection.query and connection.release directly will, after a couple of failed attempts, successfully connect to the new writer

Testing

To test this I've been running a two instance cluster in AWS RDS with a basic table that allows me to keep writing to it with the following code. (please excuse my testing code here, I was testing a few scenarios and this was pretty dirty)

// Get the client
import mysql from 'mysql2/promise';

// Create the connection pool. The pool-specific settings are the defaults
const pool = mysql.createPool({
    host: 'redacted',
    user: 'redacted',
    password: 'redacted',
    database: 'test_area',
    connectionLimit: 15,
});

const pool2 = mysql.createPool({
    host: 'redacted',
    user: 'redacted',
    password: 'redacted',
    database: 'test_area',
    connectionLimit: 15,
});

const numberOfConnections = 15

function sleep(ms) {
    return new Promise((resolve) => {
        setTimeout(resolve, ms);
    });
}

let loop = true;
while (loop) {
    for (let index = 0; index < numberOfConnections; index++) {
        console.log(`loop number ${index}`)
        // Testing with pool.getConnection() and connection.query()
        try {
            const conn = await pool.getConnection();            
            const results = await conn.query(`INSERT INTO test_table VALUES (NULL, ${index}, CURRENT_TIMESTAMP);`);
            console.log(results[0]);
            conn.release()
        } catch (err) {
            console.log('Failed to execute query: ', err);
        }

       // Testing with pool.query()
        try {
            const results = await pool2.query(`INSERT INTO test_table VALUES (NULL, ${index}, CURRENT_TIMESTAMP);`);
            console.log(results[0]);
        } catch (err) {
            console.log('Failed to execute query: ', err);
        }

        await sleep(5000)
    }

}

Notes

• One thing I have identified in my local testing is that pool.query when experiencing the Error: The MySQL server is running with the --read-only option so it cannot execute this statement will not create a new connection and the _internalId of the connection used will remain the same whereas connection.query seems to destroy the connection and create a new one after that failure. This I've only confirmed by logging out the _interalId of the connections
• The other is that pool.query seems to use the PoolConnection Class but connection.query uses the PromisePoolConnection Class.