Windows更新后Next.js+Node.js+MySQL连接关闭状态报错修复求助

Windows系统更新后，项目抛出错误：Error: Can't add new command when connection is in closed state at Connection._addCommandClosedState，更新前运行正常。相关代码文件如下：

db.js（原数据库配置） #

import mysql from "mysql2"

let connect = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "food"
});

connect.connect((err) => {
    if(err) {
        console.log("Connection error ", + JSON.stringify(err, undefined, 2));
    }
});

export default connect;

restourantController.js（原控制器） #

import connect from "../core/db.js"
import fs from "fs"
class RestourantController {
async search(req, res) {
        try {
            const city = req.params.city;
            let cityCheck = await findCity(city);
            function findCity(city) {
                return new Promise((res, rej) => {
                    connect.query(`select * from restourant where city = '${city}'`, (err, rows) => {
                        if(err) { console.log(err); }
                        else {
                            res(rows);
                        }
                    });
                })
            }
            if(cityCheck) {
                res.json(cityCheck);
            }
        }
        catch(err) {
            console.log(err);
            res.status(500).json({message: "Somathing went wrong"});
        }
    }
}

export const Restourant = new RestourantController();

修复方案 #

问题根源在于你使用了单个全局MySQL连接，系统更新可能导致连接中断，而mysql2的单个连接不会自动重连，闲置过久也会被数据库端主动关闭。改用连接池是最可靠的解决方式：

1. 修改数据库配置为连接池
   替换db.js内容，使用mysql2/promise的连接池自动管理连接生命周期：

import mysql from "mysql2/promise";

// 创建连接池，自动处理连接的创建、复用与重连
const pool = mysql.createPool({
  host: "localhost",
  user: "root",
  password: "",
  database: "food",
  connectionLimit: 10, // 最大连接数，按需调整
  waitForConnections: true,
  queueLimit: 0
});

export default pool;

2. 更新控制器的查询逻辑
   简化代码，直接用连接池的Promise方法执行查询，同时修复SQL注入风险和拼写错误：

import pool from "../core/db.js";
import fs from "fs";

class RestourantController {
  async search(req, res) {
    try {
      const city = req.params.city;
      // 使用参数化查询避免SQL注入，同时直接获取结果
      const [rows] = await pool.query("select * from restourant where city = ?", [city]);
      res.json(rows);
    } catch (err) {
      console.log(err);
      res.status(500).json({ message: "Something went wrong" });
    }
  }
}

export const Restourant = new RestourantController();

3. 关键优化说明

• 连接池会自动检测连接状态，断开时自动重建，解决更新后连接失效的问题
• 改用mysql2/promise版本，配合async/await简化异步逻辑，无需手动封装Promise
• 用参数化查询（?占位符）替代字符串拼接，彻底避免SQL注入风险
• 修正了原代码中Somathing的拼写错误

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