MySQL 已超过“max_user_connections”资源
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39150633/
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
has exceeded the 'max_user_connections' resource
提问by Peter Boomsma
I have a MySQL, Express, Angular, NodeJS application and sometimes when I log in I get the following error in my node console:
我有一个 MySQL、Express、Angular、NodeJS 应用程序，有时当我登录时，我的节点控制台中会出现以下错误：
TypeError: Cannot read property 'query' of undefined
类型错误：无法读取未定义的属性“查询”
The error occurs in my passport.local.jsfile, this is the line:
错误发生在我的passport.local.js文件中，这是一行：
connection.query('SELECT * FROM users WHERE username LIKE ?', [username], function (err, user) {
This is the passport function
这是护照功能
passport.use(new LocalStrategy(
function(username, password, done) {
console.log('app.js');
pool.getConnection(function(err, connection) {
console.log('err: ' + err);
console.log(connection);
connection.query('SELECT * FROM users WHERE username LIKE ?', [username], function (err, user) {
if (err) throw err;
for (var i = user.length - 1; i >= 0; i--) {
var current = user[i];
}
if(current){
if(bcrypt.compareSync(password, current.password)){
return done(null, user);
} else {
return done(null, false);
}
} else {
console.log('no user');
return done(null, false);
}
});
connection.release();
});
}
));
I require my poolin the top of my file
我需要pool在我的文件顶部
var pool = require('../../config/connection');
When the error occurs the:
当错误发生时：
console.log(connection);
Gets:
获取：
undefined
不明确的
I also log the error:
我还记录了错误：
console.log('err: ' + err);
Shows:
节目：
err: Error: ER_USER_LIMIT_REACHED: User 'bfe4a8980ede74' has exceeded the 'max_user_connections' resource (current value: 10)
回答by robertklep
The error you're getting is stating the issue: your MySQL server is only allowing 10 connection per user, and that limit has been reached.
您收到的错误说明了问题：您的 MySQL 服务器只允许每个用户有 10 个连接，并且已达到该限制。
The default for the mysqlconnection poolalso happens to be 10, which is cutting it really close. If any other MySQL client besides your Express app is connected to the database with the same user credentials, you may run into that particular error. I would suggest increasing max_user_connectionsin the MySQL configuration.
连接池的默认值mysql也恰好是 10，这与它非常接近。如果除您的 Express 应用程序之外的任何其他 MySQL 客户端使用相同的用户凭据连接到数据库，您可能会遇到该特定错误。我建议增加max_user_connectionsMySQL 配置。
Aside from that, there's another issue with your code: it's releasing the connection before the query has finished, which may lead to unexpected behaviour. Move the call to connection.release()to inside the callback:
除此之外，您的代码还有另一个问题：它在查询完成之前释放连接，这可能会导致意外行为。将调用移动到connection.release()回调内部：
pool.getConnection(function(err, connection) {
...
connection.query('SELECT * FROM users WHERE username LIKE ?', [username], function (err, user) {
connection.release();
...
});
});
If this is a common way you're using MySQL (get a connection, perform a query, release the connection), you can make life a bit easier by using pool.query()instead. See this example.
如果这是您使用 MySQL 的一种常见方式（获取连接、执行查询、释放连接），那么您可以通过使用pool.query()来让生活更轻松一些。请参阅此示例。
And finally, if you're working with async code, don't throw errors but pass them to the callback (and make sure that you actually handle them, because you're not handling any errors from pool.getConnectionnow, besides logging them):
最后，如果您正在使用异步代码，请不要抛出错误，而是将它们传递给回调（并确保您确实处理了它们，因为pool.getConnection除了记录它们之外，从现在开始您不会处理任何错误）：
pool.getConnection(function(err, connection) {
if (err) return done(err);
...
});
回答by JumpMan
I am assuming that your max_user_connectionsis set to 10. Please increase the max_user_connectionvalue.
我假设您max_user_connections的设置为 10。请增加该max_user_connection值。
show global variables like '%connections%';
Will help you in giving the no of connections you have set. Increase the number of connections If its less than 25 or 50. Max connections can be more than 16000 I guess, it all depends on your cpu, no of threads It can handle etc.
将帮助您提供已设置的连接数。增加连接数如果它小于 25 或 50。最大连接数可以超过 16000 我猜，这完全取决于您的 CPU，线程数它可以处理等。
SET GLOBAL max_user_connections=100;
max_user_connectionsis a dynamic variable, which means you can directly run this query. You donot have to shutdown mysql.
max_user_connections是一个动态变量，这意味着您可以直接运行此查询。您不必关闭 mysql。
