Node.js MySQL连接池长轮询遭遇ETIMEDOUT与PROTOCOL_CONNECTION_LOST问题

针对你基于PM2运行的7×24小时服务，频繁执行轮询SELECT和批量INSERT时出现的连接问题，结合你的代码给出以下具体修复和优化方案：

一、修复重试逻辑的致命bug #

你的代码里有两个直接导致重试机制失效的问题：

1. 未定义错误变量：最大重试次数判断时引用了未定义的e，会触发ReferenceError
2. 递归参数顺序错误：重试调用时未传入errorDelay，导致等待时间被错误覆盖为重试次数（1、2、3毫秒），根本起不到等待恢复的作用

修改后的_execute函数：

const _execute = async (query, params, errorDelay, attempts, lastError) => {
    if (attempts >= 5) {
        logger.error(`Error while performing query: ${lastError.message}`, null, lastError);
        throw new Error('Failed to execute query after 5 attempts');
    }

    let connection;
    const pool = await getInstance();

    try {
        connection = await pool.getConnection();
        // 先检测连接是否有效
        await connection.query('SELECT 1');
        return await connection.query(query, params);
    } catch (e) {
        console.error(e);
        const timedOut = e.code === "ETIMEDOUT" || e.code === "PROTOCOL_CONNECTION_LOST";

        logger.error(`Error while performing query: ${e.message}${timedOut ? ", retrying..." : ''}`, null, e);

        if (timedOut) {
            await new Promise(resolve => setTimeout(resolve, errorDelay));
            // 修正参数顺序，传入当前错误作为lastError
            return _execute(query, params, errorDelay, attempts + 1, e);
        }
        throw e;
    } finally {
        if (connection) {
            try {
                connection.release();
            } catch (releaseError) {
                logger.error(`Error while releasing connection: ${releaseError.message}`, null, releaseError);
            }
        }
    }
}

二、优化连接池核心配置 #

连接超时/丢失的核心原因往往是连接池配置与MySQL服务器参数不匹配，调整以下关键参数：

pool = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASS || "",
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    // 需小于MySQL服务器的wait_timeout（先执行SHOW VARIABLES LIKE 'wait_timeout'查看）
    // 默认服务器wait_timeout为8小时（28800秒），这里设为8小时减100秒避免冲突
    idleTimeout: 28700000,
    // 开启心跳维持连接活性，防止服务器主动断开空闲连接
    enableKeepAlive: true,
    keepAliveInitialDelay: 30000, // 30秒后开始发送心跳包
    // 延长连接建立超时时间，应对网络波动
    connectTimeout: 10000,
    // 设置查询超时，避免长查询阻塞连接
    queryTimeout: 5000,
});

三、重构长轮询逻辑 #

每秒轮询+每次获取新连接的模式会过度消耗连接池资源，改为复用单连接的方式：

// 单独维护轮询专用的长连接
let pollConnection;

async function getPollConnection() {
    if (pollConnection) {
        try {
            // 检测连接是否存活
            await pollConnection.query('SELECT 1');
            return pollConnection;
        } catch (e) {
            // 连接失效则销毁并重置
            await pollConnection.end();
            pollConnection = null;
        }
    }
    const pool = await getInstance();
    pollConnection = await pool.getConnection();
    return pollConnection;
}

// 轮询函数示例
async function runPoll() {
    try {
        const conn = await getPollConnection();
        const [rows] = await conn.query('SELECT * FROM your_target_table WHERE your_condition');
        // 处理查询结果
    } catch (e) {
        logger.error('Poll failed:', e);
        pollConnection = null; // 重置连接，下次重新获取
    }
}

// 启动轮询
setInterval(runPoll, 1000);

此方式避免了频繁从连接池获取/释放连接，减少资源开销和连接失效概率。

四、优化批量INSERT操作 #

频繁批量插入会增加连接负载，通过以下方式优化：

1. 合并插入语句：将多条INSERT合并为单条语句，减少查询次数
2. 使用事务包裹：减少提交次数，提升性能并保证数据一致性
3. 控制批量大小：避免单次插入过多数据导致连接长时间占用

示例代码：

async function batchInsertData(dataList) {
    const conn = await getInstance().getConnection();
    try {
        await conn.beginTransaction();
        // 合并为单条插入语句
        const sql = 'INSERT INTO your_table (col1, col2, col3) VALUES ?';
        // 转换为二维数组格式
        const values = dataList.map(item => [item.col1, item.col2, item.col3]);
        await conn.query(sql, [values]);
        await conn.commit();
    } catch (e) {
        await conn.rollback();
        throw e;
    } finally {
        conn.release();
    }
}

内容的提问来源于stack exchange，提问作者Gabriele Midulla