CODEWITHSUNDEEP
node.jsnode-mysql
Saran
Saran

Reputation: 3884

node-mysql doesn't reuse connections

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);
});

Upvotes: 0

Views: 1463

Answers (1)

Saran
Saran

Reputation: 3884

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;
      });
    }
  });
};

Upvotes: 1

Related Questions