NodeJS API MySQL连接池连接未关闭导致程序挂起问题排查

Hey Glenn, great job debugging so far—you’re right to suspect connection management is the issue here! Let’s break down what’s going wrong and fix it.

The Root Cause: Unreleased Connections #

Your connection pool is configured with connectionLimit: 10, meaning it can only handle 10 concurrent connections at once. The critical mistake? You’re never releasing connections back to the pool after using them.

Every time you call connection.getConnection(), you grab a connection from the pool—but without explicitly calling tmpConn.release(), that connection stays tied to the original request forever. Once you hit 10 requests, the pool runs out of available connections. New requests will hang indefinitely waiting for a free connection, which is why your app hangs instead of crashing—it’s just stuck waiting for resources, not hitting a fatal error.

Fixing the Code: Add Connection Release #

You need to ensure the connection is released no matter what—whether the query succeeds, fails, or hits an unexpected error. Here’s how to update your route:

const express = require('express');
const router = express.Router();
const verify = require('../../verifyToken');
var config = require('../../databaseConfig');
var connection= config.connection;

router.get('/getMainPageCount/',verify,(req,res) => {
 const chemistID = req.user._id;
 connection.getConnection(function(error,tmpConn){
 if(error){
 console.log("error in connecting to MYSQL " + error);
 return res.sendStatus(500); // No connection to release here
 }else{
 var sqlString = "SET @chemistID = ?; CALL getMainPageCount(@chemistID);";
 tmpConn.query(sqlString,[chemistID], async (err,rows,fields)=>{
 try {
 // First handle query-level errors
 if (err) {
 console.log("Query execution error: ", err);
 return res.status(500).json({ Result: false, error: err.message });
 }

 const ListData = rows[1];
 if (rows && Array.isArray(rows) && rows.length) {
 res.status(200).json({ Result: true, orders: ListData });
 } else {
 res.status(500).json({ Result: false });
 }
 } catch (e) {
 console.log("Unexpected runtime error: ", e);
 res.status(500).json({ Result: false, error: e.message });
 } finally {
 // Critical: Release the connection back to the pool every time
 tmpConn.release();
 console.log("Connection returned to pool");
 }
 })
 }
 });
});

Key Changes Made: #

1. Added query error handling: Your original code ignored the err parameter from tmpConn.query()—this would have caused silent failures and left connections hanging if your SQL failed.
2. Added a finally block: This ensures tmpConn.release() runs regardless of success or failure, guaranteeing connections are always returned to the pool for reuse.

Why Your App Hangs Instead of Crashing #

Node.js doesn’t crash here because there’s no uncaught fatal error—it’s just waiting for the connection pool to free up resources. Since connections are never released, the wait is infinite until you restart the app (which resets the pool).

Bonus: Upgrade to Promise-Based MySQL (Easier to Manage) #

As a beginner, you might find promise-based MySQL libraries like mysql2/promise easier to work with. They let you use async/await instead of nested callbacks, making connection management and error handling more intuitive. Here’s a quick example:

// First install mysql2: npm install mysql2
const mysql = require('mysql2/promise');

// Update your database config to use mysql2/promise
const pool = mysql.createPool({
 host:'localhost',
 user: 'abc',
 password: 'abc',
 database : 'abc',
 multipleStatements: true,
 connectionLimit: 10
});

// Updated route with async/await
router.get('/getMainPageCount/', verify, async (req, res) => {
 const chemistID = req.user._id;
 let connection;
 try {
 // Get a connection from the pool
 connection = await pool.getConnection();
 const [rows] = await connection.execute("SET @chemistID = ?; CALL getMainPageCount(@chemistID);", [chemistID]);
 const ListData = rows[1];
 if (rows && Array.isArray(rows) && rows.length) {
 res.status(200).json({ Result: true, orders: ListData });
 } else {
 res.status(500).json({ Result: false });
 }
 } catch (err) {
 console.log("Error: ", err);
 res.status(500).json({ Result: false, error: err.message });
 } finally {
 // Release connection only if it was successfully acquired
 if (connection) connection.release();
 }
});

This approach eliminates callback nesting and makes it harder to forget releasing connections, since the cleanup logic is front-and-center in the finally block.

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