Node.js connection pool produces infinite loop on error

Question

I recently upgraded my generic-pool package to version 3 that makes use of promises - a concept I probably don't understand enough. I have managed to get it working, despite some unexpected differences in operation.

The problem I have is when I start testing error conditions. I have purposely set the password wrong, and when testing, I get an infinite loop of "connection failed" errors - indicating something is triggering the create function despite the error. I assume I have either configured the pool incorrectly, or I am acquiring improperly.

Generic pool factory:

const poolFactory = {
  create: function() {
    return new Promise(function(resolve, reject) {
      var client = mysql.createConnection({
        host: config.host,
        user: config.user,
        password: config.pass,
      });
      client.connect(function(err) {
        if (err != null) {
          log.write('ERROR', "Connection Error: MySQL: " + err.message);
          reject(err);
        } else {
          log.write('INFO', "MySQL Connection created.");
          resolve(client);
        }
      });

    })
  },
  destroy: function(client) {
    return new Promise(function(resolve) {
      client.end(function(err) {
        if (err != null) {
          log.write('ERROR', "DB Error: MySQL: " + err.message);
        } else {
          log.write('INFO', "Database connection closed.");
          resolve();
        }
      });
    })
  }
}

const cp = genericPool.createPool(poolFactory);

Test query that triggers the connection error:

cp.acquire().then(
  function(client) {
    client.query('USE ' + config.db, function(err, results, fields) {
      if (err != null) {
        log.write('ERROR', "DB test error: MySQL: " + err.message);
      } else {
        log.write('INFO', "MySQL connection tested successfully.");
        cp.release(client)
      }
    });
  }).catch(function(err) {
  cp.release(client);
  log.write('ERROR', "Pool Error: " + err.message);
});

My error log fills up with a million lines of:

 Connection Error: MySQL: ER_ACCESS_DENIED_ERROR: Access denied for user 'user'@'localhost' (using password: YES)

I am expecting a single error because I am testing error conditions. What am I doing wrong to get the infinite loop? I thought the reject(err) was supposed to put the promise in a state where it would not answer any more queries?

Can anyone point me in the right direction?

As always - thank-you very much!

EDIT: Here is a complete script that illustrate the problem if anyone cares to see the problem first hand! The console fills up with "ERROR MySQL Connection Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'devUser'@'localhost' (using password: YES)". Thanks again.

// Test App to show logging issue

var pool = require('generic-pool'),
  mysql = require('mysql')

var config = {
  port: 8880,
  host: 'localhost',
  user: 'devUser',
  pass: 'wrong-pass',
  db: 'node-app-db'
}

const poolConfig = {
  max: 3
};

const poolFactory = {
  create: function() {
    return new Promise(function(resolve, reject) {
      var client = mysql.createConnection({
        host: config.host,
        user: config.user,
        password: config.pass,
      });
      client.connect(function(err) {
        if (err != null) {
          console.log('ERROR', "MySQL Connection Error: " + err.message);
          reject(err);
        } else {
          console.log('USAGE', "MySQL Connection created. " + cp.size + " of " + config.poolSize + " connections to DB in use.");
          resolve(client);
        }
      });

    })
  },
  destroy: function(client) {
    return new Promise(function(resolve) {
      client.end(function(err) {
        if (err != null) {
          console.log('ERROR', "DB Error: MySQL: " + err.message);
        } else {
          console.log('USAGE', "Database connection closed. Pool contains ' + cp.size + ' more connections.");
          resolve();
        }
      });
    })
  }
}

const cp = pool.createPool(poolFactory, poolConfig);

cp.acquire().then(
  function(client) {
    client.query('USE ' + config.db, function(err, results, fields) {
      if (err != null) {
        console.log('ERROR', "DB test error: MySQL: " + err.message);
      } else {
        console.log('READY', "MySQL connection tested successfully. DataServer ready for connections.");
        cp.release(client)
      }
    });
  }).catch(function(err) {
  cp.release(client);
  console.log('ERROR', "Pool Error: " + err.message);
});

Answer 1

Cross posting from my original response on the github issue

hey @whiteatom - yep you've hit "known problem" land...

for some history... In v2 a given call to pool.acquire was directly tied to a single call to factory.create and an error in the factory.create would bubble through to the pool.acquire. (this was generally bad ™️ ) In v3 calls to pool.acquire were no longed tied to calls to factory.create and therefore any errors in factory.create could not bubble up to pool.acquire calls because it didn't make any semantic sense. Instead factory errors are now exposed via the Pool itself via event emitters

So to sum up slightly: the promise returned by pool.acquire will only reject due to errors specifically related to the acquire call (such as timeout), and not because of any other errors in the pool. To catch general errors with the pool you will need to attach some event listeners to the Pool instance you have.

There is still the problem that it's possible for the pool to end up in some infinite loop if your factory.create returns promises that only ever reject. To get around this you can build backoff functionality into your factory.create function (this is a bit of a hack though, and I really need to find some way to put backoff in the pool itself.)