Node.js MySQL 需要持久连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17015590/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Node.js MySQL Needing Persistent Connection
提问by apscience
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 代码不起作用。
回答by Daniel
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);
});
});
});
回答by Adam Yost
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.
这看起来就像普通的查询，但实际上打开一个池并在后台从池中获取连接。
回答by oportocala
In response to @gladsoccquestion:
回应@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});
});
