node-mysql2: Pool cannot detect idle connection disconnected by MySQL and will trigger `read ECONNRESET` on newly acquired connection

MySQL will disconnect idle connections after certain time frame.

This time frame is determined by MySQL variable wait_timeout.

It seems mysql2’s pool implementation cannot detect this type of disconnection (ping is not sufficient).

And when it happens, any queries sent through the acquired connection will trigger a read ECONNRESET error.

At this moment, I have to send a SELECT 1 query to test the acquired connection before use.

And previous code that calls pool.query directly will have to be re-written.

Can Mysql2 provides two extra pool creation options:

verifyConnectionInterval: number of seconds before pool should send SELECT 1 query to test connections in the pool.
verifyConnection: boolean. If set true, the pool will test a connection in the pool every verifyConnectionInterval seconds.

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 2
Comments: 31 (19 by maintainers)

Commits related to this issue

keep mysql pool connections alive and functioning, using an undocumented mysql2 feature, see https://github.com/sidorares/node-mysql2/issues/683 — committed to txstate-etc/mysql2-async by wickning1 4 years ago

Most upvoted comments

All right, @sidorares, I added a PR. The PR includes two new configuration parameters: enableKeepAlive and keepAliveInitialDelay. The former defaults to false, and the latter to 0. I opted to keep keep-alive off by default to avoid unexpected side effects in existing code.

https://github.com/sidorares/node-mysql2/pull/1081

benbotto on Dec 13, 2019

@anthonywebb in one of my recent projects I am being hit with the same problem again and I implemented a quick and dirty solution by querying the mysql server with:

setInterval(() => {
  pool.query('select 1')
}, 120000)

It’s ugly but at least it works.

midnightcodr on Apr 28, 2019

@midnightcodr published as v2.1.0

sidorares on Dec 24, 2019

Sure thing, @midnightcodr. Here’s the change: https://github.com/benbotto/node-mysql2/commit/0943dee3bd78e71b2183f6ce4d0a96c50b91d13d

I’m not familiar with the mysql2 code, so I don’t know if this changes works in all scenarios. Note that just above the change there are a few branches in the code–different ways that this.stream is created–and I only tested one of those branches. Works for me ™, YMMV.

benbotto on Dec 12, 2019

I’m seeing this too, and not only due to wait_timeout.

About detecting closed connections, I’ve noticed that the number of free connections (during low-load) matches the number of actual open TCP connections. The pool._allConnections queue still remains the same. That is, ignorant of closed connections. But given that the TCP connections are being closed, it should be fairly straight forward to detect.

There are some notes in this gist.

As you can see from the notes, the amount of FREE connections slowly drops after the pool has reached its connectionLimit. I haven’t looked into why the server is closing the connections, or whether it actually is the server closing them, but it’s fairly common that MySQL does that, so I’d expect the library to just deal with it.

It seems a pretty serious issue, since after all the connections have been dropped - the pool will never regain any of them, practically stalling all subsequent DB queries until the process is restarted.

hallh on Apr 5, 2018