Suggestions on gracefully recovering from a DB crash/restart with poolCluster

[adrianblynch]

We're using the following poolCluster.getConnection('*', 'RANDOM', function (err, connection) { ... }. If a request comes into our API when the DB is down we get the POOL_NOEXIST error as expected.

Once the DB is back up, the pool of connections can't be used.

My question is, what are people doing to gracefully get things back up and running again?

A bit more detail about what we have in case it's important to the answer...

var config = require('./config')();
var log = require('./log');
var poolCluster = mysql.createPoolCluster();

config.database.main.forEach(function (serverConfig) {
    poolCluster.add(serverConfig.id, serverConfig);
});

poolCluster.on('remove', function (nodeId) {
    log.error('Removed mysql node: ' + nodeId);
    // TODO: Look for another way as this uses a private method
    if (poolCluster._findNodeIds().length === 0) {
        var errorMsg = 'No remaining mysql servers, exiting';
        log.error(errorMsg);
        throw new Error(errorMsg);
    }
});

exports.conn = {
    query: function (sql, values, cb) {
        var query = mysql.createQuery(sql, values, cb);

        poolCluster.getConnection('*', 'RANDOM', function (err, connection) {
            if (err) {
                query.on('error', function () {
                    log.error('sql error', err);
                });
                query.end(err);
                return;
            }

            query.once('end', function () {
                connection.release();
            });

            connection.query(query);
        });

        return query;
    }
};

[dougwilson]

Have you played around with the restoreNodeTimeout option in PoolCluster (https://github.com/felixge/node-mysql#poolcluster-option)? It was added in version 2.6.0

[adrianblynch]

I have not! But I will!

Will report back with what I find.

Thanks Doug.

[dougwilson]

Hi @adrianblynch , I haven't really heard back. I'm going to close this issue, but feel free to reply and I can always re-open if necessary :)

[tilleps]

Having running into this myself, restoreNodeTimeout appears to have done the trick. However, it would be nice to have a mechanism to manually restore the node, something like:

poolCluster.restore(function(cb) {});

[tilleps]

Is there any negative implications to setting restoreNodeTimeout to a really node value like 1(ms)?