About the "Too many connections" behavior

[Jasermon]

Environment

Knex version: "^3.1.0"
Database + version: MariaDB -- 10.4.28-MariaDB
OS: Windows 10 64 Bit

Issue Explaining

Until yesterday, I was using node-mysql2. I was getting "Too many connections" error when using node-mysql2. I did not attempt to solve this error until today, thinking it was a minor problem. I tried a lot of solutions by doing research and I was checking whether it was solved or not by looking at the following command:

SHOW STATUS LIKE '%onn%'

The output would always be the same. Threads_connected, Connections and Max_used_connections would always increase, never decrease.

** Although I don't know exactly, I think the "Too many connections" error is caused by one of these 3 pieces of information. Please correct me if I'm wrong.

When I couldn't figure it out, I discovered knex. It was obvious that knex was a very comprehensive library. I thought this would solve the problem, but the result was the same. I'm not quite sure what I can do anymore.

• Does the "Too many connections" error occur when one of these 3 values reaches 151?
• Is it normal that these 3 values constantly increase and never decrease?
• If they should decrease, why don't they decrease at all? I think there is no problem in my code.

Error message: Too many connections.

Codes

db.js

import getConfig from "next/config";
import knex from "knex";

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

const pool = knex({
  client: "mysql",
  connection: {
    host,
    port,
    user,
    password,
    database,
  },
  pool: {
    min: 2,
    max: 10,
  },
});

export { pool };

users-repo.js

...
import { pool } from "_helpers/api";
...

export const usersRepo = {
  ...
  getAllUsers,
  addUser,
  getStatus,
};

async function getAllUsers(data) {
  const db = await pool.transaction();
  try {
    ...
    const [allusers] = await db.raw(
      "SELECT u.*, uex.* FROM ?? AS u LEFT JOIN ?? AS uex ON u.userid = uex.userid WHERE rank != '-1'",
      [tableName, leftJoin]
    );
    await allusers.forEach((a) => {
      delete a.hash;
      delete a.id;
    });
    await db.commit();
    return allusers;
  } catch (error) {
    const errorMsg = await beCatchError(error);
    await db.rollback();
    throw errorMsg;
  }
}

async function addUser(data) {
  const db = await pool.transaction();
  try {
    ...
    const [[checkUser]] = await db.raw("SELECT ... FROM ?? WHERE ... = ?", [
      tableName,
      data.username,
    ]);

    if (checkUser) {
      throw `Username "${data.username}" already exists.`;
    }

    const [getAllUniqueIds] = await db.raw("SELECT ... FROM ??", [
      tableName,
    ]);

    let generateUserid = uuidv4();
    while (JSON.stringify(getAllUniqueIds).includes(generateUserid)) {
      generateUserid = uuidv4();
    }
    const hashedPassword = bcrypt.hashSync(data.password, 10);

    await db.raw(
      "INSERT INTO ??(...) VALUES(...)",
      [
        ...
      ]
    );
    await db.raw("INSERT INTO ??(userid) VALUES(?)", [
      leftJoin,
      generateUserid,
    ]);
    await db.commit();
    return { msg: "User added.", data: newUserData };
  } catch (error) {
    const errorMsg = await beCatchError(error);
    await db.rollback();
    throw errorMsg;
  }
}

async function getStatus(data) {
  const db = await pool.transaction();
  try {
    const [connection] = await db.raw("SHOW STATUS LIKE '%onn%'");
    await db.commit();
    return connection;
  } catch (error) {
    await db.rollback();
  }
}

These are the codes I used in the first test. In my second test I just added this and the result was still the same:

finally {
  await db.destroy();
}

First Test Results

The first query when I run the project with yarn dev:

[
  RowDataPacket { Variable_name: 'Aborted_connects', Value: '0' },
  RowDataPacket {
    Variable_name: 'Aborted_connects_preauth',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_accept',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_internal',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_max_connections',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_peer_address',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_select',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_tcpwrap',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Connections', Value: '8' },
  RowDataPacket { Variable_name: 'Max_used_connections', Value: '1' },
  RowDataPacket {
    Variable_name: 'Performance_schema_session_connect_attrs_lost',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Slave_connections', Value: '0' },
  RowDataPacket { Variable_name: 'Slaves_connected', Value: '0' },
  RowDataPacket { Variable_name: 'Ssl_client_connects', Value: '0' },
  RowDataPacket {
    Variable_name: 'Ssl_connect_renegotiates',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Ssl_finished_connects', Value: '0' },
  RowDataPacket { Variable_name: 'Threads_connected', Value: '1' }
]

After a few queries

[
  RowDataPacket { Variable_name: 'Aborted_connects', Value: '0' },
  RowDataPacket {
    Variable_name: 'Aborted_connects_preauth',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_accept',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_internal',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_max_connections',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_peer_address',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_select',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_tcpwrap',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Connections', Value: '11' },
  RowDataPacket { Variable_name: 'Max_used_connections', Value: '4' },
  RowDataPacket {
    Variable_name: 'Performance_schema_session_connect_attrs_lost',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Slave_connections', Value: '0' },
  RowDataPacket { Variable_name: 'Slaves_connected', Value: '0' },
  RowDataPacket { Variable_name: 'Ssl_client_connects', Value: '0' },
  RowDataPacket {
    Variable_name: 'Ssl_connect_renegotiates',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Ssl_finished_connects', Value: '0' },
  RowDataPacket { Variable_name: 'Threads_connected', Value: '4' }
]

After waiting 5 minutes

[
  RowDataPacket { Variable_name: 'Aborted_connects', Value: '0' },
  RowDataPacket {
    Variable_name: 'Aborted_connects_preauth',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_accept',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_internal',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_max_connections',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_peer_address',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_select',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_tcpwrap',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Connections', Value: '11' },
  RowDataPacket { Variable_name: 'Max_used_connections', Value: '4' },
  RowDataPacket {
    Variable_name: 'Performance_schema_session_connect_attrs_lost',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Slave_connections', Value: '0' },
  RowDataPacket { Variable_name: 'Slaves_connected', Value: '0' },
  RowDataPacket { Variable_name: 'Ssl_client_connects', Value: '0' },
  RowDataPacket {
    Variable_name: 'Ssl_connect_renegotiates',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Ssl_finished_connects', Value: '0' },
  RowDataPacket { Variable_name: 'Threads_connected', Value: '4' }
]

Second Test Results (with db.destroy() )

The first query when I run the project with yarn dev:

[
  RowDataPacket { Variable_name: 'Aborted_connects', Value: '0' },
  RowDataPacket {
    Variable_name: 'Aborted_connects_preauth',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_accept',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_internal',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_max_connections',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_peer_address',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_select',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_tcpwrap',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Connections', Value: '8' },
  RowDataPacket { Variable_name: 'Max_used_connections', Value: '1' },
  RowDataPacket {
    Variable_name: 'Performance_schema_session_connect_attrs_lost',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Slave_connections', Value: '0' },
  RowDataPacket { Variable_name: 'Slaves_connected', Value: '0' },
  RowDataPacket { Variable_name: 'Ssl_client_connects', Value: '0' },
  RowDataPacket {
    Variable_name: 'Ssl_connect_renegotiates',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Ssl_finished_connects', Value: '0' },
  RowDataPacket { Variable_name: 'Threads_connected', Value: '1' }
]

After a few queries

[
  RowDataPacket { Variable_name: 'Aborted_connects', Value: '0' },
  RowDataPacket {
    Variable_name: 'Aborted_connects_preauth',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_accept',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_internal',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_max_connections',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_peer_address',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_select',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_tcpwrap',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Connections', Value: '12' },
  RowDataPacket { Variable_name: 'Max_used_connections', Value: '5' },
  RowDataPacket {
    Variable_name: 'Performance_schema_session_connect_attrs_lost',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Slave_connections', Value: '0' },
  RowDataPacket { Variable_name: 'Slaves_connected', Value: '0' },
  RowDataPacket { Variable_name: 'Ssl_client_connects', Value: '0' },
  RowDataPacket {
    Variable_name: 'Ssl_connect_renegotiates',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Ssl_finished_connects', Value: '0' },
  RowDataPacket { Variable_name: 'Threads_connected', Value: '5' }
]

After waiting 5 minutes

[
  RowDataPacket { Variable_name: 'Aborted_connects', Value: '0' },
  RowDataPacket {
    Variable_name: 'Aborted_connects_preauth',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_accept',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_internal',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_max_connections',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_peer_address',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_select',
    Value: '0'
  },
  RowDataPacket {
    Variable_name: 'Connection_errors_tcpwrap',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Connections', Value: '12' },
  RowDataPacket { Variable_name: 'Max_used_connections', Value: '5' },
  RowDataPacket {
    Variable_name: 'Performance_schema_session_connect_attrs_lost',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Slave_connections', Value: '0' },
  RowDataPacket { Variable_name: 'Slaves_connected', Value: '0' },
  RowDataPacket { Variable_name: 'Ssl_client_connects', Value: '0' },
  RowDataPacket {
    Variable_name: 'Ssl_connect_renegotiates',
    Value: '0'
  },
  RowDataPacket { Variable_name: 'Ssl_finished_connects', Value: '0' },
  RowDataPacket { Variable_name: 'Threads_connected', Value: '5' }
]