ECONNRESET after idle time on node

[btraut]

I've been running into an issue over the last couple of days that has been difficult for me to diagnose. I've posted on StackOverflow and also found a similar issue, but I was hoping you guys would be able to help as well.

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?

[kwangsu61]

1. show variables like 'wait_timeout'; => wait_timeout = 28800 sec.
   Mysql will remove the idle connection during wait_timeout.
2. I think that node-mysql pool doesn't support such the function.
   If you use https://github.com/coopernurse/node-pool as connection pool, you can use idleTimeoutMillis option.
   @dougwilson How about supporting 'idleTimeoutMillis' option for the pool?
3. See Pool does not reconnect after longer disconnects. #464

[btraut]

Yes! Thank you for getting back so quickly.

I'll likely set the wait_timeout var much higher on my server for now. I'll hold off setting up node-pool for now to see if you guys are interested in adding a keep-alive method/interval into your own connections and pools.

[sidorares]

You should not treat connection as reliable even after increasing wait_timeout. I'm not sure what is current pool behavior re server side end closing (probably should be 'dispose connection and spawn new one if current number is now less than desired min'), but it's quite possible that server dropped connection and client is completely not aware of this ( 'fin' packet lost in space ). You have to be prepared to handle this in client: get connection from pool, try command, receive error, retry.

[dougwilson]

@kwangsu61 I can certainly get a idleTimeoutMillis (or other-named config) into the pool here.

But in principal, I agree with @sidorares in that something weird may be happening that is making the pool hand out a broken connection. I'll also try to look over the implementation to see what could go wrong here. An idleTimeoutMillis-like pool option is only a bandage if there is something we can do to make this automatic :) idleTimeoutMillis's use-case is actually just to keep the number of sleeping connection on your db low, rather than to avoid this issue.

[kwangsu61]

@sidorares @dougwilson Thank you so much for your suggestion.
Because of mysql default configuration(wait_timeout), this is a common issue.
It'll be good if this issue is covered in node-mysql library.

[dougwilson]

Because of mysql default configuration(wait_timeout), this is a common issue.

You'd think so, but running his module with whatever the default is never triggers this behavior for me, which is why I'm wondering if there are some disconnect cases we are not handling in the pool. I'll be adding some tests for various disconnect scenarios to find out.

[moappi]

Figure I would chime in and I've been getting the same error (very intermittently) while trying to query using connection pooling

read ECONNRESET

Been using the following code snipet with manage connections

pool.getConnection(function(err, connection) {
  // Use the connection
  connection.query( 'SELECT something FROM sometable', function(err, rows) {
    // And done with the connection.
    connection.release();

    // Don't use the connection here, it has been returned to the pool.
  });
});

Any headway with this? or is it recommended to implement failed retries as per sidorares recommendation

[dougwilson]

@moappi the code you posted is exactly what you are supposed to be doing. Are you saying that fixes the issue? If so, what where you doing before? Perhaps the reason we are not seeing the issue is that we don't know what weird things people are doing with this library that we may consider "bad" but users do not know?

[moappi]

Nope the code above actually caused the issue. Earlier we were weren't using connection pooling (creating a new connection for every query) and we never had any issues (over the past couple years).

But like I mentioned it's very intermittent (occurred on our servers just yesterday with multiple read ECONNRESET for consecutive mysql queries in a row). Solved the issue by re-booting our node service.

No idea exactly how to recreate the issue, but will spend some time to see if I can recreate it and I'll post the findings here.

[dougwilson]

Nope the code above actually caused the issue. we were weren't using connection pooling (creating a new connection for every query) and we never had any issues (over the past couple years).

Ah, OK, I wasn't able to tell from the post.

Solved the issue by re-booting our node service.

I assume this means the node process, not like the machine (or VM)?

[dougwilson]

@moappi as an additional question, when you encountered these errors, what did the stack trace look like? Did it look like the OP or something else?

[moappi]

@dougwilson so to answer your questions .. yes it was the node process that we rebooted and not the VM. I don't have access to the full stack trace but it is something that I can generate if (or when) it happens again. Not exactly sure what you mean by OP

[dougwilson]

Not exactly sure what you mean by OP

The original post, as in the first comment at the top of this page.

[moappi]

ahh no it doesn't namely because I'm currently unable to see the entire stack track .. let me enable the stack trace and then see if I can reproduce .. fingers crossed!