I just don’t understand why the below code opens a new connection/pool for every request it gets. As a result, this code generates hundreds of open connections and eventually crashes within an hour or less.
Added the error message at the bottom.
db.js
function connection() {
try {
const mysql = require('mysql2');
const config = require('../config');
const pool = mysql.createPool(config.db);
const promisePool = pool.promise();
return promisePool;
} catch (error) {
return console.log(`Could not connect - ${error}`);
}
}
const pool = connection();
module.exports = {
connection: async () => pool.getConnection(),
execute: (...params) => pool.execute(...params)
};
config.js
const config = {
db: {
host: process.env.DB_HOST,
port: process.env.DB_PORT,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
connectionLimit: 10,
waitForConnections: true,
queueLimit: 0
},
listPerPage: 10,
};
module.exports = config;
And now the file where I query the database:
const db = require('./db');
async function getByName(name) {
const sql = `SELECT name, pubkey FROM name WHERE name="${name}"`;
const [result] = await db.execute(sql);
if (result && result.length == 1) {
var resp = JSON.parse(`{
"names": {
"${result[0].name}": "${result[0].pubkey}"
}
}`);
return resp;
} else {
return {};
}
}
Thanks for your help!
======= Update 1 =======
Full code can be found here:
https://github.com/sebastiansieber/nostr-nip05-verification
(Don’t worry it’s a small repository, you’ll find above files easily)
======= Update 2 =======
This is the error message I receive when the application exits due to too many connections
Error: Too many connections
at Packet.asError (/usr/src/app/node_modules/mysql2/lib/packets/packet.js:728:17)
at ClientHandshake.execute (/usr/src/app/node_modules/mysql2/lib/commands/command.js:29:26)
at PoolConnection.handlePacket (/usr/src/app/node_modules/mysql2/lib/connection.js:487:32)
at PacketParser.onPacket (/usr/src/app/node_modules/mysql2/lib/connection.js:94:12)
at PacketParser.executeStart (/usr/src/app/node_modules/mysql2/lib/packet_parser.js:75:16)
at Socket.<anonymous> (/usr/src/app/node_modules/mysql2/lib/connection.js:101:25)
at Socket.emit (node:events:394:28)
at addChunk (node:internal/streams/readable:312:12)
at readableAddChunk (node:internal/streams/readable:287:9)
at Socket.Readable.push (node:internal/streams/readable:226:10) {
code: 'ER_CON_COUNT_ERROR',
errno: 1040,
sqlState: '',
sqlMessage: 'Too many connections',
======= Update 3 =======
As requested please see how the process list looks like:
3
Answers
Okay the issue was a misconfigured MariaDB instead of an issue with the code.
The following question / answer pointed me in the right direction: MySql Proccesslist filled with "Sleep" Entries leading to "Too many Connections"?
So I ran an SQL command to check what the
wait_timeout
is:As you can see the timeout is
28800
seconds, so it will keep them sleeping for 28800 seconds before closing them. Before the connections are closed the database reaches its limit ofmax_connections
of 500 and then the application crashes.The solution was to change the configuration of MariaDB by changing the
wait_timeout=120
which closes sleeping connections after 120 seconds.Hope this helps someone else.
I ran your script locally and bombarded it with requests – it ran just fine, complied with the connections limit and never spawned more than 10 connections.
At this point, I’m convinced that you’re not facing a MySQL leak, but rather what I would call an "execution" leak – based on my observations, you’re either spawning the project process multiple times or the connections come from elsewhere.
How are you running the script? Are you using docker?
If so, stop all your containers, run the script directly on your machine (using
node
directly) and see if the problem keeps happening.Before doing so you may want to refer to Ensuring that only a single instance of a nodejs application is running.
Your code is not leaking, therefore something else must be causing this.
You would not have all the SLEEP’ing processes, if you would follow this URL – http://www.vogella.com/tutorials/MySQLJava/article.html
to get your connection closed in a timely manner. This would also release resources and avoid looking like a memory leak.