Karthik Karthik - 14 days ago 5
MySQL Question

Pooling keeps incrementing connections and ends up in ER_CON_COUNT_ERROR

I have a class called Connection like one below. This only executes select statements. I have non pooling connection for insert or update.

var _mysql = require('mysql');

function Connection()
{
//private variables and dependencies includes

//create mysql pool connection requires nodejs mysql this connection is used only for selects.
var _connectionSelect = _mysql.createPool({
host : _config.mySQLDB.select.dbHost,
user : _config.mySQLDB.select.dbUser,
password : _config.mySQLDB.select.dbPass,
database : _config.mySQLDB.select.dbName,
supportBigNumbers : true,
connectTimeout : 7000,
connectionLimit : 5,
queueLimit : 5
});
this.executeSelect = function(sql, callback, Message)
{
//connects to mysql.
_connectionSelect.getConnection(function(connectionError, Connection){
if(connectionError)
{
console.log(connectionError);
//throws error if connection or sql gone wrong
Message.add("error", 'serviceDown');
Message.add("devError", 'unknownError');
callback(false);
}
else
{
//executes the query passed
Connection.query(sql, function(error, rows) {
Message.incerementQuery();
if(error)
{
Connection.release();
console.log(error+sql);
//throws error if connection or sql gone wrong
Message.add("error", 'unknownError');
Message.add("devError", "seriousError", "Database errors at resource server side");
callback(false);
}
else
{
Connection.release();
//executes the callback function
callback(rows);
}
});
}
});
};
}

exports.Connection = Connection;


I created an instance of this class whenever I want to execute a query.

I am aware that the default concurrent connections in MySQL is 100 and I wanted to keep that number.

Whenever I try running my application, this connection pooling is incrementing every select and reaches 100 connections pretty soon.

As you can see I am releasing the connection on success or error states. I am pretty sure that I must be doing something wrong, but difficult to figure out.

Is it because how I create instances of this class? I was hoping that if I supply

connectionLimit : 5


even if I create many instances of this class it should only utilise 5 connection?

Note: I have only one instance of this app in my local machine.

Sorry to be so amateur, I am new to this streaming I/O business. I love the idea of pooling but if I cant sort this out, I may need to use traditional open and close connection for every query . Any help would be much appreciated.

Many thanks,

Karthik

Answer

Got the answer from Doug Wilson from git hub https://github.com/dougwilson.

I should have instantiated createPool outside of the function. Works like a charm.

The code goes like

    var _mysql = require('mysql');
//create mysql pool connection requires nodejs mysql this connection is used only for selects.
        var _connectionSelect = _mysql.createPool({
            host                : _config.mySQLDB.select.dbHost,
            user                : _config.mySQLDB.select.dbUser,
            password            : _config.mySQLDB.select.dbPass,
            database            : _config.mySQLDB.select.dbName,
            supportBigNumbers   : true,
            connectTimeout      : 7000,
            connectionLimit     : 5,
            queueLimit          : 5
        }


    function Connection()
    {
        //private variables and dependencies includes

       );
    this.executeSelect = function(sql, callback, Message)
        {
            //connects to mysql.
            _connectionSelect.getConnection(function(connectionError, Connection){
                if(connectionError)
                {
                    console.log(connectionError);
                    //throws error if connection or sql gone wrong
                    Message.add("error", 'serviceDown');
                    Message.add("devError", 'unknownError');
                    callback(false);
                }
                else
                {
                    //executes the query passed
                    Connection.query(sql, function(error, rows) {
                        Message.incerementQuery();
                        if(error)
                        {
                            Connection.release();
                            console.log(error+sql);
                            //throws error if connection or sql gone wrong
                            Message.add("error", 'unknownError');
                            Message.add("devError", "seriousError", "Database errors at resource server side");
                            callback(false);
                        }
                        else
                        {
                            Connection.release();
                            //executes the callback function
                            callback(rows);
                        }
                    });
                }
            });
        };
    }

    exports.Connection = Connection;

Thanks a lot. Sorry to be so stupid.

Karthik