如何在NodeJS中维持MySQL长连接以开发Discord Bot

Hey there! Let's sort out that annoying MySQL connection disconnect issue you're facing with your Discord Bot. The root cause here is pretty straightforward: MySQL servers automatically close idle connections after a set period (typically 8 hours, controlled by the server's wait_timeout configuration). Since your current code uses a single static connection, it goes idle when the bot isn't making database queries for hours, leading to the drop.

Here are two solid solutions to keep your connection alive:

1. Use a Connection Pool (Recommended) #

The mysql package has built-in connection pooling that handles connection reuse, automatic reconnection, and idle management out of the box. This is the most reliable approach for long-running apps like Discord Bots.

Replace your current connection setup with this code:

// Initialize Variables
var config;
var mysql, pool;
var fs;

try {
    // External Packages
    fs = require('fs');
    config = require('./config.json');
    mysql = require('mysql');

    // Connection Pool Setup
    pool = mysql.createPool({
        host: config.mysql.host,
        user: config.mysql.user,
        password: config.mysql.password,
        database: config.mysql.database,
        connectionLimit: 10, // Adjust based on your bot's needs
        waitForConnections: true
    });

    // Test the pool (optional)
    pool.getConnection((err, conn) => {
        if (err) {
            console.error('Failed to get pool connection:', err);
            return;
        }
        console.log('Successfully connected to MySQL via pool');
        conn.release(); // Release the connection back to the pool
    });
} catch (e) {
    console.error(e);
}

When you need to run a query, use the pool like this:

pool.query('SELECT * FROM your_table', (err, results) => {
    if (err) throw err;
    // Handle results
});

Or if you want to hold a connection for multiple queries:

pool.getConnection((err, conn) => {
    if (err) throw err;
    conn.query('SELECT * FROM table1', (err, res1) => {
        if (err) {
            conn.release();
            throw err;
        }
        conn.query('SELECT * FROM table2', (err, res2) => {
            conn.release(); // Always release connections back to the pool!
            if (err) throw err;
            // Handle both results
        });
    });
});

2. Add Automatic Reconnection to a Single Connection #

If you prefer to stick with a single connection, you can add error handling and reconnection logic to restart the connection when it drops.

Update your existing code with these changes:

// Initialize Variables
var config;
var mysql, conn;
var fs;

function createConnection() {
    try {
        conn = mysql.createConnection({
            host: config.mysql.host,
            user: config.mysql.user,
            password: config.mysql.password,
            database: config.mysql.database
        });

        conn.connect((err) => {
            if (err) {
                console.error('Connection failed, retrying in 5 seconds:', err);
                setTimeout(createConnection, 5000); // Retry after 5 seconds
                return;
            }
            console.log('Successfully connected to MySQL');
        });

        // Handle connection errors and reconnect
        conn.on('error', (err) => {
            console.error('MySQL connection error:', err);
            if (err.code === 'PROTOCOL_CONNECTION_LOST') {
                // Connection was closed, reconnect
                createConnection();
            } else {
                throw err;
            }
        });
    } catch (e) {
        console.error(e);
        setTimeout(createConnection, 5000);
    }
}

try {
    // External Packages
    fs = require('fs');
    config = require('./config.json');
    mysql = require('mysql');

    // Start the connection
    createConnection();
} catch (e) {
    console.error(e);
}

Extra Tips #

• Always handle errors in your queries to catch connection issues early.
• Consider switching to mysql2 (a drop-in replacement for mysql) which has better promise support and more robust connection handling.
• Check your MySQL server's wait_timeout and interactive_timeout settings if you want to adjust how long idle connections stay open (but using a pool is still better than relying on this).

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