MySQL给出“读取ECONNRESET”在node.js服务器上空闲时间后出现错误 [英] MySQL giving "read ECONNRESET" error after idle time on node.js server

问题描述

我运行的节点服务器通过node-mysql模块连接到MySQL。连接和查询MySQL最初没有任何错误，但是，第一个查询离开节点服务器空闲几个小时后导致错误。错误是熟悉的读取ECONNRESET ，来自node-mysql模块的深度。

（请注意，跟踪的三个条目属于我的应用程序的错误报告代码）：

 导出时出错
。 Error.utils.createClass.init（D：\home\site\wwwroot\errors.js：180：16）
 at new newclass（D：\home\site\wwwroot\ utils.js：68：14）
在Query._callback（D：\home\site\wwwroot\db.js：281：21）
在Query.Sequence.end ：\home\site\wwwroot\\\
ode_modules\mysql\lib\protocol\sequences\Sequence.js：78：24）
在Protocol.handleNetworkError（D：\home\\ \\site\wwwroot\\\
ode_modules\mysql\lib\protocol\Protocol.js：271：14）
在PoolConnection.Connection._handleNetworkError（D：\home\site\wwwroot\\ \\ node_modules\mysql\lib\Connection.js：269：18）
在Socket.EventEmitter.emit（events.js：95：17）
在net.js：441：14 
 at process._tickCallback（node.js：415：13）
  

我的云节点服务器和MySQL服务器以及两个本地设置。

我的问题：

1. 这个问题似乎是因为连接终止限制而导致Node与我的MySQL服务器断开连接吗？




2. 当使用连接池时，node-mysql应该优雅地处理断开连接并从池中删除它们。




3. 考虑到我看到读取ECONNRESET错误，我发现很多错误。

更新：经过更多浏览，我认为我的问题是这一个的重复。看来他的连接也断开连接，但没有人建议如何保持连接活着或如何解决在第一次查询失败之外的错误。

解决方案

我在他们的Github页上接触到node-mysql的人，

1. MySQL确实修剪空闲连接。有一个MySQL变量wait_timeout设置超时前的秒数，默认值为8小时。我们可以设置默认值远远大于那个。使用显示诸如'wait_timeout'; 等变量以查看您的超时设置，并且 set wait_timeout = 28800; / p>




2. 根据这个问题，node-mysql不会在这些类型的断开之后修剪池连接。模块开发人员建议使用心跳保持连接活动，例如在一个间隔上调用 SELECT 1; 。他们还建议使用节点池模块及其idleTimeoutMillis选项自动修剪空闲连接。 p>

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.

解决方案

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.

这篇关于MySQL给出“读取ECONNRESET”在node.js服务器上空闲时间后出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！