需要持久连接的Node.js MySQL [英] Node.js MySQL Needing Persistent Connection

问题描述

我的Node Web应用程序需要一个持久的MySQL连接.问题是每天大约发生几次这种情况:

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

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

这是我的连接代码:

// 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);

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

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

推荐答案

使用mysql连接池.当连接断开时，它将重新连接，您将获得额外的好处，即能够同时进行多个sql查询.如果您不使用数据库池，则您的应用将在等待当前运行的数据库请求完成时阻止数据库请求.

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.

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

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);
    });
  });
});

这篇关于需要持久连接的Node.js MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！