NodeJS Mysql 连接器："池已关闭"

Question

我们有一个NodeJS应用程序，它作为API为我们正在构建的前端Angular应用程序提供服务。

到目前为止，这两个应用程序都运行良好，但是偶尔出现API出现奇怪错误：“Pool is closed”。现在，我正在处理需要执行大量操作（如6-10个顺序插入和数据库选择）的部分，这个错误总是或几乎总是发生，这成为了我们必须解决的重大问题。

这是我们在DAO中使用的Mysqlconnector类：

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);
            });
        });
    }
}

我所使用的"mysql"包版本是：

"mysql": "^2.15.0",

当我执行先前提到的特定操作时，会出现大量此类错误：

Error: 连接池已关闭。 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' }

这是发生错误的代码片段，但正如我之前所说，它有时会在API的其他部分任意出现：

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;
        }
    }
}

可能是什么原因导致这种情况？谢谢！

- Zerok

---

我有过类似的经历，但是我多次结束了池。 - Aditya_Anand

你们有没有更新修复这个问题的进展？ - Vivek Molkar

我也遇到了同样的问题。有任何更新吗？ - Saurabh Gangamwar

Answer 1

来自Github问题：https://github.com/mysqljs/mysql/issues/1803

"Error: Pool is closed." 不是一个查询错误，而是一种“您正在尝试关闭已经关闭的池”的错误类型。当您调用.end()时，应该以某种方式将池从“活动”状态中移除。例如：

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

- Gaurav