NodeJS Mysql connector: "Pool is closed"

Question

We've got a NodeJS app that works as an API for a frontend, Angular application that we're also building.

This far, both things have been working well, but from time to time, the API gives problems with a weird error: "Pool is closed". Now that I'm working in a part in which a lot of operations need to be performed (like, 6-10 sequential insertions and selects in the database), this error happens always or almost always, which makes it a big problem we have to solve.

This is our Mysqlconnector class that we use in the DAOs:

import { environment } from "../environments/environment";

var mysql = require('mysql')

export class MysqlConnector {
    static pool: any;
    static host: any;
    static database: any;

    constructor() {

    }

    public static connect() {
        if (!MysqlConnector.pool) {
            MysqlConnector.pool = mysql.createPool({
                connectionLimit: 10,
                host: MysqlConnector.host,
                user: environment.mysql.data.user,
                password: environment.mysql.data.password,
                database: MysqlConnector.database
            });
        }
      //  console.log("pool: ", MysqlConnector.pool)
        return MysqlConnector.pool;
    }

    /**
     * This method can be used to directly perform a SQL query to the monitoring DB.
     * @param query SQL query string
     */
    public static async monitoringDatabaseQuery(query) {
        return new Promise((resolve, reject) => {
            const connection = mysql.createPool({
                connectionLimit: 10,
                host: 'our-host',
                user: environment.mysql.data.user,
                password: environment.mysql.data.password,
                database: 'monitoring'
            });

            connection.query(query, (err, data) => {
                if (err) reject(err);
                connection.end();
                resolve(data);
            });
        })
    }

    public static setupDB(req, res, next) {
       // method that we use for picking up the correct DB at API call init
    }

    public static end() {
        const pool = MysqlConnector.connect();
        pool.end();
        MysqlConnector.pool = null;
    }

    public static query(queryString: string) {
        return new Promise((resolve, reject) => {
            const pool = MysqlConnector.connect();

            pool.query(queryString, (err, result) => {
                if (err) reject(err);
                resolve();
            });
        });
    }

    public static select(queryString: string) {
        return new Promise<any[]>((resolve, reject) => {
            const pool = MysqlConnector.connect();

            pool.query(queryString, (err, result, fields) => {
                if (err) reject(err);
                resolve(result);
            });
        });
    }
}

My version of the "mysql" package:

"mysql": "^2.15.0",

I get a bunch of errors just like this when I perform that specific operation I previously talked you about :

Error: Pool is closed. at Handshake.onConnect [as _callback] (C:\Zerok\Dev\WorkProjects\api\node_modules\mysql\lib\Pool.js:52:15) at Handshake.Sequence.end (C:\Zerok\Dev\WorkProjects\api\node_modules\mysql\lib\protocol\sequences\Sequence.js:88:24) at C:\Zerok\Dev\WorkProjects\api\node_modules\mysql\lib\protocol\Protocol.js:398:18 at Array.forEach () at C:\Zerok\Dev\WorkProjects\api\node_modules\mysql\lib\protocol\Protocol.js:397:13 at _combinedTickCallback (internal/process/next_tick.js:131:7) at process._tickCallback (internal/process/next_tick.js:180:9) -------------------- at Pool.query (C:\Zerok\Dev\WorkProjects\api\node_modules\mysql\lib\Pool.js:199:23) at Promise (C:\Zerok\Dev\WorkProjects\api\dist\lib\MysqlConnector.js:91:18) at new Promise () at Function.query (C:\Zerok\Dev\WorkProjects\api\dist\lib\MysqlConnector.js:89:16) at Function. (C:\Zerok\Dev\WorkProjects\api\dist\dao\EventDAO.js:146:63) at Generator.next () at C:\Zerok\Dev\WorkProjects\api\dist\dao\EventDAO.js:7:71 at new Promise () at __awaiter (C:\Zerok\Dev\WorkProjects\api\dist\dao\EventDAO.js:3:12) at selectedDimensions.forEach (C:\Zerok\Dev\WorkProjects\api\dist\dao\EventDAO.js:143:65) code: 'POOL_CLOSED' }

This is the piece of code where it happens, but as I said before, it sometimes happens in other parts of the API, totally arbitrarily:

private static async changeEffectDimensions(dimensionsList, eventEffectID) {
    if (dimensionsList.length > 0) {
        try {
            const dimensionsModel = new Dimensions();
            const dimensions = await dimensionsModel.getAll();
            const selectedDimensions = dimensionsModel.filterDimensions(dimensions, dimensionsList);
            let sql;

            await selectedDimensions.forEach(async dimension => {
                sql = `INSERT IGNORE INTO EffectDimensions (event_effect_id, dimension_id) VALUES (${eventEffectID}, ${dimension.dimension_id})`;
                await MysqlConnector.query(sql);
            });
            return true;
        } catch (err) {
            console.log("ERROR: ", err)
            throw err;
        }
    }
}

What may be causing this? Thank you!

Answer 1

From github issues: https://github.com/mysqljs/mysql/issues/1803

"Error: Pool is closed." is not an error from one of the queries, it's "you are trying to close pool that is already closed" type of error. When you call .end() you should somehow remove your pool from 'active' state. For example:

function refreshPool() {
   const poolToClose = pool;
   pool = null;
   if (poolToClose) {
     poolToClose.end();
   }
   pool = mysql.createPool(config);
}