conn.release() doesn't close the connection

[TomMiller-mas]

I have reviewed Issue #211

This is running in a loop every 1 minute. The first query is to see if the email job should run, and the function not shown (processEmails) checks to see if there are any emails to send. Over time, I get an error that there are no connections available. In general, if it is a simple SELECT, I don't do a transaction. So when I release a connection, does it also close it too? Every time this "loop" runs, the "Connection" info reads 2 more Active and 2 less Idle. Do I need to close the connection and then return it to the pool? How do I close a connection?

async function processSchdEmails() {
  dlog("Trace", "Enter processSchdEmails");
  let mssqlConn;
  let mdbConn;

  if (getProgramName() === "mas-auth-srv") {
    dlog("Trace", "Enter processSchdEmails - mas-auth-srv");
    try {
      const mdbpool = getPool();
      **console.log(
        "Connections  Total:" +
          mdbpool.totalConnections() +
          " Active:" +
          mdbpool.activeConnections() +
          " Idle:" +
          mdbpool.idleConnections()
      );**

      connConfig = await mdbpool.getConnection();
      const resultConfig = await connConfig.query(
        "SELECT KeyValue FROM EM_Config WHERE SubSys = 'EMAIL' AND KeyName = 'EmailServerEnabled'; ",
        []
      );
      dlog("processSchdEmails resultConfig", JSON.stringify(resultConfig[0].KeyValue));

      if (JSON.stringify(resultConfig).includes("@full_error")) {
        const dbResponseErrorMsg = JSON.stringify(resultConfig[0][0]["@full_error"]);
        console.log("Process Email Get Config Error: " + dbResponseErrorMsg);
      }

      if (parseInt(resultConfig[0].KeyValue) == true) {
        dlog("Trace", "processSchdEmails call schEmailData");
        let schEmailData = {
          dbId: "00000000-0000-0000-0000-000000000000",
          db: "mdb",
          RequestUserId: 1,
        };

        await processEmails(schEmailData);
      }
    } catch (err) {
      console.log("processEmails exception: dbId=" + data.dbId + "  Error:" + err);
    } finally {
      if (mdbConn) mdbConn.release();
      dlog("Trace", "Exit processSchdEmails - mas-auth-srv");
    }
  }
}

After 15 loops, this is what is showing on the console: Connections Total:100 Active:30 Idle:70

[TomMiller-mas]

So I have continued to research and I have also tried using end().

    if (mdbConn) {
      mdbConn.end();
      mdbConn.release();
    }

I have also added the option resetAfterUse: true, to the pool config.

Nothing works. I saw on StackOverflow this answer: https://stackoverflow.com/questions/67831413/nodejs-mariadb-connection-not-disconnecting

The only problem is they are killing the Pool, so why even have a pool? This is a significant problem with the Pool. When you release a connection it should absolutely reset it. If it thinks it is still active, then there should be a force option to roll back and reset it anyway. I don't care if it rolls back a SELECT because I already have used the data from the SELECT by the time we get to the finally block.

[TomMiller-mas]

Found my problem. We are going through the process of updating our naming convention and I missed connConfig. Once I renamed it to mdbConn, the problem went away. I likely would have found it earlier, but I had another "leak" and there, everything was named properly. So I didn't even look for a naming issue.