25

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.

Improve this question

5 Answers 5

Reset to default
23

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Your issue and answer really helped me. Have you experienced with trying out the node-pool module? Do you perhaps have specific examples for it?
add in /etc/my.cnf wait_timeout=28800; this is working :-)
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({...});
Improve this answer

2 Comments

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.
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.
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?

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.

Improve this answer

3 Comments

Thanks! I've reported the issue on node-mysql's Github page. 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.
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.
almost ten years later and somehow the problem still persist. To properly simulate it I had to set a short wait-timeout value on my development machine. In order to avoid the problem, all I have to do is to close the connection as soon as I am done with it.
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 .

Improve this answer

1 Comment

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.
0

Recently, I was facing a similar issue with MySQL and NestJS API. My application was randomly logging an error like this:

[Nest] 1  - 12/05/2023, 11:53:56 AM   ERROR [ExceptionsHandler] read ECONNRESET
QueryFailedError: read ECONNRESET
    at Query.onResult (/usr/api/src/node_modules/typeorm/driver/mysql/MysqlQueryRunner.js:158:37)
    at PoolConnection._notifyError (/usr/api/src/node_modules/mysql2/lib/connection.js:228:21)
    at PoolConnection._handleFatalError (/usr/api/src/node_modules/mysql2/lib/connection.js:183:10)
    at PoolConnection._handleNetworkError (/usr/api/src/node_modules/mysql2/lib/connection.js:196:10)
    at Socket.emit (node:events:517:28)
    at emitErrorNT (node:internal/streams/destroy:151:8)
    at emitErrorCloseNT (node:internal/streams/destroy:116:3)
    at process.processTicksAndRejections (node:internal/process/task_queues:82:21)

Solved by reducing wait_timeout and interactive_timeout on MySQL database:

set global wait_timeout = 1000;

set global interactive_timeout = 1000;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.