node-mysql2: Too many connection

OS: Ubuntu 22.04 LTS MySQL: 8.0.35 NodeJS: 20.10.0 NPM: 10.2.3 mysql2: ^3.7.0

MySQL: total threads available in my system

mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

MySQL: At any given time if I wanna see the total threads used

mysql> SHOW STATUS LIKE 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 149   |
+-------------------+-------+
1 row in set (0.00 sec)

Following is my project structure

/node-mysql
    app.js
    index.js
    /src
        /database
            mysql.js
        /cronjobs
            /** Cronjobs are run from the crontab /
            natlog.cron.js

mysql.js

require("dotenv").config();
const mysql = require("mysql2/promise");

/**
 * Connection Pool
 * Connection pools help reduce the time spent connecting to the MySQL server by reusing a previous connection leaving them open instead of closing when you are done with them.
 */

const mysqlConnection = mysql.createPool({
    host: process.env.DB_NATLOG_MYSQL_HOST,
    user: process.env.DB_NATLOG_MYSQL_USERNAME,
    password: process.env.DB_NATLOG_MYSQL_PASSWORD,
    database: process.env.DB_NATLOG_MYSQL_DATABASE,
    waitForConnections: true,
    connectionLimit: 20,
    queueLimit: 0,
    enableKeepAlive: true,
    keepAliveInitialDelay: 0,
});

mysqlConnection.on('acquire', function (connection) {
    console.log('Connection %d acquired', connection.threadId);
});

mysqlConnection.on('enqueue', function () {
console.log('Waiting for available connection slot');
});

mysqlConnection.on('release', function (connection) {
console.log('Connection %d released', connection.threadId);
});

module.exports = mysqlConnection;

I am using the mysql.js in the following file

natlog.cron.js

require("dotenv").config();
const { dateHelper } = require("../helpers");
const mysqlConnection = require("../database/mysql");

async function runConntrackAndSaveToLog() {
    try {
        const currentDate = dateHelper.currentDateInYYYMMDD();
    
        const natlogs = extractConnections(data);

        if (natlogs) {
            for await (const natlog of natlogs) {
                if (natlog.mac.includes("incomplete")) {
                    continue;
                }

                natlogValues += `
                    (
                        "${natlog.src}",
                        "${natlog.dst}",
                        "${natlog.sport}",
                        "${natlog.dport}",
                        "${natlog.mac}"
                    ),
                `;
            }

            /** Remove the last comma of the values */
            natlogValues = natlogValues.replace(/,(?=\s*$)/, '');

            const natlogQuery = `
                INSERT INTO \`${currentDate}\`
                    (
                        src_ip,
                        dest_ip,
                        src_port,
                        dest_port,
                        user_mac_address
                    )
                VALUES
                    ${natlogValues}
            `;

            await mysqlConnection.query(natlogQuery);
        }
    } catch (error) {
        console.log({
            location: "Natlog cronjob error",
            message: error.message,
            stack: error.stack,
        });
    }
}

(async () => {
    console.log("CronJob: Nat Log...");
    await runConntrackAndSaveToLog();
})();

I am calling cronjob every everyminute which runs the above code but for some reason the connection process are being overflown

* * * * * (cd /home/chan/node-mysql && /home/chan/.nvm/versions/node/v20.10.0/bin/node src/cronjobs/natlog.cron.js) 2>&1

ERROR

/home/chan/node-mysql/node_modules/mysql2/promise.js:356
    const localErr = new Error();
                     ^

Error: Too many connections
    at PromisePool.query (/home/chan/node-mysql/node_modules/mysql2/promise.js:356:22)
    at createNatlogTableIfNotExists (/home/chan/node-mysql/src/cronjobs/natlog.cron.js:11:33)
    at /home/chan/node-mysql/src/cronjobs/natlog.cron.js:45:27
    at FSReqCallback.readFileAfterClose [as oncomplete] (node:internal/fs/read/context:68:3) {
  code: 'ER_CON_COUNT_ERROR',
  errno: 1040,
  sql: undefined,
  sqlState: '',
  sqlMessage: 'Too many connections'
}

