node-mysql doesn't reuse connections

Question

I'm using the connection pool in this way:

var pool = mysql.createPool({
  host: config.db.host,
  port: config.db.port,
  user: config.db.user,
  password: config.db.password,
  database: config.db.database,
  connectionLimit: config.db.connectionLimit
});

exports.api_point = function(req, res) {

  pool.getConnection(function(err, connection) {
    if (err) {
      console.error('error acquiring connection', err);

      var result = {};
      result.error = err.code;

      res.json(result);
      return;
    } else {
      var query = connection.query('SELECT * FROM tableName', function(err, rows) {
        connection.release();

        var result = {};

        if (err) {
          console.error('error executing query: ' + err.stack);

          result.error = err.message;
        } else {
          result.data = rows;
        }

        res.json(result);
        return;
      });
    }
  });
};

Problem:

Yet, after all the connections (defined by connectionLimit, e.g. 20) are created, the next call to pool.getConnection() hangs until Express returns: "GET /api_point - - ms - -" (after ~10s).

I've tried replacing connection.release(); with connection.destroy(); with no effect. How can I solve this?

node-mysql version:

"mysql": "~2.5.1",

Btw. I track connections being created with:

var connCount = 0;
pool.on('connection', function(connection) {
  console.log('connCount:', ++connCount);
});

connection.release() should be all that is needed. If you try to request a new connection before the previous one is released, a new connection will be made, otherwise, it will reuse the one that was released. Are you sure you simply aren't just sending the requests too fast? If you sent 21 requests at the same time, the 21st will hang. — Kevin B, Nov 05 '14 at 15:36
@KevinB , that is what the docs say and what I did. Since that doesn't wotk, that's why I'm asking for help here... — Saran, Nov 05 '14 at 15:38
Np. The request finishes (and response is returned) before I initiate another. — Saran, Nov 05 '14 at 15:40
Can you show that code? What's bugging me is that after the 20th request, getConnection doesn't hang indefinitely. If it stops "hanging", then clearly you sent it before the previous was finished, and they are getting released at some point. — Kevin B, Nov 05 '14 at 15:41
I access this api via jquery.ajax (from web app), so I initiate another request only when I see response from the previous request on the web page/log. — Saran, Nov 05 '14 at 16:16
What happens to let the 21st connection through? You mention Express returning something but it is not clear to me what that is? Is this the response in the browser or CLI? What is generating that response? — takinola, Nov 05 '14 at 19:08
@takinola, I'm not 100% sure, but I think the route responses in (server) console are generated by express (router) via morgan. One (normal) response log is: "GET / 200 19.181 ms - 1592" (for HTTP GET on path / which finished successfully in 19ms and was 1592 bytes large). — Saran, Nov 05 '14 at 19:48
Since the 21st connection finally makes it through (it is just delayed) that means the connections are actually being released. The real problem is that something somewhere is delaying the response. Can you post all the related code for the path ie all the app.use and app.get code. My guess right now is the problem is not the release of the connection but that the request execution path is getting hung up somewhere else. — takinola, Nov 05 '14 at 21:09

score 1 · Accepted Answer · answered Nov 06 '14 at 09:22

Whatever I changed, nothing seemed to work until I switched to using "express-myconnection" as middleware (as described here: http://teknosains.com/i/simple-crud-nodejs-mysql).

In short: you get the connection from the request and no need to release it afterwards as the middleware cares about that.

So, my code is now (only important parts):

app.js:

var myconnection = require('express-myconnection');
var mysql = require('mysql');

app.use(
  myconnection(mysql, {
    host: config.db.host,
    port: config.db.port,
    user: config.db.user,
    password: config.db.password,
    database: config.db.database,
    connectionLimit: config.db.connectionLimit
  }, 'request')
);

api.js:

exports.api_point = function(req, res) {

  req.getConnection(function(err, connection) {
    if (err) {
      console.error('error acquiring connection', err);

      var result = {};
      result.error = err.code;

      res.json(result);
      return;
    } else {
      var query = connection.query('SELECT * FROM tableName', function(err, rows) {
        var result = {};

        if (err) {
          console.error('error executing query: ' + err.stack);

          result.error = err.message;
        } else {
          result.data = rows;
        }

        res.json(result);
        return;
      });
    }
  });
};

node-mysql doesn't reuse connections

Problem:

1 Answers1