Mysql: Pool is Closed

[clethrill]

I'm running an AWS RDS instance running MySQL, I access it through AWS Lambda functions.

I've made a small wrapper class to handle database queries.

module.exports = class DatabaseConnection {

    constructor(poolNumber) {
        if (typeof poolNumber !== "number") {
            console.log("no pool");
            this.Connection = mysql.createConnection({
                host: process.env.Hostname,
                database: process.env.Database,
                user: process.env.User,
                password: process.env.Password
            });
        }
        else {
            console.log("pool");
            this.Connection = mysql.createPool({
                connectionLimit: poolNumber,
                host: process.env.Hostname,
                database: process.env.Database,
                user: process.env.User,
                password: process.env.Password
            });

            this.Connection.on('acquire', function(connection) {
                console.log( 'Connection %d acquired', connection.threadId);
            });
            this.Connection.on('enqueue', function () {
                console.log('Waiting for available connection slot');
            });
            this.Connection.on('release', function (connection) {
                console.log('Connection %d released', connection.threadId);
            });
        }
    }

    query(params, callback) {
        console.log(params);
        this.Connection.query(params.query, params.values, callback);
    }

    terminate() {
        console.log("Terminating DB Connection");
        this.Connection.end();
    }
}

I then call it like

var Step = require('step');
var DatabaseConnection = require('./mysql');
var db = new DatabaseConnection(5); //make pool of 5 connections

function exit(err, success) {
    db.terminate();
    if (err) console.log(err);

    process.exit(success);
}

Step(
    function sendQueries() {
        var params = {
            query: "select * from mytable where column = ?",
            values: [value]
        }
        db.query(params, this);
    },
    function receiveResults(err, data) {
        if (err) exit(err, 1);

        // do whatever I want to do with the data
        processData(data, this);
    },
    function finish(err, data) {
        if (err) exit(err, 1);
        exit(null, 0);
    }
);

Note that I used the Step library to handle the asynchronous calls so db.terminate() definitely does not get called before the queries finish coming back as can be check by the logs produced by my class.

Now in my processData it goes off and calls other lambda function, those could look very similar to this, they generally, select some data, modify it a bit, then update or insert new data back into the database.

What is happening sometimes, and only sometimes I can't seem to work out a factor causing it, is the functions all start returning errors back saying pool is closed.

Node version = 6.10
mysql version = 2.13.0

[dougwilson]

I'm not very familiar with AWS Lambda, so cannot say what is going on there, but you can get that error if you end up calling the pool end multiple times, which I do see you doing in your code on an error condition.

In your Step code, the middle function

    function receiveResults(err, data) {
        if (err) exit(err, 1);

        // do whatever I want to do with the data
        processData(data, this);
    },

Will call exit on an error, but still proceed to processData. The next function in your Step will then call exit a second time.

[clethrill]

Calling exit would stop the function progressing any further.

I ended up finding the reasoning which was nothing to do with this package but how AWS Lambda functions.

The general layout of a lambda function is

//include libraries
var library = require('library');
exports.handler = function(event, context, callback) {
    //do stuff
    callback(if_there_is_an_error, data);
}

What I was doing at the top was something like

var DatabaseConnection = require('./mysql');
var db = new DatabaseConnection(5); //make pool of 5 connections

at the top in global scope outside of module.exports. What this caused was seemingly lambda executes that global space once, and everytime I invoke the function it simply calls the exported main function (makes sense). Therefore I was only ever creating a connection to the database once, the first time it ran, and everytime after that it wasn't getting created again. So I simply moved in the instantiating of the class inside the exported function like so:

exports.handler = function(event, context, callback) {
    var db = new DatabaseConnection(5);

    //stuff
    ...
}