Server connection timed out when using mysql pooling

Question

So I have been getting this error a lot recently.

Error: Connection lost: The server closed the connection.
at Protocol.end (/home/node_modules/mysql/lib/protocol/Protocol.js:$
at Socket.<anonymous> (/home/node_modules/mysql/lib/Connection.js:1$
at emitNone (events.js:111:20)
at Socket.emit (events.js:208:7)
at endReadableNT (_stream_readable.js:1064:12)
at _combinedTickCallback (internal/process/next_tick.js:138:11)
at process._tickDomainCallback (internal/process/next_tick.js:218:9)

I have been searching a lot for a solution. I read a lot that mysql pooling would solve this, which I have been using for weeks now. The error still pops up though. Does anyone know why this could be?

I'm using this base function which I found in an answer on Stackoverflow. It handles all my queries

var mysql   = require("mysql");
var config = require('./db');
var db = config.database;

var pool = mysql.createPool({
    connectionLimit : 20,
    host: db.host,
    user: db.user,
    password: db.password,
    database: db.database
});


var DB = (function () {

    function _query(query, params, callback) {
        pool.getConnection(function (err, connection) {
            if (err) {
                connection.release();
                callback(null, err);
                throw err;
            }

            connection.query(query, params, function (err, rows) {
                connection.release();
                if (!err) {
                    callback(rows);
                }
                else {
                    callback(null, err);
                }

            });

            connection.on('error', function (err) {
                connection.release();
                callback(null, err);
                throw err;
            });
        });
    };

    return {
        query: _query
    };
})();

module.exports = DB;

I'm executing queries like this:

    DB.query("SELECT * FROM lists WHERE list_id = ?", [listId], function (result, err) {
console.log(result);

}

Answer 1

The MySQL Server has a variable called interactive_timeout, this means, if your connection is idle for X seconds, the server will close the connection.

You can increase this value slightly, but the prefered way is to acknowledge the timeout and simply use a new connection from the pool if you need to query something.

See https://github.com/mysqljs/mysql#error-handling

The connection pool does not prevent any timouts, but the pool assures that you always have a connection, or have several connections if your application is under heavy load. If you only of very little traffic, you won't even need multiple connections, hence, you would not even need a connection pool.

Each connection in the pool will time out, because using release() does not close the connection but give it back to the pool.

So your disconnects are pretty normal and you should handle the error appropriately.

The connections are recreated automatically, see https://github.com/mysqljs/mysql#poolcluster-options

canRetry (Default: true)
If true, PoolCluster will attempt to reconnect when connection fails. 

How do you correctly handle the error?

Prepare a general error handler for all the MySQL errors:

// Above:
mySqlErrorHandler = function(error) {
    if (error.code == '') { // <---- Insert in '' the error code, you need to find out
        // Connection timeout, no further action (no throw)
    } else {
        // Oh, a different error, abort then
        throw error;
    }
}

// In the function:
connection.on('error', mySqlErrorHandler);

You need to find out error.code for your timeout. This can be done with console.log(error.code);.