Why Does My Node.js MySQL Connection Keep Dropping Around Midnight?

MySQL Connection Lost: Solving the "Connection lost: The server closed the connection" Error

When working with Node.js and MySQL, you may occasionally encounter an error indicating that the server closed the connection. This usually occurs around 12:00 to 2:00 AM, leaving you puzzled. The error message often appears as:

Error: Connection lost: The server closed the connection.
at Protocol.end (/opt/node-v0.10.20-linux-x64/IM/node_modules/mysql/lib/protocol/Protocol.js:73:13)
at Socket.onend (stream.js:79:10)
at Socket.EventEmitter.emit (events.js:117:20)
at _stream_readable.js:920:16
at process._tickCallback (node.js:415:13)

Despite attempting the recommended solution, the issue persists. Here's a more comprehensive approach to handle this error effectively:

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

var connection;

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

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

  connection.on('error', function(err) {
    console.log('db error', err);
    if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection lost due to server restart or idle timeout
      handleDisconnect();
    } else {
      throw err;
    }
  });
}

handleDisconnect();

This code does the following:

1. Recreates the MySQL connection object in the handleDisconnect function.
2. Attempts to reconnect by calling connection.connect() within handleDisconnect.
3. If an error occurs while connecting, it logs the error and schedules a reconnection attempt in 2 seconds to avoid excessive reconnection attempts.
4. It establishes an 'error' event listener on the connection object to monitor any errors that may arise during the connection lifecycle.
5. When a 'PROTOCOL_CONNECTION_LOST' error is detected, signifying a lost connection due to server restart or idle timeout, it triggers the handleDisconnect function again to recreate and reconnect.

By implementing this approach, you can effectively handle MySQL connection lost errors and ensure seamless database connectivity in your Node.js applications.

The above is the detailed content of Why Does My Node.js MySQL Connection Keep Dropping Around Midnight?. For more information, please follow other related articles on the PHP Chinese website!