Next.js本地开发环境MySQL连接报错：Packets out of order求助

问题现象 #

本地开发环境多次运行应用后，出现报错：

Error connecting to database: Error: Packets out of order. Got: 1 Expected: 0

完整错误栈：

Error connecting to database: Error: Packets out of order. Got: 1 Expected: 0
    at Parser._tryReadPacketHeader (Parser.js:470:15)
    at Parser.write (Parser.js:33:29)
    at Protocol.write (Protocol.js:38:16)
    at Socket.<anonymous> (Connection.js:88:28)
    at Socket.<anonymous> (Connection.js:526:10)
    at Socket.emit (node:events:527:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    --------------------
    at Protocol._enqueue (Protocol.js:144:48)
    at Protocol.handshake (Protocol.js:51:23)
    at PoolConnection.connect (Connection.js:116:18)
    at Pool.getConnection (Pool.js:48:16)
    at eval (db.js:20:12)
    at Object.(api)/./src/utils/db.js (getAllMonthlySession.js:42:1)
    at __webpack_require__ (webpack-api-runtime.js:33:42)
    at eval (getAllMonthlySession.js:5:67)
    at Object.(api)/./src/pages/api/customer_panel/appointment/getAllMonthlySession.js (getAllMonthlySession.js:32:1)
    at __webpack_require__ (webpack-api-runtime.js:33:42)
    at __webpack_exec__ (getAllMonthlySession.js:52:39)
    at getAllMonthlySession.js:53:28
    at Object.<anonymous> (getAllMonthlySession.js:56:3)
    at Module._compile (node:internal/modules/cjs/loader:1105:14)
    at Object.Module._extensions..js (node:internal/modules/cjs/loader:1159:10)
    at Module.load (node:internal/modules/cjs/loader:981:32)
    at async DevServer.runImpl (base-server.js:432:29)
    at async DevServer.run (next-dev-server.js:814:20)
    at async DevServer.handleRequestImpl (base-server.js:375:20)
    at async base-server.js:157:99 {
  code: 'PROTOCOL_PACKETS_OUT_OF_ORDER',
  fatal: true
}

当前使用的db.js代码：

import mysql from 'mysql';

// Keeping the variable name connection instead of pool :') NVM, The deadline is tight

//Updating DB Connection:
const connection = mysql.createPool({
  connectionLimit: 10,
  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,
  multipleStatements: true,
  connectTimeout: 60000,
});

connection.getConnection((err) => {
  if (err) {
    console.error('Error connecting to database:', err);
  } else {
    console.log('Connected to database');
  }
});

export default connection;

注：构建生产环境后该错误不会出现，已尝试调整connectionLimit等配置，问题未解决。

原因分析 #

• Next.js热重载导致连接池重复初始化：本地开发时热重载会频繁重新加载模块，db.js被多个API路由引用，多次执行后会重复创建连接池，旧连接池的Socket未被正确销毁，引发数据包混乱。
• 闲置连接被MySQL服务器断开：本地应用长时间运行时，连接池中的闲置连接会被MySQL主动断开，但客户端未检测到连接失效，复用失效连接时出现数据包顺序错误。
• 旧版mysql包兼容性问题：当前使用的mysql包已停止维护，在Next.js开发环境的热重载场景下存在未修复的连接管理bug。

解决方法 #

方法1：防止连接池重复初始化 #

利用模块缓存机制，确保连接池只创建一次：

import mysql from 'mysql';

let connection;

if (!global.dbConnection) {
  global.dbConnection = mysql.createPool({
    connectionLimit: 10,
    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,
    multipleStatements: true,
    connectTimeout: 60000,
    waitForConnections: true,
    queueLimit: 0,
    acquireTimeout: 60000,
    timeout: 60000,
  });

  // 监听连接错误，自动清理失效连接
  global.dbConnection.on('error', (err) => {
    if (err.code === 'PROTOCOL_CONNECTION_LOST' || err.code === 'PROTOCOL_PACKETS_OUT_OF_ORDER') {
      console.log('Reconnecting to database due to connection error');
      delete global.dbConnection;
    }
  });

  connection = global.dbConnection;
} else {
  connection = global.dbConnection;
}

export default connection;

方法2：切换到维护中的MySQL包 #

替换已废弃的mysql包为mysql2（API完全兼容，且持续维护）：

1. 安装依赖：

npm uninstall mysql
npm install mysql2

2. 修改db.js导入：

import mysql from 'mysql2'; // 替换为mysql2

mysql2对连接池管理更稳定，能自动处理失效连接，适配Next.js热重载场景。

方法3：配置连接池健康检测 #

添加连接有效性检测参数，及时清理失效连接：

const connection = mysql.createPool({
  connectionLimit: 10,
  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,
  multipleStatements: true,
  connectTimeout: 60000,
  // 新增健康检测配置
  waitForConnections: true,
  queueLimit: 0,
  acquireTimeout: 60000,
  timeout: 60000,
  pingInterval: 30000, // 每30秒ping一次数据库
  pingTimeout: 5000,
});

方法4：临时禁用热重载 #

在启动命令中添加参数禁用热重载，避免模块重复加载（仅作临时排查）：

next dev --no-hmr

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