pool cluster no response on WRITER

[quanguyen]

Hi,

I have set up an AWS Aurora MySQL (v5.7) with WRITER/READER and tried to connect from my nodejs (v8.5.0) using mysql version 2.14.1.

const mysql = require("mysql")
const poolCluster = mysql.createPoolCluster()
poolCluster.add("WRITER", {
  connectionLimit: 5, // 10 by default
  host: <link to Aurora WRITER>,
  user: 'myuser',
  password: 'mypass',
  database: 'mydb'
})
poolCluster.add("READER", {
  connectionLimit: 5, // 10 by default
  host: <link to Aurora READER>,
  user: 'myuser',
  password: 'mypass',
  database: 'mydb'
});

function query2(q) {
  console.log(new Date())
  console.log("[ query2 ]", q)

  return new Promise((resolve, reject) => {    
    const type = q.search(/^[^\w]*\(?SELECT/) == 0 ? "READER" : "WRITER"
    
    poolCluster.getConnection(type, (error, connection) => {
      if (error) {
        console.log(new Date(), "[ query2 ] failed to get connection from", type, q)
        reject(error)
      }
      else {
        console.log("[ query2 ] acquired", type, connection.threadId, q)

        connection.query(q, (error, results) => {
          if (error) {
            console.log(new Date(), "[ query2 ] failed to query on", connection.threadId)
            reject(error)
          }
          else {
            /*
             * return the connection to the pool
             * otherwise, new connection will be made until `connectionLimit` is reached => max `connectionLimit` queries are run!
             */
            connection.release()
            // connection.destroy() // when pool has no connections, the program ends!

            console.log(new Date(), "[ query2 ] result on", connection.threadId)
            resolve(results)
          }
        })
      }
    })
  })
}

query2 has been working fine for a week, until recently, it stops responding with all INSERT/UPDATE query, such as:

await query2("INSERT INTO ...")

This causes all commands after this await hangs up, not executing!

Please note that when this happened, event loop still worked as my nodejs program still received commands from client and ran SELECT query successfully.

I have some questions:

1. What could cause my INSERT/UPDATE hang up? Is it because its connectionLimit: 5 was too few? I increase it to 10 in the meantime my issue is responded.
2. Is this regex ok to use const type = q.search(/^[^\w]*\(?SELECT/) == 0 ? "READER" : "WRITER" in my query2? As I read from this link, the author suggests regex should be avoided. But as I have noted, when this happened, the event loop still worked, and SELECT queries still ran well.
   Anyway, I still change it to const type = q.startsWith("SELECT") || q.startsWith("(SELECT") ? "READER" : "WRITER"

Looking forward to your reply.

[mbaumgartl]

Have a look at waitForConnections and queueLimit pool options. Your queries should not hang but fail 😉

You can also listen the pool events to get an idea what the problem could be.

I'm not into the AWS stuff, but in the end you have to debug why your queries take that long.

BTW: You don't release the connection in case of an error. I think you can move connection.release() to the top of the (inner) callback method:

connection.query(q, (error, results) => {
    connection.release()
    if (error) {
        console.log(new Date(), "[ query2 ] failed to query on", connection.threadId)
        reject(error)
    } else {
        // ....
    }
}

[quanguyen]

Have a look at waitForConnections and queueLimit pool options. Your queries should not hang but fail 😉

You can also listen the pool events to get an idea what the problem could be.

I'm not into the AWS stuff, but in the end you have to debug why your queries take that long.

BTW: You don't release the connection in case of an error. I think you can move connection.release() to the top of the (inner) callback method:

connection.query(q, (error, results) => {
    connection.release()
    if (error) {
        console.log(new Date(), "[ query2 ] failed to query on", connection.threadId)
        reject(error)
    } else {
        // ....
    }
}

waitForConnections and queueLimit are now default.
How should I look at their values in real time?
By the way, is there a way to read the current number of active connections in a pool cluster, for READER and WRITER?

Much appreciate for the hint connection.release(). This is probably the root cause since when no connections are available (due to some stuck, probably at database side), new requests just wait there.
I also updated mysql to version 2.18.1

Regards,

[mbaumgartl]

waitForConnections and queueLimit are pool configuration options. They change the way the pool reacts to increasing waiting connections. You don't need to watch them in realtime 😉

For debugging purposes you could output the size/length of these arrays: https://github.com/mysqljs/mysql/blob/master/lib/Pool.js#L15 (maybe in an interval).