ETIMEDOUT exception brings down connection to database

[fredrikekelund]

I'm using Sequelize 3.1.0 (haven't had time to update to 3.2.0 yet) with node 0.12.4 and I'm having pretty severe issues around communicating with Postgresql.

The problem is that I get an ETIMEDOUT exception, which seems to crash Sequelize. I get this exception 5 times on an app which uses a connection pool that can have a maximum of 5 connections, so I'm thinking that this happens once per connection, even more so because these 5 errors also occur at the same time. Here's the log:

[debug] 2015-06-06 20:46:18 'Executing (default): INSERT INTO "FooBar" ("id","url","FooId","BarId","updatedAt","createdAt") VALUES (DEFAULT,\'http://www.example.com/my/example/path\',466,1,\'2015-06-06 18:30:30.701 +00:00\',\'2015-06-06 18:30:30.701 +00:00\') RETURNING *;'
Unhandled rejection SequelizeDatabaseError: read ETIMEDOUT
    at Query.formatError (/home/manifact/recorded-web/interface/node_modules/rw-scraper/node_modules/rw-data-model/node_modules/sequelize/lib/dialects/postgres/query.js:420:14)
    at null.<anonymous> (/home/manifact/recorded-web/interface/node_modules/rw-scraper/node_modules/rw-data-model/node_modules/sequelize/lib/dialects/postgres/query.js:104:19)
    at emit (events.js:107:17)
    at Query.handleError (/home/manifact/recorded-web/interface/node_modules/rw-scraper/node_modules/rw-data-model/node_modules/pg/lib/query.js:99:8)
    at null.<anonymous> (/home/manifact/recorded-web/interface/node_modules/rw-scraper/node_modules/rw-data-model/node_modules/pg/lib/client.js:166:26)
    at emit (events.js:107:17)
    at Socket.<anonymous> (/home/manifact/recorded-web/interface/node_modules/rw-scraper/node_modules/rw-data-model/node_modules/pg/lib/connection.js:59:10)
    at Socket.emit (events.js:107:17)
    at net.js:459:14
    at process._tickCallback (node.js:355:11)

After the error, Sequelize stops logging, but no exceptions are thrown when trying to save data.

I've tried finding out some details about the ETIMEDOUT error, but there wasn't much to go on. Here's an example of a ticket in node-mysql that looks similar to this one, though mysqljs/mysql#904

[mickhansen]

Try lowering the idle timeout for Sequelize, it appears to be lower than what your database allows.

[fredrikekelund]

You mean higher than what my database allows? I'll try and report back. Are there any considerations to be aware of when lowering the idle timeout, like is there a recommended minimum idle time?

[mickhansen]

Higher yes :) Well you probably want atleast a few seconds of minimum idle time, but you could set it as high as your server is set to. It's just important that it's not higher than the server's idle time since it sequelize will then use connections that the server has already timed out.

[fredrikekelund]

