Too many connection

[channaveer]

Too many connection

[Ejayz]

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

[Ejayz]

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.

[channaveer]

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.

[Ejayz]

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

[Ejayz]

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.

[channaveer]

@Ejayz yes this happened with me. When I tried with connection pool ie createPool but as you said the connection is released but the thread count keeps increasing and after reaching MySQL max thread limit everything dies and i keep getting the above error. I had to restart MySQL service to login to MySQL cli.

For time being I had to fallback to createConnection code and in the end I am releasing the connection. This one is working fine. But make sure to restart MySQL service so that the threads that are already stuck gets released and works well.

Seems like createPool is not releasing the thread and has issue for sure.

[Ejayz]

Hmmmm i see . Welp probly this is a bug or something . When would this be fixed thats the question .

[Ejayz]

Welp idk what are the pros and cons of using createConnection vs createPool @wifidabba but if there are no options then it is createConnection for now . Hope it would have update about this . stopping connection on createPool will also show error. But dont you think we can do some checking on Connection.On method ? like monitor it and close this threads?

[Ejayz]

This is still happening in createConnection . It does not drop threads and keeps on increasing even i have connection.destroy() anyidea how to stop this threads after the query? we can get threadId can we manually stop that threads using that thread id

[Ejayz]

hi @channaveer . I asked on one of nextjs community and it seems i find one of the solution . Create a singleton that will be used the same instance of connection instead of creating multiple instance each import . https://www.prisma.io/docs/orm/more/help-and-troubleshooting/help-articles/nextjs-prisma-client-dev-practices#solution This is his refference that he sent to my . Though this is the code i made chat gpt generated and seems to work fine

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() {
    console.log('Creating MySQLPoolSingleton instance'); // Add this line
    // 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 (!Connection.instance) {
      Connection.instance = new Connection();
    }

    return Connection.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();
  }
}

PS: yea i made chat gpt generate it .

Anyways a singleton sample on documentation would be good for samples on js. since most of files i guess are imported and recreated a connection

[channaveer]

This is still happening in createConnection . It does not drop threads and keeps on increasing even i have connection.destroy() anyidea how to stop this threads after the query? we can get threadId can we manually stop that threads using that thread id

As soon as after my query is completed I am ending the connection in the following manner and its working for me. If you are working on the medium to bigger project then I think going ahead with your singleton solution for time being is good one.

/** Other code */
const natlogQuery = `MY QUERY`;

(await mysqlConnection).query(natlogQuery);

(await mysqlConnection).end();

[Ejayz]

Do you think hot reload will invalidate single ton ?

[channaveer]

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'

[channaveer]

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.