node-mysql2: Pool not releasing connections if maxIdle is not set

All of my queries include dbConnection.release(), however the connections do not get released and once connectionLimit is reached, the execution pauses waiting for an available connection.

I’ve re-worked my code so that every pool.getConnection() has a dbConnection.release() right next to it, to make absolutely certain that I’m not accidentally leaving any connections unreleased.

However, this did not help, and it seems that there is a bug in the releasing of pool connections.

I store my pool in a static variable with a class called Global.

class globals
{
	static dbPool = null;
}

Runs once on startup

		globals.dbPool = await mysql.createPool({
				host: databaseServer,
				port: databasePort,
				user: databaseUsername,
				password: databasePassword,
				database: databaseName,
				connectionLimit: 10
			});

Example usage

        const dbConnection = await globals.dbPool.getConnection();

        let mysqlResponse = null;

        await dbConnection.query(`SELECT * FROM guild;`).then(result => {
            mysqlResponse = result[0];

        }).catch(err => console.log(err));

        dbConnection.release();
Result after some queries have been executed 
![image](https://github.com/sidorares/node-mysql2/assets/12071228/17c7aca3-567e-4202-8ebe-b202fcba6c5d)

About this issue

  • Original URL
  • State: open
  • Created 3 years ago
  • Reactions: 2
  • Comments: 18 (1 by maintainers)

Most upvoted comments

I’m sorry. Let me clarify my fix…

In order to get it to cleanup old released connections, I had to set maxIdle < connectionLimit. Without this, my connections maxed out for no reason, and I ended up with 10 idle connections, and no further queries would execute.

If maxIdle is default (eg. maxIdle => connectionLimit), no cleanup of old released connections occurs as shown below.

https://github.com/sidorares/node-mysql2/blob/b87ee4456556712cb8b0538f2f264d9a9fe5764a/lib/pool.js#L30

Unfortunately, I cannot create a demo repo. I’ve never posted code to github and wouldn’t know where to start.

I would like to provide my company’s experience with this issue:

We noticed a high level of timeouts and failures, and ran across this issue. We can an experiment across some of our services.

  • For about 2.5 weeks we explicitly set maxIdle = 1, and noticed 13 timeouts (a rate of 1 every 1.33 days).
  • For 8 days we did not set maxIdle (it should default to connectionLimit, which was 10), and noticed an evenly-distributed 248 timeouts (a rate of 1 every 33 minutes).
  • We went back and forth turning this on and off to make sure it wasn’t a fluke and every time setting maxIdle = 1 fixed the problem.

I suppose it could be related to something else, but @wellwelwel or @sidorares I have the same question as @dtimofeev:

shouldn’t _removeIdleTimeoutConnections be called in Pool constructor, regardless of the maxIdle is lower than the connectionLimit?

I’m worried this has a much larger impact than may be assumed.

If we have maxIdle setting equal to connectionLimit (which is also the default configuration if maxIdle is not manually set) then the cleanup process will not be initiated at all. This means that connection exceeding their idleTimeout will never be closed/destroyed.

The only workaround I see at the moment is to explicitly configure maxIdle to connectionLimit - 1 in order for the cleanup to start at all.

@wellwelwel, shouldn’t _removeIdleTimeoutConnections be called in Pool constructor, regardless of the maxIdle is lower than the connectionLimit?

This happens to me too. I’m having exactly the same problem. Here is my codes;

db.js

import getConfig from "next/config";
import mysql from "mysql2/promise";

const { serverRuntimeConfig } = getConfig();
const { host, port, user, password, database } = serverRuntimeConfig.dbConfig;

const pool = mysql.createPool({
  host,
  port,
  user,
  password,
  database,
  timezone: "+00:00",
  waitForConnections: true,
  enableKeepAlive: true,
  connectionLimit: 10,
  keepAliveInitialDelay: 0,
  queueLimit: 0,
});

export { pool };

users-repo.js

export const usersRepo = {
  ...
  getAllUsers,
  SomeFunc,
};

async function getAllUsers(data) {
  const db = await pool.getConnection();
  try {
    const tableName = data.companyCodeShort + "_users";
    const [allusers] = await db.query("SELECT * FROM ?? WHERE rank != '-1'", [
      tableName,
    ]);

    allusers.forEach((a) => {
      delete a.hash;
      delete a.id;
    });

    return allusers;
  } finally {
    db.release();
  }
}

async function SomeFunc(data) {
  const db = await pool.getConnection();
  try {
    const [users] = await db.query("SHOW STATUS LIKE '%Threads_%'");
    return users;
  } finally {
    db.release();
  }
}

SomeFunc output before getAllUsers function

[
  { Variable_name: 'Threads_cached', Value: '0' },
  { Variable_name: 'Threads_connected', Value: '4' },
  { Variable_name: 'Threads_created', Value: '39' },
  { Variable_name: 'Threads_running', Value: '1' }
]

SomeFunc output after getAllUsers function

[
  { Variable_name: 'Threads_cached', Value: '0' },
  { Variable_name: 'Threads_connected', Value: '5' },
  { Variable_name: 'Threads_created', Value: '39' },
  { Variable_name: 'Threads_running', Value: '1' }
]

SomeFunc output after some time has passed

[
  { Variable_name: 'Threads_cached', Value: '0' },
  { Variable_name: 'Threads_connected', Value: '5' },
  { Variable_name: 'Threads_created', Value: '39' },
  { Variable_name: 'Threads_running', Value: '1' }
]

In that test, Try removing maxIdle so it’s default. Then run the test, pause in the middle for at least 5 seconds to allow idleTimeout to be reached, and then see if it releases the connections that have reached the idleTimeout. Also see if you can create a 6th connection after the idleTimeout has been reached on the first 5 connections. It should allow a 6th connection if the first 5 exceed their idleTimeout. I’d try it myself but I dont know how to run those tests.