I recently reconfigured maxIdleTime in Sequelize to 10 minutes, which I thought based on this Postgresql resource would be good enough (I'm running a Postgresql server on Windows, where the default idle time for a connection seemed to be 2 hours).

Anyway, I'll lower the maxIdleTime time to 1 minute and see what the result is.

[mickhansen]

@fredrikekelund Hmm alright, yea should probably work then.

[fredrikekelund]

Unfortunately, lowering maxIdleTime didn't help, the errors still occurs

I'm not sure if this is much additional help, but this is what the error looks like when actually logging it out:

[error] 2015-06-09 04:29:27 'Couldn\'t save page, retrying...' { [SequelizeDatabaseError: read ETIMEDOUT]
  name: 'SequelizeDatabaseError',
  message: 'read ETIMEDOUT',
  parent: 
   { [Error: read ETIMEDOUT]
     code: 'ETIMEDOUT',
     errno: 'ETIMEDOUT',
     syscall: 'read',
     sql: 'INSERT INTO "FooBar" ("id","url","FooBarId","BarId","updatedAt","createdAt") VALUES (DEFAULT,\'http://example.com\',536,1,\'2015-06-09 02:13:16.107 +00:00\',\'2015-06-09 02:13:16.107 +00:00\') RETURNING *;' },
  original: 
   { [Error: read ETIMEDOUT]
     code: 'ETIMEDOUT',
     errno: 'ETIMEDOUT',
     syscall: 'read',
     sql: 'INSERT INTO "FooBar" ("id","url","FooBarId","BarId","updatedAt","createdAt") VALUES (DEFAULT,\'http://example.com\',536,1,\'2015-06-09 02:13:16.107 +00:00\',\'2015-06-09 02:13:16.107 +00:00\') RETURNING *;' },
  sql: 'INSERT INTO "FooBar" ("id","url","FooBarId","BarId","updatedAt","createdAt") VALUES (DEFAULT,\'http://example.com\',536,1,\'2015-06-09 02:13:16.107 +00:00\',\'2015-06-09 02:13:16.107 +00:00\') RETURNING *;' }

If there is additional debugging work I can do, please let me know @mickhansen. I could maybe try turning on Bluebird's long stack traces if that could be of any help?

[mickhansen]

@fredrikekelund The error is coming from the database so stack trackes likely wouldn't tell us much. Maybe Sequelize is reusing a dead connection incorrectly? I'm not sure.

[fredrikekelund]

Is there any debugging work I can do to determine if that's the case? I've resorted to trying pg-native in the meantime to see if that helps with the problem.

[mickhansen]

You can fiddle around here https://github.com/sequelize/sequelize/blob/master/lib/dialects/postgres/connection-manager.js and here: https://github.com/sequelize/sequelize/blob/master/lib/dialects/abstract/connection-manager.js

Specifically you might want to tjek if $destroy is ever called: https://github.com/sequelize/sequelize/blob/master/lib/dialects/abstract/connection-manager.js#L79

[fredrikekelund]

It seems I was able to avoid this problem by setting minConnections to 1 instead of 0. Felt a bit silly of me, but the example in the docs had minConnections set to 0, and I just copied that without much thought. Could that be the cause of a problem like this?

[mickhansen]

@fredrikekelund It shouldn't be, minConnections above 0 should mean that the pool keeps one connection alive at all time (meaning that when it times out it starts a new one), it shouldn't have an impact on this since with 0 it should create fresh connections too and discard timed out ones aswelll.

[fredrikekelund]

Hmm alright, so far it does seem a little like this might've helped the problem though. Let me keep an eye out for the next few days and I'll report back on how the system is running. I'll try and test to roll back to minConnections: 0 as well to see if that reintroduces the problem.

[shouvikme]

For some odd reasons , this solved my problem too. Had min:0 and changed it to min:1 and so far no timeout error

Also @mickhansen - is there any way we can do this : http://stackoverflow.com/questions/30811228/sequelize-single-instance-not-working-after-module-exports

It will help me buld module apps using Sequalize and Express. Currently I paste everything in one server.js file or create new Define () in each page

[pelayo]

Same or similar error here.
It occurs on sync but also on querys if the sync is skipped.

Unhandled rejection SequelizeConnectionError: connect ETIMEDOUT at /home/ubuntu/microservices/group-microservice-express/node_modules/sequelize/lib/dialects/postgres/connection-manager.js:107:20 at null.<anonymous> (/home/ubuntu/microservices/group-microservice-express/node_modules/pg/lib/client.js:176:5) at EventEmitter.emit (events.js:95:17) at Socket.<anonymous> (/home/ubuntu/microservices/group-microservice-express/node_modules/pg/lib/connection.js:59:10) at Socket.EventEmitter.emit (events.js:95:17) at net.js:441:14 at process._tickCallback (node.js:415:13)

I'm having the same error with a postgres database. Tried minConnections: 1, tried with 0. Lowered maxIdle a lot. Used a local database, a remote one, with and without user athentication.
Tested passing url for the connection and also with regular parameters.
Updated from 3.0.0 to 3.21.0.

The postgres database gets the login event since I can see It logged and if I try with a wrong password the error gets raised. Psql works perfectly with the remote and the local database so it is not a connectivity issue.

I'm out of ideas.

Forgot to add that it worked on another installation. Don't know if it is an specific version bug.

[felimartina]

Did someone ever found out what this problem is? I am facing the same issue right now. Will try setting min to 1.

[garretttaco]

@felimartina Still no luck for me. I tried the above suggestions of setting the minConnections to 1 and nothing. Did you happen to solve this issue?

[felimartina]

@garretttaco Nop :(
What we did was set pool settings to default (without specifying min/max nor timeout)...hope that works for you!

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[kris-dehaudt]

I get this error also. I tried to set the min: 1 and idle: 30000 (30 seconds) in the pool settings but it didn't solve my issue.

Unhandled rejection SequelizeDatabaseError: read ETIMEDOUT
    at Query.formatError (/service/node_modules/sequelize/lib/dialects/postgres/query.js:350:16)
    at Result.query.on.err (/service/node_modules/sequelize/lib/dialects/postgres/query.js:93:21)
    at emitOne (events.js:96:13)
    at Result.emit (events.js:188:7)
    at Result.Query.handleError (/service/node_modules/pg/lib/query.js:163:8)
    at Client.<anonymous> (/service/node_modules/pg/lib/client.js:188:26)
    at emitOne (events.js:96:13)
    at Connection.emit (events.js:188:7)
    at Socket.<anonymous> (/service/node_modules/pg/lib/connection.js:86:10)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at emitErrorNT (net.js:1278:8)
    at _combinedTickCallback (internal/process/next_tick.js:74:11)
    at process._tickCallback (internal/process/next_tick.js:98:9)

[OliverLonghi]

I'm facing the same situation.

[gaspaonrocks]

I have the same problem... ETIMEDOUT connection error with mysql and postgres...

But it works with sqlite and the express example (with slight modifications)

[OliverLonghi]

The following command solved my situation:
/sbin/sysctl -w net.ipv4.tcp_keepalive_time=200 net.ipv4.tcp_keepalive_intvl=200 net.ipv4.tcp_keepalive_probes=5

More details at http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-firewall-guidance.html#connecting-firewall-guidance.change-tcpip-settings

[gaspaonrocks]

Ok i'm don't know if anyone thought of this, but it was the mistake i made so i'm gonna share it.

What didn't work wasn't sequelize, but the fact that i didn't have a mysql server running... sqlite doesn't require one so it works.

It may be obvious to some but i figured it out only recently.

[qeesung]

+1

SequelizeConnectionError: connect ETIMEDOUT
...

[qeesung]

If there are any plan to resolve this issue ? Our service will crash after running for some time. T_T

[OliverLonghi]

@qeesung did you tried this?
http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-firewall-guidance.html#connecting-firewall-guidance.change-tcpip-settings

[Jfeng3]

@OliverLonghi does not work for me

[Jfeng3]

same error, on aws ec2. anyone can help?

[rsshilli]

If any of you are seeing this error consistently, you have a firewall problem. I guarantee it. Something, somewhere is stopping the connection. It mostly likely isn't your Sequelize settings.

With that said, we see this error very rarely, but maybe once every 500 times an AWS Lambda starts up. It only happens when the Lambda is fresh (ie. hasn't been run in over 20 mins) and needs to make an initial connection. Once the Lambda has started it'll re-use the old connection and this problem will never happen.

We're running on Sequelize 3.30.4, Node 6.10.3 and MySQL ^2.13.0. My connection pool settings look like this:

    let sequelize = new Sequelize(dbName, dbUsername, dbPassword, {
      host: dbHost,
      dialect: "mysql",

      pool: {
        max: 5,
        min: 0,
        idle: 1 // Keep this very low or it'll make all Lambda requests take longer
      },
    });

What's even stranger, is that I have other Lambdas that share the exact same startup code (I mean exactly the same zip, different method) that startup a second or two before I see this error and they run fine. They're all connecting to the same RDS database, which shows about 5 DB connections at the time (not high at all) nor a high load on the DB. It's very strange.

Here's the full stack trace:

2017-11-22T06:57:47.709Z	66946aa0-cf52-11e7-b612-6d9d4421107e	Error received: SequelizeConnectionError: connect ETIMEDOUT SequelizeConnectionError: connect ETIMEDOUT
at Handshake._callback (/var/task/node_modules/sequelize/lib/dialects/mysql/connection-manager.js:95:20)
at Handshake.Sequence.end (/var/task/node_modules/mysql/lib/protocol/sequences/Sequence.js:88:24)
at Protocol.handleNetworkError (/var/task/node_modules/mysql/lib/protocol/Protocol.js:363:14)
at Connection._handleNetworkError (/var/task/node_modules/mysql/lib/Connection.js:428:18)
at Connection._handleConnectTimeout (/var/task/node_modules/mysql/lib/Connection.js:424:8)
at Socket.g (events.js:292:16)
at emitNone (events.js:86:13)
at Socket.emit (events.js:185:7)
at Socket._onTimeout (net.js:338:8)
at ontimeout (timers.js:386:14)
at tryOnTimeout (timers.js:250:5)
at Timer.listOnTimeout (timers.js:214:5)

I suspect there's something wrong at the connection pool level, but I'm not sure how to diagnose this further. Any ideas?

[rsshilli]

I seem to have fixed my problem by upgrading MySQL from 2.13.0 to 2.15.0 (currently the latest). I haven't seen this in 3 weeks since doing that.

[johnreynolds]

I'm experiencing the same problem when using AWS Lightsail connecting to and RDS instance. I haven't solved it yet, but this snippet from the docs is helping:
sequelize
.authenticate()
.then(() => {
console.log('Connection has been established successfully.');
})
.catch(err => {
console.error('Unable to connect to the database:', err);
})

[lordinferno1]

As @rsshilli said, something was blocking the connection, in my case, i had to allow inbound connections from heroku ip to my rds database in AWS.
Also, im not sure if setting DATABASE_URL in Heroku config variable helped

[sharq88]

We've struggled with this issue for quite long, but worked around with the below at the end:

let sequelize = new Sequelize(dbName, dbUsername, dbPassword, {
	host: dbHost,
	...
	retry  : {
		match: [
			/ETIMEDOUT/,
			/EHOSTUNREACH/,
			/ECONNRESET/,
			/ECONNREFUSED/,
			/ETIMEDOUT/,
			/ESOCKETTIMEDOUT/,
			/EHOSTUNREACH/,
			/EPIPE/,
			/EAI_AGAIN/,
			/SequelizeConnectionError/,
			/SequelizeConnectionRefusedError/,
			/SequelizeHostNotFoundError/,
			/SequelizeHostNotReachableError/,
			/SequelizeInvalidConnectionError/,
			/SequelizeConnectionTimedOutError/
		],
		max  : 5
	}
});

[dts-peter]

I've encountered the same issue while using Amazon RDS (Aurora PostreSQL). I've tweaked inbound connections for the security group and everything is working like a charm now.

[jared-evari]

Hi @dts-peter, what do you mean by "tweaked inbound connections for the security group"? What did you change?

[dts-peter]

@jared-evari You need to update your inbound connections. Go to the databases list in your AWS Console, select the database that you need to update, then do a search on the page for 'VPC security groups' (it's under the 'Security' label). You will see the link for the security group under the 'VPC security groups'. Open that link. In the bottom of the page, select 'Inbound' tab. Press 'Edit', then 'Add Rule'. Enter the following values:
Type: Custom TCP, Protocol: TCP, Port Range: 0, Source: Custom, IP addresses field: 0.0.0.0/0, ::/0, Description: you can leave this empty. Press 'Save'.

[wtesler]

My situation was like what @dts-peter said, but with Google Cloud SQL.

[rsshilli]

In addition to @sharq88 's options above, this one helps too:

  dialectOptions: {
    connectTimeout: 20000, // default is 10s which causes occasional ETIMEDOUT errors (see https://stackoverflow.com/a/52465919/491553)
  },

Here's the link to the SO page, so you don't have to copy & paste: https://stackoverflow.com/a/52465919/491553

[fromerosk]

These issues with sequelize timeout keep getting closed but no solution have ever being posted.
Many are closed with "work-around" that most of the times doesn't work.

[piotr-pawlowski]

My situation was like what @dts-peter said, but with Google Cloud SQL.

How did you resolve it?

[marcogrcr]

For people experiencing issues in AWS Lambda, I have created a pull request that documents how to properly configure sequelize for Lambda. Hopefully you'll be able to see it in https://sequelize.org/master/manual/aws-lambda.html once it gets merged. In the meantime, please check #12642 out and provide feedback if you find any inaccuracies or space for improvement.

[UdittLamba]

For people experiencing issues in AWS Lambda, I have created a pull request that documents how to properly configure sequelize for Lambda. Hopefully you'll be able to see it in https://sequelize.org/master/manual/aws-lambda.html once it gets merged. In the meantime, please check #12642 out and provide feedback if you find any inaccuracies or space for improvement.

This is a great resource. Thanks for the help @marcogrcr.

[NDEDE]

at Socket.emit (node:events:527:28)
at Socket.emit (node:domain:475:12)
at emitErrorNT (node:internal/streams/destroy:157:8)
at emitErrorCloseNT (node:internal/streams/destroy:122:3)
at processTicksAndRejections (node:internal/process/task_queues:83:21)
[ERROR] 07:16:28 SequelizeConnectionError: connect ETIMEDOUT 34.226.129.116:5432

Am also having the same issue kindly can you assist

[mikhaillampu]

In my case I had this issue in for loop when not using AWAIT for async operation. As you know, this leads to possibility of firing dozens of promises almost at one moment and some (just one, for instance) of them can easily be rejected because of db connection pool overlimit.
Firstly while investigating I added:
process.on('uncaughtException', error => console.log({ errorOnProccess: process.pid, error }))
This already could help my issue because logic has become to keep working.
And then I just found a place in code, that could fire hundreds of promises at one time because of no 'await' (there are about 20.000 elements in for loop here, and could be up to several thousands of db requests). And, as this logic is not intended to be fast, I just added 'await' - and voi la, no errors more.