node-mysql pool active connections not getting released

[iamtennislover]

Hi felixge,

Can you please take a look at this?
http://stackoverflow.com/questions/28575164/node-mysql-pool-active-connections-not-getting-released

Thanks for all your previous helps.

[dougwilson]

@abarik1981 there is something missing from the code you posted, because I cannot run it since there is an error when it tries to call mydbc.connect since no connect method is defined in your code posted. Otherwise, nothing pops up as a big issue, which is why I tried to run it (but it's not complete enough for me to run).

[iamtennislover]

I have updated at stackoverflow... anyways, i have posted the whole code here...

/**
 * Test mysql_client (./test.js)
 */
'use strict';
var async = require('async');
var mysqlc = require('../mysql_client');
var connProp = {
        connectionLimit : 10,
        host            : 'xxx',
        user            : 'xxx',
        password        : 'xxx',
        database        : 'xxx'
};
var mydbc = new mysqlc.MySqlClient(connProp);

async.series([
    function(callback) {
        mydbc.connect(callback);
    },
    function(callback) {
        var intervalObject;
        intervalObject = setInterval(function() {
            mydbc.runQry('SELECT 1', function(err, results) {
                if (err) {
                    console.error('foo-err:', intervalObject, err);
                    callback(err);
                } else {
                    console.log('resultsxxxx', results)
                }
            })
        }, 1000*0.5)
    },
    function(callback) {
        mydbc.disconnect(callback);
    }
    ],
    function(err, results) {
        console.log('results', results[1]);
});

// This results in the following output, finally hitting enqueue event:
connecting mysql
running mysql qry: SELECT 1
resultsxxxx [ { '1': 1 } ]
running mysql qry: SELECT 1
resultsxxxx [ { '1': 1 } ]
running mysql qry: SELECT 1
resultsxxxx [ { '1': 1 } ]
running mysql qry: SELECT 1
resultsxxxx [ { '1': 1 } ]
running mysql qry: SELECT 1
resultsxxxx [ { '1': 1 } ]
running mysql qry: SELECT 1
resultsxxxx [ { '1': 1 } ]
running mysql qry: SELECT 1
resultsxxxx [ { '1': 1 } ]
running mysql qry: SELECT 1
resultsxxxx [ { '1': 1 } ]
running mysql qry: SELECT 1
resultsxxxx [ { '1': 1 } ]
running mysql qry: SELECT 1
resultsxxxx [ { '1': 1 } ]
running mysql qry: SELECT 1
results undefined
runQry-Waiting for available connection slot-ERROR
running mysql qry: SELECT 1
runQry-Waiting for available connection slot-ERROR
running mysql qry: SELECT 1
runQry-Waiting for available connection slot-ERROR



// This is the wrapper for node-mysql (pretty straightforward)
/**
 * mysql_client (./mysql_client.js)
 */

// Import modules
var mysql = require('mysql');
var path = require('path');
var fs = require('fs');
var async = require('async');

// Export module with Namespace
var mysqlc = exports;

// Constants
var DEFAULT_MYSQL_PORT = 3306;
var DEFAULT_AUTOCOMMIT = false;
var DEFAULT_CONNLIMIT = 10;

/**
 * MySQL Class Wrapper
 * @param {string} user: user of database account
 * @param {string} password: password for database account
 * @param {string} database: database name
 * @param {string} host: hostname of server
 * @param {string} port: port of server
 * @param {boolean} autocommit: autocommit
 * @return {object} object
 */
mysqlc.MySqlClient = function MySqlClient(connProp) {
    "use strict";
    this.user = connProp.user;
    this.password = connProp.password;
    this.database = connProp.database;
    this.host = connProp.host;
    this.port = connProp.port || DEFAULT_MYSQL_PORT;
    this.autocommit = connProp.autocommit || DEFAULT_AUTOCOMMIT;
    this.connectionLimit = connProp.connectionLimit || DEFAULT_CONNLIMIT
    this.pool = null;

    this.connProp = {
            connectionLimit : this.connectionLimit,
            host            : this.host,
            port            : this.port,
            user            : this.user,
            password        : this.password,
            database        : this.database,
            multipleStatements: true
    };
}


mysqlc.MySqlClient.prototype.runQry = function runQry(qry, args, callback) {
    console.log('running mysql qry:', qry);
    this.pool.getConnection(function(err, connection) {
        if (err) {
            console.error('runQry-cannot getConnection ERROR:', err);
            return callback(err);
        }
        connection.query(qry, args, function(err, rows) {
            if (err) {
                console.error('runQry-cannot run qry ERROR:', err);
                return callback(err);
            }
            connection.release();
            return callback(null, rows);
        });
    });
};

mysqlc.MySqlClient.prototype.connect = function connect(callback) {
    var pool = mysql.createPool(this.connProp);
    this.pool = pool;

    pool.on('enqueue', function () {
        console.error('runQry-Waiting for available connection slot-ERROR');
        return callback('pool-not-empty');
    });

    // verify connection
    pool.getConnection(function(err, connection) {
        console.log('connecting mysql');
        if (err) {
            console.error('ERROR connecting to mysql server with connProp:', this.connProp);
            return callback(err);
        }
        connection.release();
        return callback(null);
    });
};

mysqlc.MySqlClient.prototype.disconnect = function disconnect(callback) {
    var pool = this.pool;
    pool.getConnection(function(err, connection) {
        console.log('disconnecting mysql');
        if (err) {
            console.error('ERROR disconnecting to mysql server with connProp:', this.connProp);
            return callback(err);
        }
        connection.release();
        pool.end();
        return callback(null);
    });
}

[dougwilson]

Does it have any issues if you change the interval from 1000*0.5 to something like 5000?

[iamtennislover]

Hmm.. The interval doesn't help, as the connections are still present for a long time. Its actually not anything to do with the interval, as this is only to simulate what happens when I use node-mysql with express-js.

When I use it with express-js, I am never calling pool.end() throughout the period the server is up (many days)... Now, what I have noticed is, after couple of hours during the non-peak hours when there are no requests to the server, the mysql connections are released and watch -n 2 "netstat -an | grep 127.0.0.1.*3306.*ESTABLISHED | wc -l" counter drops (but after couple hrs).

But, during peak hours, when many users access the same page (which makes multiple calls to mysql.pool.getConnection), I see that enqueue event is hit as too many connections have been opened (e.i around 30) and node-mysql hasn't closed them yet (even though connection.release has been called in all cases)

[dougwilson]

I think you are confused about what the enqueue event means. Hitting that event is in no way an error. The point of it is just to let you know what's happening in the pool. The only time it's an error is if you call getConnection, and the enqueue event is hit and then your getConnection never, ever returns. The purpose is so that this module will never open more than connectionLimit to your server.

If at peak times 10 is not enough for you, then you need to increase that limit.

[dougwilson]

This module will hold on to those 10 connections for as long as possible, so that when a new request comes in, it doesn't have high latency waiting for a new connection to MySQL to be established. We don't close the connections at all, in fact--it's up to the MySQL server to close them, which happens after a few hours of inactivity, according to your MySQL server's configuration.

[dougwilson]

TL;DR connection.release() does not close connections, since that would be a determent to your web server, and against the whole reason to even use a pool: so that when a request comes in, a connection may be idling there, waiting to suddenly spring into action to serve that request.

[iamtennislover]

Yes, thanks, I understand it better. I knew that enqueue was never an error; but I was just wondering, if there is a setting to reduce that inactivity period of a connection from few hours to a setting? I am not sure of the implications of having many idle connections on the server, thats why I kept the limit low to 10

Yes, I will eventually increase the limit.

[dougwilson]

There isn't an idle setting yet, but there is an open ticket: #962 Feel free to submit a PR if you need it quick :) Otherwise it will eventually come some day. Another option is you can ditch this module's pool implementation and use generic-pool if you really need it now and don't have the time to contribute a PR :)

[iamtennislover]

thank you :)