Problem with Mysql Pool conections

Question

I have this class I use to create the connection:

class DbConnection {
  constructor() {
    this._con = undefined;
    this._pool = undefined;
  }

  _createPool(  database , user, password ) {
    this._pool =  mysql.createPool({
      connectionLimit: 100,
      host: "localhost",
      port: 3306,
      database: database,
      user: user,
      password: password
    });
  }

  async openConnection( req ) {
    this._createPool(req.database, req.user, req.password );

    return new Promise((resolve, reject) => {
      this._pool.getConnection((err, connection) => {
        if (err)
          reject( err );
        this._con = connection;
        resolve();
      });
    });
  }

  async beginTransaction() {
    return new Promise((resolve, reject) => {
      this.con.beginTransaction(err => {
        if (err) {
          this.con.rollback( () => this.con.release() );
          reject( err );
        }
        resolve();
      });
    });
  }

  get con() {
    return this._con;
  }

  close() {
    if (this.con)
      this.con.release();
  }
}

This is the control:

class GenericCtrl {

  constructor( db, dao ) {
    this._db = db;
    this._dao = dao;
  }

  async buscar( req, res ) {
    req.assert( 'id', 'um id é necessário.' ).notEmpty().isInt().withMessage('Deve ser inteiro');

    if (req.validationErrors())
      return res.status( 400 ).json( { erros: req.validationErrors() } );

    try {
      await this._db.openConnection( req );
      let response = await this._dao.read( req.params.id  );

      res.status( 200 ).json( response );

    } catch (e) {
      res.status( 500 ).json( { erro: e.toString() } );

    } finally {
      this._db.close();
    }
  }

}

This is the DAO:

class GenericDao {

  constructor( db ) {
    this._db = db;
  }

  get con() {
    return this._db.con;
  }

  read( query, sql = undefined, inserts = undefined ) {

    sql = "SELECT * FROM ?? WHERE ?? = ?";
    inserts = [ 'Usuario', 'id', 23 ];

    sql = mysql.format(sql, inserts);

    return new Promise( ( resolve, reject ) => {
      this.con.query( sql, ( err, result ) => err ? reject( err ) : resolve( result ) );
    });
  }

}

It has more Code, I just removed the part that I think necessary for the issue.

The code works, but sometimes it shows an error in the log as the message:

Unhandledpromiserejectionwarning: Error: Connection already Released

I don’t know how to fix this. Can anyone help me? If you need more details I will update the question. Thanks in advance!

Answer 1

The problem is that as calls are made asynchronously, you can create a new connection to each request and, before the first one is completed, a second one can be started. The path is you use the variables and returns within a defined scope or use the function directly query of pool (and in consequence "wipe" enough your code).

Pooling Connections

Rather than Creating and Managing Connections one-by-one, this module also provides built-in Connection pooling using mysql.createPool(config). Read more about Connection pooling.

Create a pool and use it directly:

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
});

pool.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

This is a shortcut for the pool.getConnection() -> connection.query() -> connection.release() code flow. Using pool.getConnection() is Useful to share Connection state for subsequent queries. This is because two calls to pool.query() may use two Different Connections and run in Parallel.

In free translation:

Instead of creating and managing connections one by one, this module also provides a pooling built-in connection using mysql.createPool(config). Read more about pooling connecting.

Create a pool and use it directly:

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
});

pool.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

This is a shortcut to the code stream pool.getConnection() -> connection.query() -> connection.release(). Use pool.getConnection() is useful for sharing the connection status for subsequent queries. This is why two calls to the pool.query() can use two different connections and run in parallel.

So in short you can remove all manipulations the variable con (including creating a new connection), add a get to the pool, remove the finally of your controller and change your method read for:

// ...
read(query, sql, inserts) {

  sql = 'SELECT * FROM ?? WHERE ?? = ?';
  inserts = ['Usuario', 'id', 23];

  sql = mysql.format(sql, inserts);

  return new Promise( ( resolve, reject ) => {
    this.pool.query( sql, ( err, result ) => err ? reject( err ) : resolve( result ) );
  });
}
// ...

There are also some improvements you can make with regards to promises, using async and await and simplifying your code, but I won’t go into that in this answer.