NodeJS用node-mysql执行MySQL长查询遇10分钟连接断开求助

• 检查并调整MySQL服务器会话级超时参数
  MySQL服务器的wait_timeout（非交互式连接闲置超时）和net_write_timeout（服务器向客户端写数据的超时）可能被设为10分钟（600秒），导致服务器主动关闭连接。可在获取连接后，先执行会话级参数修改覆盖全局设置：

  pool.getConnection((err, connection) => {
    if (err) throw err;
    // 设置会话级超时为30分钟（1800秒），可根据实际查询时长调整
    connection.query('SET SESSION wait_timeout = 1800, net_write_timeout = 1800', (err) => {
      if (err) {
        connection.release();
        throw err;
      }
      // 执行长审计查询
      connection.query('SELECT * FROM large_table FOR AUDIT', (err, results) => {
        connection.release();
        if (err) throw err;
        // 处理查询结果
      });
    });
  });
  

• 修正连接池的连接级配置参数
  当前配置中的timeout是连接池级超时，而非单个连接的超时。需在连接池的connectionConfig中设置连接级超时，并启用keepAlive维持连接活跃：

  const pool = mysql.createPool({
    host: 'your-host',
    user: 'your-user',
    password: 'your-pass',
    database: 'your-db',
    connectionLimit: 500,
    debug: false,
    // 连接池级超时配置
    acquireTimeout: 5 * 60 * 1000,
    connectTimeout: 5 * 60 * 1000,
    // 单个连接的专属配置
    connectionConfig: {
      // 设置连接超时为30分钟
      timeout: 30 * 60 * 1000,
      // 启用TCP keepalive，防止中间设备断开连接
      keepAlive: true,
      keepAliveInitialDelay: 30000 // 30秒后开始发送心跳包
    }
  });
  

• 切换到mysql2包（推荐）
  原mysql包已停止维护，mysql2对长查询、流式处理支持更完善，超时配置更直观。先安装依赖：

  npm install mysql2
  

  连接池配置及查询示例：

  const mysql = require('mysql2/promise');
  const pool = mysql.createPool({
    host: 'your-host',
    user: 'your-user',
    password: 'your-pass',
    database: 'your-db',
    connectionLimit: 500,
    // 初始化时自动设置会话级超时
    initSql: 'SET SESSION wait_timeout = 1800, net_write_timeout = 1800',
    // 连接超时设置
    connectTimeout: 30 * 60 * 1000,
    // 启用连接心跳
    enableKeepAlive: true,
    keepAliveInitialDelay: 30000
  });
  
  // 使用async/await执行长查询
  async function runAuditQuery() {
    const connection = await pool.getConnection();
    try {
      const [results] = await connection.query('SELECT * FROM large_table FOR AUDIT');
      // 处理查询结果
    } finally {
      connection.release();
    }
  }
  

• 优化查询结果处理，避免客户端阻塞
  若查询返回大量数据，一次性加载所有结果可能导致内存溢出或处理缓慢，触发服务器net_write_timeout。可使用流式处理逐行读取结果：

  // 基于mysql2的流式查询示例
  const connection = await pool.getConnection();
  const query = connection.query('SELECT * FROM large_table FOR AUDIT');
  
  query.on('result', (row) => {
    // 逐行处理数据，避免一次性加载全部
    processRow(row);
    // 按需暂停流，防止内存压力过大
    query.pause();
    setTimeout(() => query.resume(), 10);
  });
  
  query.on('end', () => {
    connection.release();
    console.log('审计查询完成');
  });
  
  query.on('error', (err) => {
    connection.release();
    throw err;
  });
  

内容的提问来源于stack exchange，提问作者Amir Saleem