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

问题描述

我正在运行通过 node-mysql 模块连接到 MySQL 的 Node 服务器.最初连接和查询 MySQL 效果很好，没有任何错误，但是，在使 Node 服务器空闲几个小时后的第一次查询会导致错误.错误是熟悉的read ECONNRESET，来自node-mysql模块的深处.

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)

此错误发生在我的云节点服务器和 MySQL 服务器以及两者的本地设置上.

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

我的问题:

1. 这个问题是不是因为 Node 与我的 MySQL 服务器的连接断开了，可能是由于连接生命周期的限制?

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?

当使用连接池时，node-mysql 应该优雅地处理断开连接并从池中修剪它们.是不是在我进行查询之前不知道断开连接，从而使错误不可避免?

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?

考虑到我在其他 StackOverflow 帖子中经常看到read ECONNRESET"错误，我是否应该从 MySQL 中寻找其他地方来诊断问题?

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.

推荐答案

我通过 他们的 Github 页面 并得到了一些肯定的答案.

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

1. MySQL 确实会修剪空闲连接.有一个 MySQL 变量wait_timeout"，它设置超时前的秒数，默认值为 8 小时.我们可以将默认值设置得比这大得多.使用 show variables like 'wait_timeout'; 查看您的超时设置，并使用 set wait_timeout=28800; 更改它.

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.

根据这个问题，node-mysql 没有在这些类型的断开连接后修剪池连接.模块开发人员建议使用心跳来保持连接处于活动状态，例如每隔一段时间调用 SELECT 1; .他们还建议使用 node-pool 模块 及其 idleTimeoutMillis 选项来自动修剪空闲连接.

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屋！