Thursday, 8 June 2017

NodeJS lost connection with Mysql with pool exported

I have a Mysql database hosted in a remote server and a Node API hosted in Azure webapps.

The problem is mysql driver lost the connection and can't resume the connection if there is no reboot.

I have been declared a global value at server.js that requires que pool:

global.DB = require('./middlewares/database');

At database.js:

    var pool = mysql.createPool({
    connectionLimit: 10,
    host: [SERVER],
    user: [USER],
    password: [PASSWORD],
    database: [DATABASE],
    connectTimeout: 20000,
    adquireTimeout: 20000,
    debug: false
});

exports.query = function (query, params, callback) {

    pool.getConnection(function (err, connection) {

        if (err) {
            console.log(err);
            connection.release();
            throw err;
        }

        connection.query(query, params, function (err, rows) {

            connection.release();

            if (!err) {
                callback(null, rows);
            }

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

And then, to make any query from anywhere of the API:

 DB.query(queryString, [arguments], function (err, rows, fields) {
  if (err) throw err;
});

This work properly at first time but when pass a few minutes:

    { Error: connect ETIMEDOUT
    at PoolConnection.Connection._handleConnectTimeout (D:\home\site\wwwroot\node_modules\mysql\lib\Connection.js:419:13)
    at Object.onceWrapper (events.js:293:19)
    at emitNone (events.js:86:13)
    at Socket.emit (events.js:188:7)
    at Socket._onTimeout (net.js:352:8)
    at ontimeout (timers.js:386:14)
    at tryOnTimeout (timers.js:250:5)
    at Timer.listOnTimeout (timers.js:214:5)
    --------------------
    at Protocol._enqueue (D:\home\site\wwwroot\node_modules\mysql\lib\protocol\Protocol.js:141:48)
    at Protocol.handshake (D:\home\site\wwwroot\node_modules\mysql\lib\protocol\Protocol.js:52:41)
    at PoolConnection.connect (D:\home\site\wwwroot\node_modules\mysql\lib\Connection.js:130:18)
    at Pool.getConnection (D:\home\site\wwwroot\node_modules\mysql\lib\Pool.js:48:16)
    at Pool.releaseConnection (D:\home\site\wwwroot\node_modules\mysql\lib\Pool.js:157:10)
    at Pool._removeConnection (D:\home\site\wwwroot\node_modules\mysql\lib\Pool.js:277:8)
    at Pool._purgeConnection (D:\home\site\wwwroot\node_modules\mysql\lib\Pool.js:258:8)
    at Ping.onOperationComplete [as _callback] (D:\home\site\wwwroot\node_modules\mysql\lib\Pool.js:101:12)
    at Ping.Sequence.end (D:\home\site\wwwroot\node_modules\mysql\lib\protocol\sequences\Sequence.js:86:24)
    at D:\home\site\wwwroot\node_modules\mysql\lib\protocol\Protocol.js:399:18
  errorno: 'ETIMEDOUT',
  code: 'ETIMEDOUT',
  syscall: 'connect',
  fatal: true }

I adjust the timeout of the Mysql to 28800 and set the error connections to 1000 without result.

How is the best way to do similar pattern without lost the connection?



via Daneel Olivaw

No comments:

Post a Comment