Mysql: too many connections - not pooling?

[gotmikhail]

I've read through multiple issues / threads, but can't seem to find a solution.

I regularly get a the error, ER_CON_COUNT_ERROR: Too many connections, but from my understanding pooling should take care of that by queuing, correct?

The max connection on the server is low, only 32, but again, queuing ... ?

Note that I'm relying on default values. (10 connections, unlimited queue, etc)

Here's my code:

var dbPool = mysql.createPool({
    "host": appConfig.db.ip,
    "user": appConfig.db.user,
    "password": appConfig.db.pw,
    "database": appConfig.db.database,
    "dateStrings": true,
    "multipleStatements": true
});

exports.dbQuery = function (queryString, callback, nested, preserveObject) {
    if (nested !== false) {
        nested = true;
    }
    dbPool.getConnection(function (err, connection) {
        var queryOptions = {
            "sql": queryString,
            "nestTables": nested
        };
        connection.query(queryOptions, function (err, results) {
            // Recycle db connection
            connection.release();
            if (err) {
                console.log(err);
                // Report error to callback()
                if (typeof(callback) === 'function') {
                    callback(true);
                }
            }
            if (typeof(callback) === 'function') {
                callback(false, camelize(snakeize(results)), preserveObject);
            }
        });
    });
};

[dougwilson]

You may need to adjust the connectionLimit option to createPool. By default the pool will create up to 10 concurrent connections per every time you call mysql.createPool.

[gotmikhail]

I was assuming the default value of 10. But, if there aren't 10 available, it shouldn't error out, should it?

In my code, the pool is created in the very beginning and used later on through the dbQuery() call.

I can definitely "fiddle" with the numbers, but I was thinking I wouldn't need to if it behaved the way it was intended. If I'm making the wrong conclusions, just let me know.

On the good note, this has been a great module, used across multiple projects!

[dougwilson]

I can definitely "fiddle" with the numbers, but I was thinking I wouldn't need to if it behaved the way it was intended. If I'm making the wrong conclusions, just let me know.

Gotcha. Yes, it is supposed to error out if it cannot reach it's max. BUT perhaps that should be configurable in a way to say "hey, allow a max of 10, but only error out if I can't at least get 5" or something. I'd have to think on it. But the current behavior is how connection pooling works in other languages' implementations.

[RameshRM]

@dougwilson I am experiencing too many connections with default of 10 in a connection pool, but my load is very small, my code looks like this

pool.query(query, options, callback)

As you can see , I don't manage connections letting the pool take care of acquiring & releasing. Trying to figure out how to solve this, let me know if any pointers.

[germansokolov13]

I also use just pool.query and I get this error over time after several dozens requests to server even with the connectionLimit set to 1

[germansokolov13]

My bad. It seems my pool itself is initialized many times. Chances are @RameshRM could experience the same thing. I was using express-di and was not aware that its factories would execute on each request.

[Rootmars]

If you're using express, then don't be stupid like me by creating a connection pool in app.use:

app.use(function(req, res, next){
    res.locals.connectionPool = mysql.createPool({
        connectionLimit : 10,
        host : 'localhost',
        user : 'user',
        password : 'password',
        database : 'database'
    });
    next();
});

This creates a new connection pool every time the app is loaded. Instead, create the connection pool outside of app.use:

var connectionPool = mysql.createPool({
    connectionLimit : 10,
    host : 'localhost',
    user : 'user',
    password : 'password',
    database : 'database'
});

app.use(function(req, res, next){
    res.locals.connectionPool = connectionPool
    next();
});

[dougwilson]

Instead of

app.use(function(req, res, next){
    res.locals.connectionPool = connectionPool
    next();
});

Just use app.locals (http://expressjs.com/en/4x/api.html#app.locals):

app.locals.connectionPool = mysql.createPool({
    connectionLimit : 10,
    host : 'localhost',
    user : 'user',
    password : 'password',
    database : 'database'
});

[Rootmars]

Still new at using nodejs. Thanks for the advice!

[alur222]

I have to share the same experience when initializing the pool.
I had the same "Too many connections" error because I initialized the pool on every route. (SMH).

Cheer to everyone who also solved it by via app.locals ! :clinking_glasses:

[kai-koch]

Problem seems to be resolved with using app.locals.
Re-open, if needed.