Pool Connections not getting released

[SS K]

I use connection pool with the connectionLimit set to 10. Once the connections reach 10, the next query doesn't work, unless I kill some of the connections as shown in the table below. 

All connections are in SLEEP mode.Should't these connections be reused? I do release the connections. I have noticed some connections in SLEEP mode for more than 5 hours.  Is this a known issue or am I doing something wrong?

I have the code snippet below the table

mysql> select * from information_schema.processlist order by TIME;

+--------+--------+------------------------+--------+----------------+------+-------+------+

| ID     | USER   | HOST                   | DB     | COMMAND        | TIME |STATE  | INFO |

+--------+--------+------------------------+--------+----------------+------+-------+------+

| 795202 | testDB | example.test.com:47132 | testDB | Sleep          |    2 |       | NULL |

| 795206 | testDB | example.test.com:47138 | testDB | Sleep          |    3 |       | NULL |

| 795166 | testDB | example.test.com:47090 | testDB | Sleep          |    6 |       | NULL |

| 795158 | testDB | example.test.com:47088 | testDB | Sleep          |   10 |       | NULL |

| 795140 | testDB | example.test.com:47074 | testDB | Sleep          |   22 |       | NULL |

| 795111 | testDB | example.test.com:47052 | testDB | Sleep          |   27 |       | NULL |

| 795083 | testDB | example.test.com:47030 | testDB | Sleep          |   36 |       | NULL |

| 795078 | testDB | example.test.com:46994 | testDB | Sleep          |   42 |       | NULL |

| 794723 | testDB | example.test.com:46782 | testDB | Sleep          |   59 |       | NULL |

| 794718 | testDB | example.test.com:46776 | testDB | Sleep          |  139 |       | NULL |

+--------+--------+------------------------+--------+----------------+------+-------+------+

10 rows in set (0.00 sec)

const CONFIG = {

        connectionLimit: 10,
        host: DB_HOST,
        user: DB_USER,
        password: DB_PASS,
        database: DB_NAME,
        waitForConnections: true,
        wait_timeout: 600,
        connect_timeout: 10,

        queueLimit: 0

}

pool = mysql.createPool(CONFIG);

function _query(query, params, callback) {

        pool.getConnection(function (err, connection) {

            if (err) {

                connection.release();

                callback(err, null);

                throw err;

            }

            

            connection.query(query, params, function (err, rows) {

                connection.release();

                if (!err) {

                    callback(null, rows);

                }

                else {

                    callback(err);

                }

            });

            

            connection.on('error', function (err) {

                connection.release();

                callback(err, null);

                throw err;

            });

        });

    };

    return {

        query: _query

    };

})

[Ryan Lee]

You're code looks fine. Not sure why queries are hanging. Maybe it's something to with your queries and not your connections?

The pool has a query option that takes out all the allocation headache. Call it using `pool.query()`. It's in the documentation. I have not had any problems using this shorthand.

Here is your code rewritten:

//query.js
const CONFIG = { ... };
const pool = mysql.createPool(CONFIG);
module.exports = {
  query: pool.query
};

Use it like this:

//app.js
const query = require('./query.js');
query('SELECT * FROM users where 1', (err, vals) => {
  console.log(vals);
});

Note, you have a `return` statement and no `const` in front of pool. How are you exporting this module? You might be leaking memory and redeclaring pool instead of reusing it. That would cause your hanging issue.

--
You received this message because you are subscribed to the Google Groups "node-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-mysql+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/node-mysql/491017db-a449-4395-92e9-13109bf63545%40googlegroups.com.