mysql连接丢失错误nodejs
我使用下面的代码将我的节点连接到mysql,用于我在我的项目中使用的所有休息apis; 我已经把它作为我所有查询请求的常用数据库连接文件。mysql连接丢失错误nodejs
var mysql = require('mysql');
var db_connect = (function() {
function db_connect() {
mysqlConnConfig = {
host: "localhost",
user: "username",
password: "password",
database: "db_name"
};
}
db_connect.prototype.unitOfWork = function (sql) {
mysqlConn = mysql.createConnection(mysqlConnConfig);
try {
sql(mysqlConn);
} catch (ex) {
console.error(ex);
} finally {
mysqlConn.end();
}
};
return db_connect;
})();
exports.db_connect = db_connect;
上面的代码工作正常,我将使用我的查询执行'sql'如下所示在我所有的休息API如下。
var query1 = "SELECT * FROM table1";
sql.query(query1,function(error,response){
if(error){
console.log(error);
}
else{
console.log(response);
}
})
一切都很好至今,但问题是我正在8-12小时运行后得到的SQL协议连接错误 我永远的模块
forever start app.js
我开始我的项目与上述永远的模块。 8-12小时后,我得到了下面的错误,我所有的休息api都无法正常工作或正在关闭。
"stack": ["Error: Connection lost: The server closed the connection.", " at Protocol.end (/path/to/my/file/node_modules/mysql/lib/protocol/Protocol.js:109:13)", " at Socket.<anonymous> (/path/to/my/file/node_modules/mysql/lib/Connection.js:102:28)", " at emitNone (events.js:72:20)", " at Socket.emit (events.js:166:7)", " at endReadableNT (_stream_readable.js:913:12)", " at nextTickCallbackWith2Args (node.js:442:9)", " at process._tickDomainCallback (node.js:397:17)"],
"level": "error",
"message": "uncaughtException: Connection lost: The server closed the connection.",
"timestamp": "2017-09-13T21:22:25.271Z"
然后,我就在我的研究解决如下配置手柄断开。 但我正在努力配置我的sql连接如下所示与我的代码。
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();
任何人都可以帮我改变我的代码与上述代码?
SHOW SESSION VARIABLES LIKE '%wait_timeout';
SHOW GLOBAL VARIABLES LIKE '%wait_timeout';
其中之一被设置为28800(8小时)。增加它。
或...抓住错误并重新连接。
或...检查在您的框架中如何处理“连接池”。
但是...请注意,可能会出现网络故障。所以,简单地增加超时将不会处理这样的故障。
或......不要挂在连接上这么久。它不是很好玩。 和它可能导致超过max_connections
。
(对不起,我不明白你的应用不够好,具体谈谈这些多路径的去追求它。)
MAX_CONNECTIONS
...wait_timeout
和max_connections
一起走了笨拙的方式。如果超时时间“太高”,连接数量可能会持续增长,从而威胁到“连接太多”的错误。在典型的设计中,最好降低超时以防止客户过度浪费连接到连接上。
如果你的情况是这样的:“固定希望永远保持连接的客户端数”,则增加超时,但不max_connections
(至少不是远远超出了固定数量的客户端)。
不过,如果网络打嗝,连接可能会中断。所以,你仍然可以“失去联系”。 (但是,如果一切都在同一台机器上,这是不太可能的。)
它似乎在你面前提到你的代码工作正常,我建议的方法:
1 - 更改连接你的MySQL服务器空闲超时(防止断开)
2,发送保活或每小时简单查询。
最后,每次断开连接时更改您的代码以重新连接。
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
connection.query("SELECT * FROM Mytable",function(err, rows, fields) {
// Get your query result here
});
}); // 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();
我已经通过池连接解决了这个问题。尝试在这样https://www.npmjs.com/package/mysql
var mysql = require('mysql');
var pool = mysql.createPool(...);
pool.getConnection(function(err, connection) {
// Use the connection
connection.query('SELECT something FROM sometable', function (error, results, fields) {
// And done with the connection.
connection.release();
// Handle error after the release.
if (error) throw error;
// Don't use the connection here, it has been returned to the pool.
});
});
是增加那些超时会给任何其他的错误? – Jagadeesh
你认为这将是我的问题的原因? – Jagadeesh
增加了关于max_connections –