why is my Node/Express application leaking MySQL connections?

Question

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:

Answer 1

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 of max_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.

Answer 2

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.