About this issue

  • Original URL
  • State: open
  • Created 3 years ago
  • Reactions: 1
  • Comments: 18

Most upvoted comments

As you mentioned that you had posted in NextJS forum which means you might be using NextJS, So probably the hot reload might invalidate the singleton. But kindly check the thread if the thread acquired are being released or not by login to MySQL cli or any MySQL client with the following command

SHOW STATUS LIKE 'Threads_connected'

Already fixed by creating a global variable . Thanks

Hi . I have the same problem . Have you check mysql server monitoring if like connection stops but process keeps on increasing ? I noticed that connection is getting closed but the process keeps increasing in my mysql server.

But still the problem persists for createPool as the threads are not being released after conn.query is finished executing the query. The issue has to be rectified and solved in the library.

After creating a singleton and putting a global variable it seems to be working fine. Probly update on how to use the library will be a good step since most of javascript framework do Hot Reload which will I think cause the problem . A didicated tutorial on how to create a class or function that will create the connection and a file that will assign the connection to global variable and return it if available.

Good thought. Would you like to update the docs and send a PR to team, that would be amazing as you have the complete example with context on the same.

idk how to created docs or tutorial mostly informative one . but this are the code i have

connectionClass.ts

import mysql, { Pool, PoolConnection } from 'mysql2/promise';
const { DB_HOST, DB_USER, DB_PASSWORD, DB_NAME } = process.env;

export  class Connection {
  private static instance: Connection;
  private pool: Pool;

  private constructor() {
    // Set up your MySQL connection pool parameters
    const poolConfig: mysql.PoolOptions = {
      host: DB_HOST,
    user: DB_USER,
    password: DB_PASSWORD,
    database: DB_NAME,
    waitForConnections: true,
    connectionLimit: 10,
    idleTimeout: 5000,
    queueLimit: 0,
    };

    this.pool = mysql.createPool(poolConfig);
  }

  public static getInstance(): Connection {
    if (!this.instance) {
      this.instance = new Connection();
    }

    return this.instance;
  }

  public async getConnection(): Promise<PoolConnection> {
    return this.pool.getConnection();
  }

  // You can add other methods or configurations as needed

  public async closePool(): Promise<void> {
    await this.pool.end();
  }
}

db.ts

import {Connection} from "./connectionClass";
//Create global variable so it would not get overwritten or removed when hotreload happens
declare global {
  var instance: undefined | Connection;
}
//Check if global instance is already available  if not then you can get new instance if global instance is available then it would //use that instead and assign to variable where you will return so you can use it to other api files . 
//Check if environment is in production or not . if it is then it would set the global instance from instance instead . 
if (process.env.NODE_ENV !== 'production') globalThis.instance=instance;
export default  instance;

That are the codes . idk how others would implement this but that is the way i did.

Resource i found this thing from : Prisma Instantiation Practice

But still the problem persists for createPool as the threads are not being released after conn.query is finished executing the query. The issue has to be rectified and solved in the library.

After creating a singleton and putting a global variable it seems to be working fine. Probly update on how to use the library will be a good step since most of javascript framework do Hot Reload which will I think cause the problem . A didicated tutorial on how to create a class or function that will create the connection and a file that will assign the connection to global variable and return it if available.

Good thought. Would you like to update the docs and send a PR to team, that would be amazing as you have the complete example with context on the same.

But still the problem persists for createPool as the threads are not being released after conn.query is finished executing the query. The issue has to be rectified and solved in the library.

After creating a singleton and putting a global variable it seems to be working fine. Probly update on how to use the library will be a good step since most of javascript framework do Hot Reload which will I think cause the problem . A didicated tutorial on how to create a class or function that will create the connection and a file that will assign the connection to global variable and return it if available.