nodejs mysql 错误：连接丢失 服务器关闭了连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20210522/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
nodejs mysql Error: Connection lost The server closed the connection
提问by HymanieLin
when I use node mysql, an error is appear between 12:00 to 2:00 that the TCP connection is shutdown by the server. This is the full message:
当我使用 node mysql 时，在 12:00 到 2:00 之间出现错误，TCP 连接被服务器关闭。这是完整的消息：
Error: Connection lost: The server closed the connection.
at Protocol.end (/opt/node-v0.10.20-linux-x64/IM/node_modules/mysql/lib/protocol/Protocol.js:73:13)
at Socket.onend (stream.js:79:10)
at Socket.EventEmitter.emit (events.js:117:20)
at _stream_readable.js:920:16
at process._tickCallback (node.js:415:13)
There is the solution. However, after I try by this way, the problem also appear. now I do not know how to do. Does anyone meet this problem?
有解决方案。但是，我按照这种方式尝试后，问题也出现了。现在我不知道该怎么办。有没有人遇到这个问题？
Here is the way I wrote follow the solution:
这是我按照解决方案编写的方式：
var handleKFDisconnect = function() {
kfdb.on('error', function(err) {
if (!err.fatal) {
return;
}
if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
console.log("PROTOCOL_CONNECTION_LOST");
throw err;
}
log.error("The database is error:" + err.stack);
kfdb = mysql.createConnection(kf_config);
console.log("kfid");
console.log(kfdb);
handleKFDisconnect();
});
};
handleKFDisconnect();
回答by CloudyMarble
Try to use this codeto handle server disconnect:
尝试使用此代码来处理服务器断开连接：
var db_config = {
host: 'localhost',
user: 'root',
password: '',
database: 'example'
};
var connection;
function handleDisconnect() {
connection = mysql.createConnection(db_config); // Recreate the connection, since
// the old one cannot be reused.
connection.connect(function(err) { // The server is either down
if(err) { // or restarting (takes a while sometimes).
console.log('error when connecting to db:', err);
setTimeout(handleDisconnect, 2000); // We introduce a delay before attempting to reconnect,
} // to avoid a hot loop, and to allow our node script to
}); // process asynchronous requests in the meantime.
// If you're also serving http, display a 503 error.
connection.on('error', function(err) {
console.log('db error', err);
if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually
handleDisconnect(); // lost due to either server restart, or a
} else { // connnection idle timeout (the wait_timeout
throw err; // server variable configures this)
}
});
}
handleDisconnect();
In your code i am missing the parts after connection = mysql.createConnection(db_config);
在您的代码中，我缺少以下部分 connection = mysql.createConnection(db_config);
回答by Gajus
I do not recall my original use case for this mechanism. Nowadays, I cannot think of any valid use case.
我不记得我对这种机制的原始用例。如今，我想不出任何有效的用例。
Your client should be able to detect when the connection is lost and allow you to re-create the connection. If it important that part of program logic is executed using the same connection, then use transactions.
您的客户端应该能够检测到连接何时丢失并允许您重新创建连接。如果使用相同的连接执行部分程序逻辑很重要，则使用事务。
tl;dr; Do not use this method.
tl;博士; 不要使用这种方法。
A pragmatic solution is to force MySQL to keep the connection alive:
一个实用的解决方案是强制 MySQL 保持连接处于活动状态：
setInterval(function () {
db.query('SELECT 1');
}, 5000);
I prefer this solution to connection pool and handling disconnect because it does not require to structure your code in a way thats aware of connection presence. Making a query every 5 seconds ensures that the connection will remain alive and PROTOCOL_CONNECTION_LOSTdoes not occur.
我更喜欢这个解决方案而不是连接池和处理断开连接，因为它不需要以一种知道连接存在的方式来构建你的代码。每 5 秒进行一次查询可确保连接保持活动状态并且PROTOCOL_CONNECTION_LOST不会发生。
Furthermore, this method ensures that you are keeping the same connection alive, as opposed to re-connecting. This is important. Consider what would happen if your script relied on LAST_INSERT_ID()and mysql connection have been reset without you being aware about it?
此外，此方法可确保您保持相同的连接处于活动状态，而不是重新连接。这个很重要。考虑一下如果您的脚本依赖LAST_INSERT_ID()并且 mysql 连接在您不知道的情况下被重置会发生什么？
However, this only ensures that connection time out (wait_timeoutand interactive_timeout) does not occur. It will fail, as expected, in all others scenarios. Therefore, make sure to handle other errors.
但是，这只能确保不会发生连接超时 (wait_timeout和interactive_timeout)。正如预期的那样，它会在所有其他情况下失败。因此，请确保处理其他错误。
回答by Simon Fearby
To simulate a dropped connection try
要模拟断开的连接，请尝试
connection.destroy();
More information here: https://github.com/felixge/node-mysql/blob/master/Readme.md#terminating-connections
更多信息在这里：https: //github.com/felixge/node-mysql/blob/master/Readme.md#termination-connections
回答by Aleks Towers
Creating and destroying the connections in each query maybe complicated, i had some headaches with a server migration when i decided to install MariaDB instead MySQL. For some reason in the file etc/my.cnf the parameter wait_timeout had a default value of 10 sec (it causes that the persistence can't be implemented). Then, the solution was set it in 28800, that's 8 hours. Well, i hope help somebody with this "güevonada"... excuse me for my bad english.
在每个查询中创建和销毁连接可能很复杂，当我决定安装 MariaDB 而不是 MySQL 时，我对服务器迁移感到有些头疼。由于某些原因，在文件 etc/my.cnf 中，参数 wait_timeout 的默认值为 10 秒（这会导致无法实现持久性）。然后，解决方案设置为28800，即8小时。好吧，我希望能帮助某人解决这个“güevonada”...请原谅我的英语不好。
回答by Lalgulab Khatkar
better solution is to use pool - ill handle this for you.
更好的解决方案是使用池 - 我会为你处理这个问题。
const pool = mysql.createPool({
host: 'localhost',
user: '--',
database: '---',
password: '----'
});
// ... later
pool.query('select 1 + 1', (err, rows) => { /* */ });