复制MySQL错误:服务器关闭了连接(node.js)

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (26)

我正在尝试复制我在EC2上的node.js应用程序中看到的MySQL错误,节点MySQL库:

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

我无法在本地复制这个错误--我的代码很好地处理了数据库--它每隔几秒钟就重新检查一次,并在重新启动后重新连接到db。在EC2上,它发生在太平洋04:00左右,但db仍然正常运行。

我想

  1. 用本地MySQL再现崩溃。
  2. 在MySQL助手模块中添加任何我需要的逻辑来处理这个问题

以下是我的node.js应用程序中的错误:

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助手模块):

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;
};
提问于
用户回答回答于

我遇到了类似的问题,创建了一个getConnection()包装函数,它在将MySQL连接返回给调用者并根据需要重新建立连接之前,检查MySQL连接的运行情况。在我的测试中,它透明地处理了应用程序的致命和非致命连接问题。如果连接只是超时,应用程序就会恢复,而不会发生任何错误。如果存在短暂但致命的数据库连接问题,一旦再次可用数据库连接,应用程序将自动恢复工作。

为了重新生成测试问题,请将以下两行添加到my.ini或my.cnf文件中的[mysqld]区块:

interactive_timeout=30
wait_timeout=30

以下是我命名为“database.js”的文件的内容:

var mysql = require("mysql");
var CONFIG = require(__dirname + "/configuration");

module.exports.getConnection = function() {
    // Test connection health before returning it to caller.
    if ((module.exports.connection) && (module.exports.connection._socket)
            && (module.exports.connection._socket.readable)
            && (module.exports.connection._socket.writable)) {
        return module.exports.connection;
    }
    console.log(((module.exports.connection) ?
            "UNHEALTHY SQL CONNECTION; RE" : "") + "CONNECTING TO SQL.");
    var connection = mysql.createConnection({
        host     : CONFIG.db.host,
        user     : CONFIG.db.user,
        password : CONFIG.db.password,
        database : CONFIG.db.database,
        port     : CONFIG.db.port
    });
    connection.connect(function(err) {
        if (err) {
            console.log("SQL CONNECT ERROR: " + err);
        } else {
            console.log("SQL CONNECT SUCCESSFUL.");
        }
    });
    connection.on("close", function (err) {
        console.log("SQL CONNECTION CLOSED.");
    });
    connection.on("error", function (err) {
        console.log("SQL CONNECTION ERROR: " + err);
    });
    module.exports.connection = connection;
    return module.exports.connection;
}

// Open a connection automatically at app startup.
module.exports.getConnection();

// If you've saved this file as database.js, then get and use the
// connection as in the following example:
// var database = require(__dirname + "/database");
// var connection = database.getConnection();
// connection.query(query, function(err, results) { ....
用户回答回答于

MySQL池功能。节点-MySQL

var mysql = require('mysql');
var pool  = mysql.createPool({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret'
});

pool.getConnection(function(err, connection) {
  // connected! (unless `err` is set)
  connection.end();
});

扫码关注云+社区