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);

}

I have had similiar problems with the connectionPool. Does this link help you? — Daniel W.
– Daniel W., Commented Sep 6, 2018 at 8:33
@DanFromGermany Thank you for your reply, but I don't think it does. I keep the nodejs script running with pm2, so I don't close it with ctrl + c. — P.Yntema
– P.Yntema, Commented Sep 6, 2018 at 9:09
Do you have time between two queries? Because there is an idle timeout in mysql. If you don't query something for X seconds, the connection gets closed. — Daniel W.
– Daniel W., Commented Sep 7, 2018 at 9:56
@DanFromGermany Yes, it is running persistent, so if no user connects it won't execute queries. But I thought mysql pooling would solve this? — P.Yntema
– P.Yntema, Commented Sep 7, 2018 at 14:12

Daniel W. · Accepted Answer · 2018-09-10 10:42:23Z

2

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);.

edited Sep 10, 2018 at 10:42

answered Sep 7, 2018 at 15:24

Daniel W.

32.6k15 gold badges99 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

P.Yntema Over a year ago

Could you suggest any code which will recreate the existing connection(s) in the pool without the user having any issues? Thanks very much for the answer

Daniel W. Over a year ago

The connections are recreated automatically.

P.Yntema Over a year ago

So you are saying that if I just handle the error then my websocket won't crash because of the error and the pool will reconnect automatically? Or should I destroy the connection that timed out first?

Daniel W. Over a year ago

@P.Yntema You handle the error, but you also throw it: throw err;

P.Yntema Over a year ago

Thanks so much for your help, will let you know if it fixes the problem.

|

Collectives™ on Stack Overflow

Server connection timed out when using mysql pooling

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related