Node.js MySQL需要持久连接

I need a persistent MySQL connection for my Node web app. The problem is that this happens about a few times a day:

我的Node Web应用程序需要持久的MySQL连接。问题是这种情况每天发生几次：

Error: Connection lost: The server closed the connection.
at Protocol.end (/var/www/n/node_modules/mysql/lib/protocol/Protocol.js:73:13)
at Socket.onend (stream.js:79:10)
at Socket.EventEmitter.emit (events.js:117:20)
at _stream_readable.js:895:16
at process._tickCallback (node.js:415:13)
error: Forever detected script exited with code: 8
error: Forever restarting script for 2 time
info: socket.io started

Here is my connection code:

这是我的连接代码：

// Yes I know multipleStatements can be dangerous in the wrong hands.
var sql = mysql.createConnection({
    host: 'localhost',
    user: 'my_username',
    password: 'my_password',
    database: 'my_database',
    multipleStatements: true
});

sql.connect();

function handleDisconnect(connection) {
    connection.on('error', function(err) {
        if (!err.fatal) {
            return;
        }
        if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
            throw err;
        }
        console.log('Re-connecting lost connection: ' + err.stack);
        sql = mysql.createConnection(connection.config);
        handleDisconnect(sql);
        sql.connect();
    });
}

handleDisconnect(sql);

As you can see, the handleDisconnect code does not work..

如您所见，handleDisconnect代码不起作用..

3 个解决方案

#1

Use the mysql connection pool. It will reconnect when a connection dies and you get the added benefit of being able to make multiple sql queries at the same time. If you don't use the database pool, your app will block database requests while waiting for currently running database requests to finish.

使用mysql连接池。它会在连接中断时重新连接，并且您可以获得能够同时进行多个SQL查询的额外好处。如果不使用数据库池，则应用程序将在等待当前正在运行的数据库请求完成时阻止数据库请求。

I usually define a database module where I keep my queries separate from my routes. It looks something like this...

我通常定义一个数据库模块，我将查询与我的路由分开。它看起来像这样......

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

exports.getUsers = function(callback) {
  pool.getConnection(function(err, connection) {
    if(err) { 
      console.log(err); 
      callback(true); 
      return; 
    }
    var sql = "SELECT id,name FROM users";
    connection.query(sql, [], function(err, results) {
      connection.release(); // always put connection back in pool after last query
      if(err) { 
        console.log(err); 
        callback(true); 
        return; 
      }
      callback(false, results);
    });
  });
});

#2

I know this is super delayed, but I've written a solution to this that I think might be a bit more generic and usable. I had written an app entirely dependent on connection.query() and switching to a pool broke those calls.

我知道这是超级延迟，但我已经写了一个解决方案，我认为可能会更通用和可用。我写了一个完全依赖于connection.query（）的应用程序，并且切换到池打破了这些调用。

Here's my solution:

这是我的解决方案：

var mysql = require('mysql');

var pool = mysql.createPool({
    host     : 'localhost',
    user     : 'user',
    password : 'secret',
    database : 'test',
    port     : 3306
});

module.exports = {
    query: function(){
        var sql_args = [];
        var args = [];
        for(var i=0; i<arguments.length; i++){
            args.push(arguments[i]);
        }
        var callback = args[args.length-1]; //last arg is callback
        pool.getConnection(function(err, connection) {
        if(err) {
                console.log(err);
                return callback(err);
            }
            if(args.length > 2){
                sql_args = args[1];
            }
        connection.query(args[0], sql_args, function(err, results) {
          connection.release(); // always put connection back in pool after last query
          if(err){
                    console.log(err);
                    return callback(err);
                }
          callback(null, results);
        });
      });
    }
};

This instantiates the pool once, then exports a method named query. Now, when connection.query() is called anywhere, it calls this method, which first grabs a connection from the pool, then passes the arguments to the connection. It has the added effect of grabbing the callback first, so it can callback any errors in grabbing a connection from the pool.

这会将池实例化一次，然后导出名为query的方法。现在，当在任何地方调用connection.query（）时，它会调用此方法，该方法首先从池中获取连接，然后将参数传递给连接。它具有首先获取回调的附加效果，因此它可以在从池中获取连接时回调任何错误。

To use this, simply require it as module in place of mysql. Example:

要使用它，只需将其作为模块代替mysql。例：

var connection = require('../middleware/db');

function get_active_sessions(){
  connection.query('Select * from `sessions` where `Active`=1 and Expires>?;', [~~(new Date()/1000)], function(err, results){
    if(err){
      console.log(err);
    }
    else{
      console.log(results);
    }
  });
}

This looks just like the normal query, but actually opens a pool and grabs a connection from the pool in the background.

这看起来就像普通查询一样，但实际上会打开一个池并在后台从池中获取连接。

#3

In response to @gladsocc question:

回应@gladsocc问题：

Is there a way to use pools without refactoring everything? I have dozens of SQL queries in the app.

有没有办法使用池而不重构一切？我在应用程序中有几十个SQL查询。

This is what I ended up building. It's a wrapper for the query function. It will grab the connection, do the query, then release the connection.

这就是我最终建立的。它是查询功能的包装器。它将获取连接，执行查询，然后释放连接。

var pool = mysql.createPool(config.db);

exports.connection = {
    query: function () {
        var queryArgs = Array.prototype.slice.call(arguments),
            events = [],
            eventNameIndex = {};

        pool.getConnection(function (err, conn) {
            if (err) {
                if (eventNameIndex.error) {
                    eventNameIndex.error();
                }
            }
            if (conn) { 
                var q = conn.query.apply(conn, queryArgs);
                q.on('end', function () {
                    conn.release();
                });

                events.forEach(function (args) {
                    q.on.apply(q, args);
                });
            }
        });

        return {
            on: function (eventName, callback) {
                events.push(Array.prototype.slice.call(arguments));
                eventNameIndex[eventName] = callback;
                return this;
            }
        };
    }
};

And I use it like I would normally.

而且我像平常一样使用它。

db.connection.query("SELECT * FROM `table` WHERE `id` = ? ", row_id)
          .on('result', function (row) {
            setData(row);
          })
          .on('error', function (err) {
            callback({error: true, err: err});
          });