Ошибка connection timed out after 15000 milliseconds

Issue: unable to set requestTimeout with configuration parameter for «mssql» dilect
No matter whatever value is in requestTimeout parameter, the driver sets default value of «15000ms».
Hence, Timeout error occurs for all query that takes longer than 15s.

Application log is as follows:

Executing (default): SELECT [AnalysisTagID], [RefDate], [CategorySet], [AssetID], [Category] FROM [EMA_RiskAllocCategoriesView] AS [EMA_RiskAllocCategoriesView] ORDER BY [EMA_RiskAllocCategoriesView].[AssetID] OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
Error EMA_RiskAllocCategoriesView.getAll DatabaseError [SequelizeDatabaseError]: Timeout: Request failed to complete in 15000ms
    at Query.formatError (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/sequelize/lib/dialects/mssql/query.js:309:12)
    at Request.userCallback (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/sequelize/lib/dialects/mssql/query.js:69:23)
    at Request.callback (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/request.js:37:27)
    at Connection.message (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/connection.js:2181:24)
    at Connection.dispatchEvent (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/connection.js:1172:36)
    at MessageIO.<anonymous> (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/connection.js:1045:14)
    at MessageIO.emit (events.js:200:13)
    at Message.<anonymous> (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/message-io.js:32:14)
    at Message.emit (events.js:205:15)
    at endReadableNT (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:1077:12)
    at processTicksAndRejections (internal/process/task_queues.js:84:9) {
  name: 'SequelizeDatabaseError',
  parent: RequestError: Timeout: Request failed to complete in 15000ms
      at RequestError (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/errors.js:32:12)
      at Connection.requestTimeout (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/connection.js:1117:21)
      at Timeout._onTimeout (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/connection.js:1085:14)
      at listOnTimeout (internal/timers.js:531:17)
      at processTimers (internal/timers.js:475:7) {
    message: 'Timeout: Request failed to complete in 15000ms',
    code: 'ETIMEOUT',
    sql: 'SELECT [AnalysisTagID], [RefDate], [CategorySet], ' +
      '[AssetID], [Category] FROM [EMA_RiskAllocCategoriesView] ' +
      'AS [EMA_RiskAllocCategoriesView] ORDER BY ' +
      '[EMA_RiskAllocCategoriesView].[AssetID] OFFSET 0 ROWS ' +
      'FETCH NEXT 3 ROWS ONLY;'
  },

What are you doing?

sequelize is instantiated as follows where requestTimeout is set to 30s

const sequelize = new Sequelize(config.db.schema, config.db.username, config.db.password, {  
  "host": config.db.url,
  "dialect": "mssql",
  "dialectOptions": {
    "requestTimeout": 300000
  },
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
});

A query that does full scan in a large table is run as follows:

EMA_RiskAllocCategoriesView.findAll({
    }).catch((err)=>{
      console.log("Error EMA_RiskAllocCategoriesView.getAll",err)
    });

To Reproduce
Steps to reproduce the behavior:

1. Initiate sequelize with requestTimeout set to 30s
2. Use a query that takes longer than 15s
3. See error which says «Request failed to complete in 15000ms»

What do you expect to happen?

Not thow request timeout error for queries that run for 30s if sequelize is set to have requestTimeout of 30s.

What is actually happening?

Timeout Error for all query that takes longer than 15 s

Output, either JSON or SQL

Executing (default): SELECT [AnalysisTagID], [RefDate], [CategorySet], [AssetID], [Category] FROM [EMA_RiskAllocCategoriesView] AS [EMA_RiskAllocCategoriesView] ORDER BY [EMA_RiskAllocCategoriesView].[AssetID] OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
Error EMA_RiskAllocCategoriesView.getAll DatabaseError [SequelizeDatabaseError]: Timeout: Request failed to complete in 15000ms
    at Query.formatError (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/sequelize/lib/dialects/mssql/query.js:309:12)
    at Request.userCallback (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/sequelize/lib/dialects/mssql/query.js:69:23)
    at Request.callback (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/request.js:37:27)
    at Connection.message (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/connection.js:2181:24)
    at Connection.dispatchEvent (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/connection.js:1172:36)
    at MessageIO.<anonymous> (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/connection.js:1045:14)
    at MessageIO.emit (events.js:200:13)
    at Message.<anonymous> (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/message-io.js:32:14)
    at Message.emit (events.js:205:15)
    at endReadableNT (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:1077:12)
    at processTicksAndRejections (internal/process/task_queues.js:84:9) {
  name: 'SequelizeDatabaseError',
  parent: RequestError: Timeout: Request failed to complete in 15000ms
      at RequestError (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/errors.js:32:12)
      at Connection.requestTimeout (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/connection.js:1117:21)
      at Timeout._onTimeout (/Users/abinash/Desktop/my-app/new-dashboard/ember-electron/resources/sapiat-server/node_modules/tedious/lib/connection.js:1085:14)
      at listOnTimeout (internal/timers.js:531:17)
      at processTimers (internal/timers.js:475:7) {
    message: 'Timeout: Request failed to complete in 15000ms',
    code: 'ETIMEOUT',
    sql: 'SELECT [AnalysisTagID], [RefDate], [CategorySet], ' +
      '[AssetID], [Category] FROM [EMA_RiskAllocCategoriesView] ' +
      'AS [EMA_RiskAllocCategoriesView] ORDER BY ' +
      '[EMA_RiskAllocCategoriesView].[AssetID] OFFSET 0 ROWS ' +
      'FETCH NEXT 3 ROWS ONLY;'
  },

Environment

Dialect:

• mssql

Dialect library version: tedious 6.1.2
Database version: mcr.microsoft.com/mssql/server:2017-latest (docker)
Sequelize version: 5.8.7
Node Version: v12.3.1
OS: osx
If TypeScript related: TypeScript version: XXX
Tested with latest release:

• No

Self investigation.
hard coded fix is to change the value of the following variable in «lib/connection.js» module of tedious library
const DEFAULT_CLIENT_REQUEST_TIMEOUT = 15 * 1000;

У меня есть 3 узла Akka Cluster, и на каждом узле кластера работают 3 субъекта. Кластер работает нормально около 2 часов, но через 2 часа я получаю следующее предупреждение:

[INFO] [06/07/2018 15:08:51.923] [ClusterSystem-akka.remote.default-remote-dispatcher-6] [akka.tcp://ClusterSystem@192.168.2.8:2552/system/transports/akkaprotocolmanager.tcp0/akkaProtocol-tcp%3A%2F%2FClusterSystem%40192.168.2.7%3A2552-112] No response from remote for outbound association. Handshake timed out after [15000 ms].

[WARN] [06/07/2018 15:08:51.923] [ClusterSystem-akka.remote.default-remote-dispatcher-18] [akka.tcp://ClusterSystem@192.168.2.8:2552/system/endpointManager/reliableEndpointWriter-akka.tcp%3A%2F%2FClusterSystem%40192.168.2.7%3A2552-8] Association with remote system [akka.tcp://ClusterSystem@192.168.2.7:2552] has failed, address is now gated for [5000] ms. Reason: [Association failed with [akka.tcp://ClusterSystem@192.168.2.7:2552]] Caused by: [No response from remote for outbound association. Handshake timed out after [15000 ms].]

[WARN] [06/07/2018 16:07:06.347] [ClusterSystem-akka.actor.default-dispatcher-101] [akka.remote.PhiAccrualFailureDetector@3895fa5b] heartbeat interval is growing too large: 2839 millis

Редактировать: Ответ Akka CLuster Managemant от API

{
  "selfNode": "akka.tcp://ClusterSystem@127.0.0.1:2551",
  "leader": "akka.tcp://ClusterSystem@127.0.0.1:2551",
  "oldest": "akka.tcp://ClusterSystem@127.0.0.1:2551",
  "unreachable": [
    {
      "node": "akka.tcp://ClusterSystem@127.0.0.1:2552",
      "observedBy": [
        "akka.tcp://ClusterSystem@127.0.0.1:2551",
        "akka.tcp://ClusterSystem@127.0.0.1:2560"
      ]
    }
  ],
  "members": [
    {
      "node": "akka.tcp://ClusterSystem@127.0.0.1:2551",
      "nodeUid": "105742380",
      "status": "Up",
      "roles": [
        "Frontend",
        "dc-default"
      ]
    },
    {
      "node": "akka.tcp://ClusterSystem@127.0.0.1:2552",
      "nodeUid": "-150160059",
      "status": "Up",
      "roles": [
        "RuleExecutor",
        "dc-default"
      ]
    },
    {
      "node": "akka.tcp://ClusterSystem@127.0.0.1:2560",
      "nodeUid": "-158907672",
      "status": "Up",
      "roles": [
        "RuleExecutor",
        "dc-default"
      ]
    }
  ]
}

** Edit1: ** Конфигурация установки кластера и конфигурация детектора отказов

cluster {
      jmx.multi-mbeans-in-same-jvm = on
      roles = ["Frontend"]
      seed-nodes = [
        "akka.tcp://ClusterSystem@192.168.2.9:2551"]
      auto-down-unreachable-after = off

      failure-detector {

        # FQCN of the failure detector implementation.
        # It must implement akka.remote.FailureDetector and have
        # a public constructor with a com.typesafe.config.Config and
        # akka.actor.EventStream parameter.
        implementation-class = "akka.remote.PhiAccrualFailureDetector"

        # How often keep-alive heartbeat messages should be sent to each connection.
        # heartbeat-interval = 10 s

        # Defines the failure detector threshold.
        # A low threshold is prone to generate many wrong suspicions but ensures
        # a quick detection in the event of a real crash. Conversely, a high
        # threshold generates fewer mistakes but needs more time to detect
        # actual crashes.
        threshold = 18.0

        # Number of the samples of inter-heartbeat arrival times to adaptively
        # calculate the failure timeout for connections.
        max-sample-size = 1000

        # Minimum standard deviation to use for the normal distribution in
        # AccrualFailureDetector. Too low standard deviation might result in
        # too much sensitivity for sudden, but normal, deviations in heartbeat
        # inter arrival times.
        min-std-deviation = 100 ms

        # Number of potentially lost/delayed heartbeats that will be
        # accepted before considering it to be an anomaly.
        # This margin is important to be able to survive sudden, occasional,
        # pauses in heartbeat arrivals, due to for example garbage collect or
        # network drop.
        acceptable-heartbeat-pause = 15 s

        # Number of member nodes that each member will send heartbeat messages to,
        # i.e. each node will be monitored by this number of other nodes.
        monitored-by-nr-of-members = 2

        # After the heartbeat request has been sent the first failure detection
        # will start after this period, even though no heartbeat message has
        # been received.
        expected-response-after = 10 s

      }

    }

var sql = require( "seriate" );

var connection = {
    name: "example-1",
    user: "user",
    password: "pass",
    host: "host_ip",
    database: "Test"
};    

exports.getDataSql = function(req, res) {
    var results = {};

    sql.execute( connection, {
       procedure: "GetData",
        params: {
            Name: {
                type: sql.NVARCHAR(50),
                val: "user2"
            },
            LName: {
                type: sql.NVARCHAR(50),
                val: "user1"
            },
            finalName: {
                type: sql.NVARCHAR(50),
                val: "user3"
            }
        }
    }).then( function( results ) {
        console.log("getting data: ");
        res.json(results[0][0]);

    }, function( err ) {
        console.log( "Error", err );
        res.status(500).send({ error: 'Something failed!' });
    } );

};

3 ответа

var connection = {
    name: "example-1",
    user: "user",
    password: "pass",
    host: "host_ip",
    database: "Test",
    requestTimeout: 300000
};    

pool.close()

I keep getting this error:

{«name»:»RequestError»,»message»:»Timeout: Request failed to complete in 15000ms»,»code»:»ETIMEOUT»,»number»:»ETIMEOUT»,»precedingErrors»:[]}

How do I increase the timeout for my request?

I am not sure if this is coming from the sql server database or from the node.js service?

How do I see what is happening with sql server from azure?

I have sql server management studio and visual studio so i can login to my database, but don’t see how to increase timeout etc.

Are there any parameters I set in node.js to increase timeout?

I found this:

http://azure.github.io/azure-mobile-apps-node/global.html#dataConfiguration

and presume I have to set something in my query object?

My node.js API that I call, searchService.js

var HttpStatus = require('http-status-codes');

module.exports = {
    "post": function (req, res, next) {
        var resultSet = {
            TotalRecords: 0,
            Results: null
        };
        var parameters = [
            { name: 'forumId', value: req.body.forumId },
            { name: 'registrantId', value: req.body.registrantId },
            { name: 'userId', value: req.azureMobile.user.id },
            { name: 'exclusive', value: req.body.exclusive },
            { name: 'type', value: req.body.type },
            { name: 'categoryIds', value: req.body.categoryIds.join(",") },
            { name: 'locationIds', value: req.body.locationIds.join(",") },
            { name: 'unitIds', value: req.body.unitIds.join(",") },
            { name: 'priceIds', value: req.body.priceIds.join(",") },
            { name: 'delimiter', value: "," }
        ];

        console.log("parameters = " + JSON.stringify(parameters));

        var query = {
            sql: "exec SearchServicesStrictTotal @forumId, @registrantId, @userId, @exclusive, @type, @categoryIds, @locationIds, @unitIds, @priceIds, @delimiter",
            parameters: parameters
        };

        req.azureMobile.data.execute(query)
            .then(function(result) {
                console.log("got result " + JSON.stringify(result));

                resultSet.TotalRecords = result[0].Total;
                res.status(HttpStatus.OK).send(resultSet);
            }).catch(function(error){
                console.log("error one " + JSON.stringify(error));
                res.status(HttpStatus.BAD_REQUEST).send(error);
            });
    }
};

I keep getting this error:

{«name»:»RequestError»,»message»:»Timeout: Request failed to complete in 15000ms»,»code»:»ETIMEOUT»,»number»:»ETIMEOUT»,»precedingErrors»:[]}

How do I increase the timeout for my request?

I am not sure if this is coming from the sql server database or from the node.js service?

How do I see what is happening with sql server from azure?

I have sql server management studio and visual studio so i can login to my database, but don’t see how to increase timeout etc.

Are there any parameters I set in node.js to increase timeout?

I found this:

http://azure.github.io/azure-mobile-apps-node/global.html#dataConfiguration

and presume I have to set something in my query object?

My node.js API that I call, searchService.js

var HttpStatus = require('http-status-codes');

module.exports = {
    "post": function (req, res, next) {
        var resultSet = {
            TotalRecords: 0,
            Results: null
        };
        var parameters = [
            { name: 'forumId', value: req.body.forumId },
            { name: 'registrantId', value: req.body.registrantId },
            { name: 'userId', value: req.azureMobile.user.id },
            { name: 'exclusive', value: req.body.exclusive },
            { name: 'type', value: req.body.type },
            { name: 'categoryIds', value: req.body.categoryIds.join(",") },
            { name: 'locationIds', value: req.body.locationIds.join(",") },
            { name: 'unitIds', value: req.body.unitIds.join(",") },
            { name: 'priceIds', value: req.body.priceIds.join(",") },
            { name: 'delimiter', value: "," }
        ];

        console.log("parameters = " + JSON.stringify(parameters));

        var query = {
            sql: "exec SearchServicesStrictTotal @forumId, @registrantId, @userId, @exclusive, @type, @categoryIds, @locationIds, @unitIds, @priceIds, @delimiter",
            parameters: parameters
        };

        req.azureMobile.data.execute(query)
            .then(function(result) {
                console.log("got result " + JSON.stringify(result));

                resultSet.TotalRecords = result[0].Total;
                res.status(HttpStatus.OK).send(resultSet);
            }).catch(function(error){
                console.log("error one " + JSON.stringify(error));
                res.status(HttpStatus.BAD_REQUEST).send(error);
            });
    }
};

Я пытаюсь запустить хранимую процедуру в моей базе данных SQL с сервера узлов с помощью seriate. однако я получаю следующую ошибку, и я не уверен, почему. Я бы очень признателен вам за вашу помощь.

Ошибка: {[RequestError: ошибка SqlContext. Не удалось выполнить шаг «GetData» с: «Тайм-аут: запрос не удалось завершить я 15000ms»] name: «RequestError», сообщение: «Ошибка SqlContext. Не удалось выполнить шаг «GetData» с: «Запрос на тайм-аут не завершился в 15000мс», код: «ETIMEOUT», номер: «ETIMEOUT», строка «Номер»: undefined, state: undefined, class: undefined, serverName: undefined, procName: undefined, beforeErrors: [], step: ‘GetData’}

Это мой код:

var sql = require( "seriate" );

var connection = {
    name: "example-1",
    user: "user",
    password: "pass",
    host: "host_ip",
    database: "Test"
};    

exports.getDataSql = function(req, res) {
    var results = {};

    sql.execute( connection, {
       procedure: "GetData",
        params: {
            Name: {
                type: sql.NVARCHAR(50),
                val: "user2"
            },
            LName: {
                type: sql.NVARCHAR(50),
                val: "user1"
            },
            finalName: {
                type: sql.NVARCHAR(50),
                val: "user3"
            }
        }
    }).then( function( results ) {
        console.log("getting data: ");
        res.json(results[0][0]);

    }, function( err ) {
        console.log( "Error", err );
        res.status(500).send({ error: 'Something failed!' });
    } );




};