重现MySQL错误:服务器关闭了连接(node.js) [英] Reproduce MySQL error: The server closed the connection (node.js)
问题描述
我正在尝试使用节点mysql库在EC2上的node.js应用中重现MySQL错误:
连接丢失:服务器关闭了连接.
Connection lost: The server closed the connection.
我无法在本地重现该错误-通过我的代码可以很好地处理数据库-它仅每隔几秒钟重新检查一次，并在重新启动后重新连接到db.在EC2上，它发生在太平洋时间凌晨4点左右，但是数据库仍然正常运行.
I am unable to reproduce the error locally- killing the database is handled just fine by my code- it just rechecks every few seconds and reconnects to the db once it is restarted. On EC2, it happens around 4am Pacific, but the db is still up and running fine.
我想
- 使用我的本地mysql重现崩溃
- 在我的mysql帮助器模块中添加处理此问题所需的任何逻辑
这是我的node.js应用程序中的错误:
Here's the error in my node.js app:
2012-10-22T08:45:40.518Z-错误:uncaughtException日期=周一10月22日 2012 08:45:40 GMT + 0000(UTC)，pid = 14184，uid = 0，gid = 0， cwd =/home/ec2-user/my-app，execPath =/usr/bin/nodejs， 版本= v0.6.18，argv = [/usr/local/bin/node， /home/ec2-user/my-app/app.js，--my-app]，rss = 15310848， heapTotal = 6311392，heapUsed = 5123292，loadavg = [0.0029296875， 0.0146484375、0.04541015625]，正常运行时间= 3238343.511107486，跟踪= [列= 13， 文件=/home/ec2-user/my-app/node_modules/mysql/lib/protocol/Protocol.js， function = Protocol.end，line = 63，method = end，native = false，column = 10， file = stream.js，function = Socket.onend，line = 80，method = onend， native = false，column = 20，file = events.js，function = Socket.emit， 行= 88，方法=发射，本机=否，列= 51，文件= net.js， function = TCP.onread，line = 388，method = onread，native = false]， stack = [错误:连接丢失:服务器关闭了连接.，
在Protocol.end (/home/ec2-user/my-app/node_modules/mysql/lib/protocol/Protocol.js:63:13)，位于Socket.onend(stream.js:80:10)，位于Socket.emit (events.js:88:20)，位于TCP.onread(net.js:388:51)]
2012-10-22T08:45:40.518Z - error: uncaughtException date=Mon Oct 22 2012 08:45:40 GMT+0000 (UTC), pid=14184, uid=0, gid=0, cwd=/home/ec2-user/my-app, execPath=/usr/bin/nodejs, version=v0.6.18, argv=[/usr/local/bin/node, /home/ec2-user/my-app/app.js, --my-app], rss=15310848, heapTotal=6311392, heapUsed=5123292, loadavg=[0.0029296875, 0.0146484375, 0.04541015625], uptime=3238343.511107486, trace=[column=13, file=/home/ec2-user/my-app/node_modules/mysql/lib/protocol/Protocol.js, function=Protocol.end, line=63, method=end, native=false, column=10, file=stream.js, function=Socket.onend, line=80, method=onend, native=false, column=20, file=events.js, function=Socket.emit, line=88, method=emit, native=false, column=51, file=net.js, function=TCP.onread, line=388, method=onread, native=false], stack=[Error: Connection lost: The server closed the connection.,
at Protocol.end (/home/ec2-user/my-app/node_modules/mysql/lib/protocol/Protocol.js:63:13), at Socket.onend (stream.js:80:10), at Socket.emit (events.js:88:20), at TCP.onread (net.js:388:51)]
这是我的代码(mysql帮助器模块):
Here's my code (mysql helper module):
module.exports = function (conf,logger) {
var mysql = require('mysql');
var connectionState = false;
var connection = mysql.createConnection({
host: conf.db.hostname,
user: conf.db.user,
password: conf.db.pass,
database: conf.db.schema,
insecureAuth: true
});
function attemptConnection(connection) {
if(!connectionState){
connection = mysql.createConnection(connection.config);
connection.connect(function (err) {
// connected! (unless `err` is set)
if (err) {
logger.error('mysql db unable to connect: ' + err);
connectionState = false;
} else {
logger.info('mysql connect!');
connectionState = true;
}
});
connection.on('close', function (err) {
logger.error('mysqldb conn close');
connectionState = false;
});
connection.on('error', function (err) {
logger.error('mysqldb error: ' + err);
connectionState = false;
/*
if (!err.fatal) {
return;
}
if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
throw err;
}
*/
});
}
}
attemptConnection(connection);
var dbConnChecker = setInterval(function(){
if(!connectionState){
logger.info('not connected, attempting reconnect');
attemptConnection(connection);
}
}, conf.db.checkInterval);
return connection;
};
推荐答案
这是我最终使用的，并且效果很好.偶尔连接丢失/重新启动，恢复得很好.我有一个database.js文件，它可以建立连接并定期检查它们.
Here's what I ended up using, and it worked pretty well. On the occasional connection lost/restart it recovered nicely. I have a database.js file which establishes connections and checks them periodically.
发出请求:
var conn = require('./database');
var sql = 'SELECT foo FROM bar;';
conn.query(sql, [userId, plugId], function (err, rows) {
// logic
}
这是我的databbase.js
Here's my databbase.js
var mysql = require('mysql');
var Common = require('./common');
var conf = Common.conf;
var logger = Common.logger;
var connectionState = false;
var connection = mysql.createConnection({
host: conf.db.hostname,
user: conf.db.user,
password: conf.db.pass,
database: conf.db.schema,
insecureAuth: true
});
connection.on('close', function (err) {
logger.error('mysqldb conn close');
connectionState = false;
});
connection.on('error', function (err) {
logger.error('mysqldb error: ' + err);
connectionState = false;
});
function attemptConnection(connection) {
if(!connectionState){
connection = mysql.createConnection(connection.config);
connection.connect(function (err) {
// connected! (unless `err` is set)
if (err) {
logger.error('mysql db unable to connect: ' + err);
connectionState = false;
} else {
logger.info('mysql connect!');
connectionState = true;
}
});
connection.on('close', function (err) {
logger.error('mysqldb conn close');
connectionState = false;
});
connection.on('error', function (err) {
logger.error('mysqldb error: ' + err);
if (!err.fatal) {
//throw err;
}
if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
//throw err;
} else {
connectionState = false;
}
});
}
}
attemptConnection(connection);
var dbConnChecker = setInterval(function(){
if(!connectionState){
logger.info('not connected, attempting reconnect');
attemptConnection(connection);
}
}, conf.db.checkInterval);
// Mysql query wrapper. Gives us timeout and db conn refreshal!
var queryTimeout = conf.db.queryTimeout;
var query = function(sql,params,callback){
if(connectionState) {
// 1. Set timeout
var timedOut = false;
var timeout = setTimeout(function () {
timedOut = true;
callback('MySQL timeout', null);
}, queryTimeout);
// 2. Make query
connection.query(sql, params, function (err, rows) {
clearTimeout(timeout);
if(!timedOut) callback(err,rows);
});
} else {
// 3. Fail if no mysql conn (obviously)
callback('MySQL not connected', null);
}
}
// And we present the same interface as the node-mysql library!
// NOTE: The escape may be a trickier for other libraries to emulate because it looks synchronous
exports.query = query;
exports.escape = connection.escape;
这篇关于重现MySQL错误:服务器关闭了连接(node.js)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
