Search code examples
mysqlnode.jsazurepool

mysql pool connection with node with the same connection acquired and released increasing on each call

I have a node application connected to MySQL using mysqljs/mysql Recently I have realized this by debugging: (server logs)

info: Url: /loginWeb
info: Method: POST
info: Connection 1098089 acquired
info: Connection 1098089 released
POST /loginWeb 200 214.618 ms - 186
info: Url: /loginWeb
info: Method: POST
info: Connection 1098089 acquired
info: Connection 1098089 acquired
info: Connection 1098089 released
info: Connection 1098089 released
POST /loginWeb 200 76.776 ms - 186
info: Url: /loginWeb
info: Method: POST
info: Connection 1098089 acquired
info: Connection 1098089 acquired
info: Connection 1098089 acquired
info: Connection 1098089 released
info: Connection 1098089 released
info: Connection 1098089 released
POST /loginWeb 200 122.202 ms - 186
info: Url: /loginWeb
info: Method: POST
info: Connection 1098089 acquired
info: Connection 1098089 acquired
info: Connection 1098089 acquired
info: Connection 1098089 acquired
info: Connection 1098089 released
info: Connection 1098089 released
info: Connection 1098089 released
info: Connection 1098089 released
POST /loginWeb 200 249.179 ms - 186
info: Url: /loginWeb
info: Method: POST
info: Connection 1098089 acquired
info: Connection 1098089 acquired
info: Connection 1098089 acquired
info: Connection 1098089 acquired
info: Connection 1098089 acquired
info: Connection 1098089 released
info: Connection 1098089 released
info: Connection 1098089 released
info: Connection 1098089 released
info: Connection 1098089 released
POST /loginWeb 200 101.805 ms - 186

As you can see in the logs, the same connection is acquired and released increasing on each call. I don't understand why.

My code is this:

const mysql = require('mysql')
const pool = mysql.createPool(config.mysql)

exports.query = function (query, params, callback) {
  pool.getConnection(function (err, connection) {
    if (err) {
      log.error('getConnection -> mysql connection error: ', err)
      throw err
    }

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

      if (!err) {
        callback(null, rows)
      }
    })

    connection.on('error', function (err) {
      connection.release()
      log.error('on error -> mysql connection error: ', err)
      throw err
    })
  })

  pool.on('release', function (connection) {
    log.info('Connection %d released', connection.threadId)
  })
  pool.on('acquire', function (connection) {
    log.info('Connection %d acquired', connection.threadId)
  })
}

NOTE: NodeJS can stablish the connection propertly but in a few minutes the connection is lost and DB returns: ECONTIMEOUT

Can someone explain this behavior?

Solution

  • In the log, all the connection's threadIds are the same. So, the pool didn't create new connection any more. In my test, if I move the following code out of "query" function, then the release and acquire events are emited once at a time.

    pool.on('release', function (connection) {
   log.info('Connection %d released', connection.threadId)
})
pool.on('acquire', function (connection) {
   log.info('Connection %d acquired', connection.threadId)
})

    You also mentioned you got a ECONTIMEOUT error. Usually, this is a networking or firewall issue. You could try to do a packet capture to see if there is a firewall blocking somewhere. If this is not the case, you'd better report the issue here for further assistance.