Node.js handling mysql disconnects.

You may lose the connection to a MySQL server due to network problems, the server timing you out, the server being restarted, or crashing. All of these events are considered fatal errors, and will have the err.code = 'PROTOCOL_CONNECTION_LOST'. See the Error Handling section for more information.

A good way to handle such unexpected disconnects is shown below:

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

var connection;

function handleDisconnect() {
  connection = mysql.createConnection(db_config); // Recreate the connection, since
                                                  // the old one cannot be reused.

  connection.connect(function(err) {              // The server is either down
    if(err) {                                     // or restarting (takes a while sometimes).
      console.log('error when connecting to db:', err);
      setTimeout(handleDisconnect, 2000); // We introduce a delay before attempting to reconnect,
    }                                     // to avoid a hot loop, and to allow our node script to
  });                                     // process asynchronous requests in the meantime.
                                          // If you're also serving http, display a 503 error.
  connection.on('error', function(err) {
    console.log('db error', err);
    if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually
      handleDisconnect();                         // lost due to either server restart, or a
    } else {                                      // connnection idle timeout (the wait_timeout
       throw err;                                 // server variable configures this)
    });
}

handleDisconnect();

As you can see in the example above, re-connecting a connection is done by establishing a new connection. Once terminated, an existing connection object cannot be re-connected by design.

With Pool, disconnected connections will be removed from the pool freeing up space for a new connection to be created on the next getConnection call.

2 Replies to “Node.js handling mysql disconnects.”

Rob says:

November 13, 2020 at 1:34 pm

Could you please broaden the audience for this lovely article by explaining which file the code goes in, when it’s executed, and where in the chain of execution it’s called when there’s an error?

1. admin says:
  
  February 14, 2021 at 7:43 am
  
  Hi Rob,
  Thanks for reading…
  
  This code would be placed in the connection generation file, depends on the structure of the code, different code bases might hide this away in a class or just have it in the model section.
  
  The code just keeps retrying to connect each time a “PROTOCOL_CONNECTION_LOST” event is triggered or the server is down.
  
  re. “chain of execution it’s called when there’s an error?” not sure what you mean..
  
  Thanks
  
  😉

2 Replies to “Node.js handling mysql disconnects.”

Leave a Reply Cancel reply