0

On our production Azure Hosted CentOS API server we are having intermittent Sequelize connection issues to our Azure SQL database.

Our connection settings are as follows:

const sequelize = new Sequelize(dbDatabase, dbUser, dbPassword, {
 host: dbHost,
 dialect: 'mssql',
 operatorsAliases: false,
 pool: {
   max: 5092,
   min: 0,
   acquire: 10000,
   idle: 10000,
   evict: 500,
 },
 dialectOptions: {
 encrypt: false, // Use this if you're on Windows Azure
 requestTimeout: 60000 * 2,
 },
}

We have also set the ulimit for open files by the process to the system max.

We are using PM2 to run the server.

The two errors that sometimes appear in our logs roughly every 10-20 minutes are:

SequelizeConnectionError
    at Connection.connection.on (/home/AZ-admin/XXX/node_modules/sequelize/lib/dialects/mssql/connection-manager.js:75:16)
    at emitNone (events.js:106:13)
    at Connection.emit (events.js:208:7)
    at Connection.cleanupConnection (/home/AZ-admin/XXX/node_modules/tedious/lib/connection.js:568:16)
    at Connection.enter (/home/AZ-admin/XXX/node_modules/tedious/lib/connection.js:1961:12)
    at Connection.transitionTo (/home/AZ-admin/XXX/node_modules/tedious/lib/connection.js:993:26)
    at Connection.socketEnd (/home/AZ-admin/XXX/node_modules/tedious/lib/connection.js:1036:12)
    at Socket.<anonymous> (/home/AZ-admin/XXX/node_modules/tedious/lib/connection.js:877:18)
    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:139:11)
    at process._tickCallback (internal/process/next_tick.js:181:9)

or

SequelizeHostNotFoundError: Failed to connect to xxx-prod.database.windows.net:1433 - getaddrinfo ENOTFOUND xxx-prod.database.windows.net
    at Connection.connection.on.err (/home/XXX/node_modules/sequelize/lib/dialects/mssql/connection-manager.js:98:22)
    at emitOne (events.js:116:13)
    at Connection.emit (events.js:211:7)
    at Connection.socketError (/home/XXX/node_modules/tedious/lib/connection.js:1016:14)
    at /home/XXX/node_modules/tedious/lib/connection.js:861:25
    at GetAddrInfoReqWrap.callback (/home/XXX/node_modules/tedious/lib/connector.js:69:18)
    at GetAddrInfoReqWrap.onlookupall [as oncomplete] (dns.js:79:17)

We think this could anything from an issue with connections/files being opened and not closed and reaching a limit to a problem with our DNS looking up the domain name.

It does appear to be somewhat tied to an increase in traffic, but the correlation is not 100% clear.

user3345225
  • 31
  • 1
  • 5
  • If my reply is helpful, please accept it as answer(click on the mark option beside the reply to toggle it from greyed out to fill in.), see https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – Jason Pan Apr 19 '21 at 03:49

1 Answers1

0

I saw your sample code missing options in dialectOptions.

It should be like below.

"dialectOptions": {
    options: {
        encrypt: true,
    }
}

Before I have create a sample project to connect mssql and mysql. Hope it can help you. For more details, please check my answer in below post.

Azure Database for MySQL - webapp nodejs

enter image description here

enter image description here

Jason Pan
  • 15,263
  • 1
  • 14
  • 29
  • We have added 'encrypt: true' as well as setting the packetSize (which we read elsewhere) and neither has fixed the intermittent connection issues. Is there a reason why not having encrypt set would lead to intermittent issues? – user3345225 Apr 20 '21 at 17:23