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
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.
idk how to created docs or tutorial mostly informative one . but this are the code i have
connectionClass.ts
db.ts
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
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.
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.