Why Does My Node.js App Get 'Connection Lost: The server closed the connection' Errors When Using MySQL, and How Can I Fix It?

Connection Lost: Troubleshooting MySQL Disconnects in Node.js

Node.js users may encounter the "Connection lost: The server closed the connection" error when utilizing the mysql module. This issue arises when the server abruptly terminates the TCP connection, often within a specific time range (e.g., between midnight and 2 AM).

Cause

The error indicates that the connection between Node.js and the MySQL server has been severed by the server, typically due to an unexpected disconnect or server restart.

Solution

To address this problem, one can employ a connection handling mechanism that detects and re-establishes lost connections automatically. Here's an example:

var db_config = {
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'example'
};

var connection;

function handleDisconnect() {
  connection = mysql.createConnection(db_config);

  connection.connect(function(err) {
    if (err) {
      console.log('error when connecting to db:', err);
      setTimeout(handleDisconnect, 2000);
    }
  });

  connection.on('error', function(err) {
    console.log('db error', err);
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
      handleDisconnect();
    } else {
      throw err;
    }
  });
}

handleDisconnect();

Explanation

In this solution:

1. The db_config object specifies the database connection parameters.
2. The handleDisconnect function is invoked to create a new connection and attach an error handler to it.
3. If the connection is lost due to a server restart or idle timeout, the error handler detects it and calls the handleDisconnect function again, which recreates the connection.
4. The setTimeout function prevents excessive reconnection attempts by introducing a delay before retrying.

By implementing this connection handling mechanism, you can ensure that your application can gracefully handle temporary connection losses and maintain a reliable connection to the MySQL database, even during server restarts or maintenance periods.

The above is the detailed content of Why Does My Node.js App Get 'Connection Lost: The server closed the connection' Errors When Using MySQL, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!