Peter Boomsma Peter Boomsma - 3 months ago 25
MySQL Question

has exceeded the 'max_user_connections' resource

I have a MySQL, Express, Angular, NodeJS application and sometimes when I log in I get the following error in my node console:


TypeError: Cannot read property 'query' of undefined


The error occurs in my
passport.local.js
file, this is the line:

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
pool
in the top of my file

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)

Answer

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.

The default for the mysql connection pool also 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_connections in the MySQL configuration.

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:

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.

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.getConnection now, besides logging them):

pool.getConnection(function(err, connection) {
  if (err) return done(err);
  ...
});