mysql - PROTOCOL_SEQUENCE_TIMEOUT while doing dynamic MySQL Connection in sails-js

Question

My Sails js Application has a Multi-tenant Database Structure. So as per Multitenant Logic, I had to create a dynamic database connection in the controller File. I am following this Method https://sailsjs.com/documentation/reference/waterline-orm/datastores/driver for dynamic MySQL database connection.

All the APIs works fine as per requirement, but when I started Performance testing of Single Api with 500 users and 10 seconds ramp-up time at that time Api started failing after 300 requests, Checking to the stack trace there was below issue

{
error: Error: Handshake inactivity timeout
    at Handshake.<anonymous> (/usr/src/app/node_modules/mysql/lib/protocol/Protocol.js:160:17)
    at Handshake.emit (node:events:369:20)
    at Handshake._onTimeout (/usr/src/app/node_modules/mysql/lib/protocol/sequences/Sequence.js:124:8)
    at Timer._onTimeout (/usr/src/app/node_modules/mysql/lib/protocol/Timer.js:32:23)
    at listOnTimeout (node:internal/timers:557:17)
    at processTimers (node:internal/timers:500:7)
    --------------------
    at Protocol._enqueue (/usr/src/app/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Protocol.handshake (/usr/src/app/node_modules/mysql/lib/protocol/Protocol.js:51:23)
    at PoolConnection.connect (/usr/src/app/node_modules/mysql/lib/Connection.js:116:18)
    at Pool.getConnection (/usr/src/app/node_modules/mysql/lib/Pool.js:48:16)
    at Object.getConnection (/usr/src/app/node_modules/machinepack-mysql/lib/get-connection.js:78:25)
    at wrapper (/usr/src/app/node_modules/@sailshq/lodash/lib/index.js:3282:19)
    at Deferred.parley.retry [as _handleExec] (/usr/src/app/node_modules/machine/lib/private/help-build-machine.js:1076:19)
    at Deferred.exec (/usr/src/app/node_modules/parley/lib/private/Deferred.js:286:10)
    at Deferred.tryCatcher (/usr/src/app/node_modules/bluebird/js/release/util.js:11:23)
    at ret (eval at makeNodePromisifiedEval (/usr/src/app/node_modules/bluebird/js/release/promisify.js:184:12), <anonymous>:14:23)
    at Deferred.toPromise (/usr/src/app/node_modules/parley/lib/private/Deferred.js:572:19)
    at Deferred.then (/usr/src/app/node_modules/parley/lib/private/Deferred.js:431:22) {
  code: 'PROTOCOL_SEQUENCE_TIMEOUT',
  fatal: true,
  timeout: 10000
},
meta: undefined
}
  at Object.tenantConnection (/usr/src/app/api/services/utils.js:93:29)
  at async Object.login [as user/login] (/usr/src/app/api/controllers/UserController.js:112:28) {
cause: Exception: `getConnection` failed ("failed").  Could not acquire a connection to the database using the specified manager.
Additional data:
{
  error: Error: Handshake inactivity timeout
      at Handshake.<anonymous> (/usr/src/app/node_modules/mysql/lib/protocol/Protocol.js:160:17)
      at Handshake.emit (node:events:369:20)
      at Handshake._onTimeout (/usr/src/app/node_modules/mysql/lib/protocol/sequences/Sequence.js:124:8)
      at Timer._onTimeout (/usr/src/app/node_modules/mysql/lib/protocol/Timer.js:32:23)
      at listOnTimeout (node:internal/timers:557:17)
      at processTimers (node:internal/timers:500:7)
      --------------------
      at Protocol._enqueue (/usr/src/app/node_modules/mysql/lib/protocol/Protocol.js:144:48)
      at Protocol.handshake (/usr/src/app/node_modules/mysql/lib/protocol/Protocol.js:51:23)
      at PoolConnection.connect (/usr/src/app/node_modules/mysql/lib/Connection.js:116:18)
      at Pool.getConnection (/usr/src/app/node_modules/mysql/lib/Pool.js:48:16)
      at Object.getConnection (/usr/src/app/node_modules/machinepack-mysql/lib/get-connection.js:78:25)
      at wrapper (/usr/src/app/node_modules/@sailshq/lodash/lib/index.js:3282:19)
      at Deferred.parley.retry [as _handleExec] (/usr/src/app/node_modules/machine/lib/private/help-build-machine.js:1076:19)
      at Deferred.exec (/usr/src/app/node_modules/parley/lib/private/Deferred.js:286:10)
      at Deferred.tryCatcher (/usr/src/app/node_modules/bluebird/js/release/util.js:11:23)
      at ret (eval at makeNodePromisifiedEval (/usr/src/app/node_modules/bluebird/js/release/promisify.js:184:12), <anonymous>:14:23)
      at Deferred.toPromise (/usr/src/app/node_modules/parley/lib/private/Deferred.js:572:19)
      at Deferred.then (/usr/src/app/node_modules/parley/lib/private/Deferred.js:431:22) {
    code: 'PROTOCOL_SEQUENCE_TIMEOUT',
    fatal: true,
    timeout: 10000
  },
  meta: undefined
}
    at Object.tenantConnection (/usr/src/app/api/services/utils.js:93:29)
    at async Object.login [as user/login] (/usr/src/app/api/controllers/UserController.js:112:28) {
  code: 'failed',
  exit: 'failed',
  traceRef: {},
  raw: [Object]
},
isOperational: true,
code: 'failed',
exit: 'failed',
traceRef: {},
raw: { error: [Error], meta: undefined }

}

Note: I am using Azure Mysql Database.

Creating and Destroying dynamic Connection

// Get the generic, stateless driver for our database (e.g. MySQL).
var Driver = sails.getDatastore().driver;

// Create our own dynamic connection manager (e.g. connection pool)
var manager = (
  await Driver.createManager({ connectionString: req.param('connectionUrl') })
).manager;

var db;
try {
 db = (
   await Driver.getConnection({ manager: managerReport.manager })
 ).connection;
} catch (err) {
 await Driver.destroyManager({ manager: managerReport.manager });
 throw err;
}

//**********************************************
// Do some stuff here...
// e.g.
//     await Driver.sendNativeQuery({
//       connection: db,
//       nativeQuery: '...'
//     });
//**********************************************

// Finally, before we continue, tear down the dynamic connection manager.
// (this also takes care of releasing the active connection we acquired above)
await Driver.destroyManager({ manager: managerReport.manager });

return res.ok();

标签： mysqlnode.jsazureazure-sql-databasesails.js

Answer 1

node(v4.2.0) 和 mysql 存在问题。我使用的是node(v4.2.0)，我也遇到了同样的问题，我已经升级到node v4.2.1，上面的mysql超时问题已经解决，一切似乎都正常了。

超时来自 Protocol.js 文件。如果您检查 node-mysql，您会看到它是一个用于查询的变量“超时”。将超时设置为高于 10000（默认值）。

pool = require('mysql').createPool({
    connectionLimit : 1000,
    connectTimeout  : 60 * 60 * 1000,
    acquireTimeout  : 60 * 60 * 1000,
    timeout         : 60 * 60 * 1000,
    host            : process.env.DB_HOST,
    user            : process.env.DB_USERNAME,
    password        : process.env.DB_PASSWORD,
    database        : process.env.DB_DATABASE
});

您还可以在 Sequence.js 文件（node_modules/mysql/lib/protocol/sequences/Sequence.js）中编辑超时

this._timeout  = 100000;