Node.js MySQL连接问题：长连断开/销毁后无法重连及连接策略咨询

Hey there! Let's tackle your MySQL connection problems step by step, and also cover the best approach for high-request scenarios.

First, Let's Understand Your Current Problems #

1. Long-lived connections getting disconnected: MySQL servers have a default wait_timeout (usually around 8 hours, but some configurations set it shorter) that closes idle connections. If your app's connection sits idle beyond this time, the server will drop it.
2. Can't reconnect after calling destroy()/end(): Your current code creates a single connection in the class constructor. Once you close that connection, the this.con reference is still pointing to the closed connection object, and there's no logic to reinitialize it.
3. Question about per-request connections: Spinning up and closing a new connection for every request is a terrible idea—connection creation is resource-heavy, and you'll quickly hit MySQL's connection limit or see massive performance drops with high traffic.

Solutions to Fix Reconnection & Connection Management #

1. Use MySQL Connection Pool (Recommended for All Scenarios) #

Connection pools are designed to handle exactly these problems. They manage a pool of reusable connections, automatically handling idle timeouts, reconnections, and distributing connections across requests. Here's how to refactor your code:

const mysql = require('mysql');

module.exports = class DataAccessLayer {
  constructor() {
    // Create a connection pool instead of a single connection
    this.pool = mysql.createPool({
      host: "host.com",
      user: "asdfg",
      password: "zxcvb",
      database: "DB_example",
      // Optional pool settings to tune based on your traffic
      connectionLimit: 10, // Max number of concurrent connections
      waitForConnections: true, // Wait for a connection if pool is full
      queueLimit: 0 // Unlimited queue for connection requests
    });

    // Optional: Log pool connection status
    this.pool.on('connection', (connection) => {
      console.log(`Connected to DB (connection ID: ${connection.threadId})`);
    });

    this.pool.on('error', (err) => {
      console.error('Pool error:', err);
      // Pool automatically handles reconnections on most errors
    });
  }

  getUserBySenderId(senderId) {
    // Get a connection from the pool
    this.pool.getConnection((err, connection) => {
      if (err) throw err;

      // Use parameterized queries to PREVENT SQL INJECTION (critical fix!)
      const query = `
        SELECT sender_id, first_name, last_name, creation_date
        FROM USER
        WHERE sender_id = ?
      `;

      connection.query(query, [senderId], (err, result, fields) => {
        // Always release the connection back to the pool when done
        connection.release();

        if (err) throw err;
        console.log(result);
      });
    });
  }
};

Key Improvements in This Code: #

• Connection Pooling: The pool manages connections automatically—reusing idle ones, creating new ones when needed, and handling reconnections if a connection drops.
• SQL Injection Protection: Using ? placeholders and passing values as an array eliminates the risk of SQL injection (your original code was vulnerable to this!).
• No Manual Reconnection Logic: The pool handles reconnections behind the scenes, so you don't have to write custom error handlers for dropped connections.

2. If You Must Use a Single Connection (Not Recommended) #

If for some reason you need a single long-lived connection, add automatic reconnection logic by listening to the connection's error event:

const mysql = require('mysql');

module.exports = class DataAccessLayer {
  constructor() {
    this.createConnection();
  }

  createConnection() {
    this.con = mysql.createConnection({
      host: "host.com",
      user: "asdfg",
      password: "zxcvb",
      database: "DB_example"
    });

    this.con.connect((err) => {
      if (err) {
        console.error('Connection failed, retrying in 3 seconds...', err);
        setTimeout(() => this.createConnection(), 3000);
        return;
      }
      console.log("Connected to DB");
    });

    // Handle connection errors to trigger reconnection
    this.con.on('error', (err) => {
      console.error('Connection error:', err);
      if (err.code === 'PROTOCOL_CONNECTION_LOST' || err.code === 'ECONNRESET') {
        console.log('Reconnecting to DB...');
        this.createConnection();
      } else {
        throw err;
      }
    });
  }

  getUserBySenderId(senderId) {
    // Use parameterized queries here too!
    const query = `
      SELECT sender_id, first_name, last_name, creation_date
      FROM USER
      WHERE sender_id = ?
    `;
    this.con.query(query, [senderId], (err, result, fields) => {
      if (err) throw err;
      console.log(result);
    });
  }
};

Final Notes on High Traffic Scenarios #

• Never use per-request connections: The overhead of creating and tearing down connections for every request will cripple your app's performance and can lead to MySQL hitting its max connection limit.
• Tune your pool settings: Adjust connectionLimit based on your MySQL server's max allowed connections (check max_connections in MySQL config) and your app's traffic. Start with a reasonable number (like 10-20) and adjust based on performance monitoring.
• Always use parameterized queries: This is non-negotiable—it protects your database from malicious attacks and avoids syntax errors from unescaped values.

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