Node.js进程在MySQL关闭后无法恢复，然后打开

Question

我正在使用Node.js与MySQL和restify。

下面的代码作为REST的一部分运行。效果很好。

server.get('/test', function (req, res, next) {
    var query_string =
        "SELECT DATE(date_transacted) AS transaction_date, " +
        " MonthReports.tb AS MonthReports__tb " +
        " FROM monthly_reports MonthReports " +
        " WHERE ( date_transacted >= \'2015-01-00\' AND date_transacted <= \'2015-09-00\' ) ";

    connection.query(
        query_string
        , function (err, rows, fields) {
            if (err) throw err;
            res.send(rows);
        });
});

如果我故意关闭MySQL数据库，并进行REST调用来运行查询，我将得到错误。

无法在致命错误后进行队列查询。

此时，我打开MySQL数据库。node.js进程无法恢复，当我进行REST调用时，也会出现相同的错误。REST服务器已经死了。

如何使Node.js REST服务器代码恢复？

Answer 1

我假设您是在您的脚本中进行全局连接。

一个简单的方法是为每个请求创建一个连接：

server.get('/test', function (req, res, next) {
    var query_string =
        "SELECT DATE(date_transacted) AS transaction_date, " +
        " MonthReports.tb AS MonthReports__tb " +
        " FROM monthly_reports MonthReports " +
        " WHERE ( date_transacted >= \'2015-01-00\' AND date_transacted <= \'2015-09-00\' ) ";



    var connection = getConnection(function connected(err) {
        if (err) {
           // error connecting  to mysql! alert user
        } else {
          connection.query(
            query_string
            , function (err, rows, fields) {
              if (err) throw err;
              res.send(rows);
          });
        }
    });

});

上面的代码是psuedo代码，因为我不熟悉节点mysql库。这将允许每个请求查看mysql是否能够连接到，而牺牲了每个web请求的连接。

另一种策略可能是在发出查询时检查err，如果出现错误，请尝试重新建立全局连接。

server.get('/test', function (req, res, next) {
    var query_string =
        "SELECT DATE(date_transacted) AS transaction_date, " +
        " MonthReports.tb AS MonthReports__tb " +
        " FROM monthly_reports MonthReports " +
        " WHERE ( date_transacted >= \'2015-01-00\' AND date_transacted <= \'2015-09-00\' ) ";

    connection.query(
        query_string
        , function (err, rows, fields) {
            if (err) {
              // Try to reconnect here instead of throwing error and stopping node process, and reissue query
            }
            res.send(rows);
        });
});

Answer 2

这个网站给出了一个完整的答案。归功于这篇文章的作者，而不是我。

https://www.exratione.com/2013/01/nodejs-connections-will-end-close-and-otherwise-blow-up/

/**
 * @fileOverview A simple example module that exposes a getClient function.
 *
 * The client is replaced if it is disconnected.
 */

var mysql = require("mysql");

var client = mysql.createConnection({
  host: "127.0.0.1",
  database: "mydb",
  user: "username",
  password: "password"
});

/**
 * Setup a client to automatically replace itself if it is disconnected.
 *
 * @param {Connection} client
 *   A MySQL connection instance.
 */
function replaceClientOnDisconnect(client) {
  client.on("error", function (err) {
    if (!err.fatal) {
      return;
    }

    if (err.code !== "PROTOCOL_CONNECTION_LOST") {
      throw err;
    }

    // client.config is actually a ConnectionConfig instance, not the original
    // configuration. For most situations this is fine, but if you are doing
    // something more advanced with your connection configuration, then
    // you should check carefully as to whether this is actually going to do
    // what you think it should do.
    client = mysql.createConnection(client.config);
    replaceClientOnDisconnect(client);
    client.connect(function (error) {
      if (error) {
        // Well, we tried. The database has probably fallen over.
        // That's fairly fatal for most applications, so we might as
        // call it a day and go home.
        //
        // For a real application something more sophisticated is
        // probably required here.
        process.exit(1);
      }
    });
  });
}

// And run this on every connection as soon as it is created.
replaceClientOnDisconnect(client);

/**
 * Every operation requiring a client should call this function, and not
 * hold on to the resulting client reference.
 *
 * @return {Connection}
 */
exports.getClient = function () {
  return client;
};