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

5 回答

• 11

  查看node-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();
  });
  

  回复于 2024-04-28T03:39:54+08:00
• 8

  我遇到了类似的问题，并创建了一个getConnection（）包装函数，该函数在将mysql连接返回给调用者之前检查mysql连接的运行状况，并根据需要重新 Build 连接 . 在我的测试中，它已经为应用程序透明地处理了致命和非致命的连接问题 . 如果连接只是超时，则应用程序将恢复，而不会出现任何错误 . 如果存在暂时但致命的数据库连接问题，则只要数据库连接可用，应用程序就会自动恢复运行 .

  至于重现测试问题，请将以下两行添加到 [mysqld] 块下的my.ini或my.cnf文件中：

  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) { ....
  

  回复于 2024-04-28T03:39:54+08:00
• -1

  这是我最终使用的，它运作得很好 . 偶尔连接丢失/重启它恢复得很好 . 我有一个database.js文件，它 Build 连接并定期检查它们 .

  提出要求：

  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;
  

  回复于 2024-04-28T03:39:54+08:00
• 2

  使用generic-pool，我写了一些在本地工作的东西 . 我想我在服务器端以奇怪的方式崩溃了 .

  // Test node connection pool stuff
  
  // Create a MySQL connection pool with
  // a max of 10 connections, a min of 2, and a 30 second max idle time
  var poolModule = require('generic-pool');
  var pool = poolModule.Pool({
      name     : 'mysql',
      create   : function(callback) {
          var Client = require('mysql').Client; // use node-mysql library in all it's dubious glory
          var c = new Client();
          c.user     = 'root';
          c.password = 'xxx';
          c.database = 'test';
          c.on('close', function (err) {
            console.log('mysqldb conn close');
          });
          c.on('error', function (err) {
            console.log('mysqldb error: ' + err);
          });
  
          // parameter order: err, resource
          // new in 1.0.6
          callback(null, c);
      },
      destroy  : function(client) { client.end(); },
      max      : 10,
      // optional. if you set this, make sure to drain() (see step 3)
      min      : 2, 
      // specifies how long a resource can stay idle in pool before being removed
      idleTimeoutMillis : 30000,
       // if true, logs via console.log - can also be a function
      log : true 
  });
  
  var http = require('http');
  http.createServer(function (req, res) {
  
    // Get db conn
    pool.acquire(function(err, client) {
        if (err) {
            // handle error - this is generally the err from your
            // factory.create function 
            console.log('pool.acquire err: ' + err);
            res.writeHead(500, {'Content-Type': 'application/json'});
            out = {
              err: err
            }
            res.end(JSON.stringify(out));
        }
        else {
            client.query("select * from foo", [], function(err, results) {
  
                if(err){
                  res.writeHead(500, {'Content-Type': 'application/json'});
                  out = {
                    err: err
                  }
                  res.end(JSON.stringify(out));
                } else {
                  res.writeHead(500, {'Content-Type': 'application/json'});
                  out = {
                    results: results
                  }
                  res.end(JSON.stringify(out));
                }
  
  
                // return object back to pool
                pool.release(client);
            });
        }
    });
  }).listen(9615);
  

  相当请不要在凌晨4点死亡，没有明显的理由！

  回复于 2024-04-28T03:39:54+08:00
• 0

  解决方案是使用池连接！

  您可以编写代码来手动处理连接，它可以工作 . 但是池是为此设计的，使用池连接解决了连接丢弃错误 .

  var mysql = require('mysql');
  var pool  = mysql.createPool({
      connectionLimit : 10,
       host            : 'example.org',
      user            : 'bob',
       password        : 'secret',
        database        : 'my_db'
      });
  
     pool.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
              if (error) throw error;
              console.log('The solution is: ', results[0].solution);
           });
  

  pooling mysql connection

  回复于 2024-04-28T03:39:54+08:00