Getting mysql:"Too many connections" despite using createPool in NodeJs

Question

I am using Observables to display real-time data in Angular2+ application and the back-end is written in NodeJs that uses MySQL database. Since I am using Observables, I need tens of millions of connections of MySQL to keep the real-time work going.

But it is not possible to acquire so many connections. So I used pooling where a connection is created from the pool of connections. However, I fail to implement it. I still get the error:

Error: ER_CON_COUNT_ERROR: Too many connections"

How can I close my connections so that the connections are not outnumbered?

Front end code:

angular.component.ts

Observable.interval(10000).subscribe(x => {

  this.viewData(Val);

  // more functions

  console.log(" Observable")
});

NodeJS code:

dashboard.service.js

function viewData(data) {

    var sqlQuery = `
    select * from TRANSACTION_PAYLOAD where INTERFACE_NAME = 'Highmark' AND (STATUS ='SUCCESS_RESPONSE')`

    var deferred = Q.defer();
    console.log("INSIDE NODE JS SERVICE");

    var host = config.host;
    var user = config.user;
    var password = config.password;
    var database = config.database;

    var con = mysql.createPool({
        host: host,
        user: user,
        password: password,
        database: database
    });

    con.getConnection(function (err) {
        console.log("Inside .getConnection ")

        if (err) deferred.reject(err.name + ': ' + err.message);

        con.query(sqlQuery,
            function (err, result, fields) {
                if (err) deferred.reject(err.name + ': ' + err.message);


                console.log(result);
                deferred.resolve(result);
            });
    });
    return deferred.promise; 
    con.close();
}

You seems to be creating a new pool every time you perform `viewData`. The idea is to use a single connection pool for all `viewData` and other db operations. — William Chong, Dec 20 '18 at 06:37
The permitted number of connections is controlled by the max_connections system variable. The default value is 151 to improve performance when MySQL is used with the Apache Web server. To support more connections, set max_connections to a larger value. mysqld actually permits max_connections + 1 client connections. The extra connection is reserved for use by accounts that have the SUPER privilege. [https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html] — deepchudasama, Dec 20 '18 at 06:39
@ William ...can you please suggest how to use a single connection pool for all the viewData ? — Techdive, Dec 20 '18 at 06:41

score 1 · Answer 1 · answered Dec 20 '18 at 06:44

1

you are creating mysql pool for every request. You supposed to have single connection pool.

connection pool manages connections made to mysql server automatically.

you need to take out connection pool initialization out of your function.

take the connection pull out

const mysqlConOptions = {
    host: host,
    user: user,
    password: password,
    database: database
};
var conPool = mysql.createPool(mysqlConOptions);

inside your function,

conn = await conPool.getConnection();

answered Dec 20 '18 at 06:44

Derviş Kayımbaşıoğlu

28,492
4
50
72

and in conPool ...basically in pooling , even if we don't end the connection . i.e. conPool.close() doesn't execute ...still it will work ? i.e. it won't give mysql - Too many connections error ? – Techdive Dec 20 '18 at 07:17

Getting mysql:"Too many connections" despite using createPool in NodeJs

1 Answers1