我试图重现我在EC2上的node.js应用中看到的MySQL错误,其中包含节点mysql库:
连接丢失:服务器关闭了连接。
我无法在本地重现该错误-通过我的代码可以很好地处理数据库- 它仅每隔几秒钟重新检查一次,并在重新启动后重新连接到db。在EC2上,它发生在太平洋时间凌晨4点左右,但是数据库仍然正常运行。
我想
这是我的node.js应用程序中的错误:
2012-10-22T08:45:40.518Z-错误: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,版本= 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,trace = [列= 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,line = 88,method = emit,native = false,column = 51,file = net.js,function = TCP.onread,行= 388,方法= onread,本机= false,堆栈= [错误:连接丢失:服务器关闭了连接。 在Socket.onend(stream.js:80:10)的Protocol.end(/home/ec2-user/my- app/node_modules/mysql/lib/protocol/Protocol.js:63:13)处。在TCP.onread(net.js:388:51)发出(events.js:88:20)]
这是我的代码(mysql帮助程序模块):
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文件,它建立连接并定期检查它们。
提出要求:
var conn = require('./database'); var sql = 'SELECT foo FROM bar;'; conn.query(sql, [userId, plugId], function (err, rows) { // logic }
这是我的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;