Last Updated: February 25, 2016
·
3.897K
· xandout

Prevent node-mysql connection closed error

I have been using nodemon and forever to keep my MySQL backed Node.js app running. I had a small issue where the app would crash after 24-36 hours with MySQL closing the connection. I just assumed it was due to poor coding on my part but didn't really know where my mistake was until now......

exports.getData = function getData(what, callback) {
    pool.getConnection(function(err, connection) {
        connection.on('error', function(err) {
            console.log(err); // 'ER_BAD_DB_ERROR'
        });
        var sql = "SELECT * FROM table WHERE column = '" + what + "'";
        connection.query(sql, function(err, rows) {
            connection.on('error', function(err) {
                console.log(err); // 'ER_BAD_DB_ERROR'
            });
            callback(rows);
        });
        connection.release();
    });
}

This code works, not all the time but 99% of the time it works great.

The problem as it turns out is where I was releasing the connection....

exports.getData = function getData(what, callback) {
    pool.getConnection(function(err, connection) {
        connection.on('error', function(err) {
            console.log(err); // 'ER_BAD_DB_ERROR'
        });
        var sql = "SELECT * FROM table WHERE column = '" + what + "'";
        connection.query(sql, function(err, rows) {
            connection.on('error', function(err) {
                console.log(err); // 'ER_BAD_DB_ERROR'
            });
            connection.release(); //Here is where it should be.
            callback(rows);
        });
        //Not Here
    });
}

I was calling back to other functions, which sometimes themselves where callbacks or would do other long-running tasks and never releasing the connection.

Since I had a pool of connections I should have been releasing the connection when I was done with it......imagine that.

** TL;DR **
Always put your toys up when you are done with them.

1 Response
Add your response

Great article, thanks :D

over 1 year ago ·