23

I'm running a Node server connecting to MySQL via the node-mysql module. Connecting to and querying MySQL works great initially without any errors, however, the first query after leaving the Node server idle for a couple hours results in an error. The error is the familiar read ECONNRESET, coming from the depths of the node-mysql module.

A stack trace (note that the three entries of the trace belong to my app's error reporting code):

Error
at exports.Error.utils.createClass.init (D:\home\site\wwwroot\errors.js:180:16)
at new newclass (D:\home\site\wwwroot\utils.js:68:14)
at Query._callback (D:\home\site\wwwroot\db.js:281:21)
at Query.Sequence.end (D:\home\site\wwwroot\node_modules\mysql\lib\protocol\sequences\Sequence.js:78:24)
at Protocol.handleNetworkError (D:\home\site\wwwroot\node_modules\mysql\lib\protocol\Protocol.js:271:14)
at PoolConnection.Connection._handleNetworkError (D:\home\site\wwwroot\node_modules\mysql\lib\Connection.js:269:18)
at Socket.EventEmitter.emit (events.js:95:17)
at net.js:441:14
at process._tickCallback (node.js:415:13)

This error happens both on my cloud Node server and MySQL server as well as a local setup of both.

My questions:

  1. Does this problem appear to be a disconnection of Node's connection to my MySQL server(s), perhaps due to a connection lifetime limitation?

  2. When using connection pools, node-mysql is supposed to gracefully handle disconnections and prune them from the pool. Is it not aware of the disconnect until I make a query, thus making the error unavoidable?

  3. Considering that I see the "read ECONNRESET" error a lot in other StackOverflow posts, should I be looking elsewhere from MySQL to diagnose the problem?

Update: After more browsing, I think my issue is a duplicate of this one. It appears his connection is disconnecting as well, but no one has suggested how to keep the connection alive or how to address the error outside of failing on the first query back.

Community
  • 1
  • 1
Brent Traut
  • 5,614
  • 6
  • 29
  • 54

4 Answers4

22

I reached out to the node-mysql folks on their Github page and got some firm answers.

  1. MySQL does indeed prune idle connections. There's a MySQL variable "wait_timeout" that sets the number of second before timeout and the default is 8 hours. We can set the default to be much larger than that. Use show variables like 'wait_timeout'; to view your timeout setting and set wait_timeout=28800; to change it.

  2. According to this issue, node-mysql doesn't prune pool connections after these sorts of disconnections. The module developers recommended using a heartbeat to keep the connection alive such as calling SELECT 1; on an interval. They also recommended using the node-pool module and its idleTimeoutMillis option to automatically prune idle connections.

Brent Traut
  • 5,614
  • 6
  • 29
  • 54
11

If this happens when establishing a single reused connection, it can be avoided by establishing a connection pool instead.

For example, if you're doing something like this...

var db = require('mysql')
  .createConnection({...})
  .connect(function(err){});

do this instead...

var db = require('mysql')
  .createPool({...});
Ben Thielker
  • 4,104
  • 4
  • 21
  • 20
  • 1
    Whether using a single connection or a pool, I was still running into this issue. As my accepted answer outlines, it was a symptom of MySQL hanging up and node-mysql not realizing it. This may have been fixed since then, as it's been a number of years since I've used it. – Brent Traut Jul 12 '16 at 18:44
  • 2
    Thanks for the insight. I found this post when encountering this same error recently. Switching to use of a connection pool resolved it for me, so I posted this to help anybody else in the same situation. – Ben Thielker Jul 14 '16 at 06:58
0

Does this problem appear to be a disconnection of Node's connection to my MySQL server(s), perhaps due to a connection lifetime limitation?

Yes. The server has closed its end of the connection.

When using connection pools, node-mysql is supposed to gracefully handle disconnections and prune them from the pool. Is it not aware of the disconnect until I make a query, thus making the error unavoidable?

Correct, but it should handle the error internally, not pass it back to you. This appears to be a bug in node-mysql. Report it.

Considering that I see the "read ECONNRESET" error a lot in other StackOverflow posts, should I be looking elsewhere from MySQL to diagnose the problem?

It is either a bug in the node-MySQL connection pool implementation, o else you haven't configured it properly to detect failures.

user207421
  • 305,947
  • 44
  • 307
  • 483
  • Thanks! I've reported the issue on [node-mysql's Github page](https://github.com/felixge/node-mysql/issues/778). I suspect you're right, but I'd love to get absolute confirmation from their developers that this is a bug and not a mistake in my app code. – Brent Traut Apr 06 '14 at 23:42
  • Well I know nothing about node-MySQL, but other connection pools allows you to define a dummy SQL statement such as 'SELECT 1' that the pool can use to test whether a connection is still alive before returning it to you. You need to investigate whether you've done that correctly, or at all. – user207421 Apr 06 '14 at 23:54
0

I have been also facing the same issue. Apparently it was happening because one of the backend process has been triggered on table which was being referred in my api.

This caused table to go in lock wait state and my query request got failed with connection reset. Though i'm wondering why i didn't receive lock wait error .

Raj Techie
  • 1
  • 2
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 12 '22 at 13:33