重现MySQL错误：服务器关闭了连接（node.js）

Question

我试图重现我在EC2上的node.js应用中看到的MySQL错误，其中包含节点mysql库：

连接丢失：服务器关闭了连接。

我无法在本地重现该错误-通过我的代码可以很好地处理数据库-
它仅每隔几秒钟重新检查一次，并在重新启动后重新连接到db。在EC2上，它发生在太平洋时间凌晨4点左右，但是数据库仍然正常运行。

我想

1. 用我的本地mysql重现崩溃
2. 在我的mysql帮助器模块中添加我需要的任何逻辑

这是我的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;
};

Answer 1

这是我最终使用的，效果很好。偶尔连接丢失/重新启动，恢复得很好。我有一个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;