Node.js process cannot recover after MySQL turned off, then turn on

Question

I am using Node.js with MySQL and restify.

I have the following code which is run as part of a REST API. It works fine.

server.get('/test', function (req, res, next) {
    var query_string =
        "SELECT DATE(date_transacted) AS transaction_date, " +
        " MonthReports.tb AS MonthReports__tb " +
        " FROM monthly_reports MonthReports " +
        " WHERE ( date_transacted >= \'2015-01-00\' AND date_transacted <= \'2015-09-00\' ) ";

    connection.query(
        query_string
        , function (err, rows, fields) {
            if (err) throw err;
            res.send(rows);
        });
});

If I deliberately turn off the MySQL database and makes a REST API call which will run the query, I will get the error

Cannot enqueue Query after fatal error.

At this point, I turn on the MySQL database. The node.js process is unable to recover and the same error keeps appearing when I make a REST API call. The REST API server is dead.

What can be done to make the Node.js REST API server code recoverable?

score 3 · Answer 1 · answered Nov 11 '15 at 14:49

I am assuming you are connecting globally inside your script.

One simple way would be to create a connection per request:

server.get('/test', function (req, res, next) {
    var query_string =
        "SELECT DATE(date_transacted) AS transaction_date, " +
        " MonthReports.tb AS MonthReports__tb " +
        " FROM monthly_reports MonthReports " +
        " WHERE ( date_transacted >= \'2015-01-00\' AND date_transacted <= \'2015-09-00\' ) ";



    var connection = getConnection(function connected(err) {
        if (err) {
           // error connecting  to mysql! alert user
        } else {
          connection.query(
            query_string
            , function (err, rows, fields) {
              if (err) throw err;
              res.send(rows);
          });
        }
    });

});

The above code is psuedo code as i'm not familiar with the node mysql library. This will allow each request to see if mysql is able to be connected to, at the expense of having a connection per web request.

Another strategy could be to check err when you issue a query, and if there is an error try to reestablish the global connection

server.get('/test', function (req, res, next) {
    var query_string =
        "SELECT DATE(date_transacted) AS transaction_date, " +
        " MonthReports.tb AS MonthReports__tb " +
        " FROM monthly_reports MonthReports " +
        " WHERE ( date_transacted >= \'2015-01-00\' AND date_transacted <= \'2015-09-00\' ) ";

    connection.query(
        query_string
        , function (err, rows, fields) {
            if (err) {
              // Try to reconnect here instead of throwing error and stopping node process, and reissue query
            }
            res.send(rows);
        });
});

score 0 · Answer 2 · answered Nov 18 '15 at 06:17

This website gives a complete answer. Credit goes to the writer of this article, not me.

https://www.exratione.com/2013/01/nodejs-connections-will-end-close-and-otherwise-blow-up/

/**
 * @fileOverview A simple example module that exposes a getClient function.
 *
 * The client is replaced if it is disconnected.
 */

var mysql = require("mysql");

var client = mysql.createConnection({
  host: "127.0.0.1",
  database: "mydb",
  user: "username",
  password: "password"
});

/**
 * Setup a client to automatically replace itself if it is disconnected.
 *
 * @param {Connection} client
 *   A MySQL connection instance.
 */
function replaceClientOnDisconnect(client) {
  client.on("error", function (err) {
    if (!err.fatal) {
      return;
    }

    if (err.code !== "PROTOCOL_CONNECTION_LOST") {
      throw err;
    }

    // client.config is actually a ConnectionConfig instance, not the original
    // configuration. For most situations this is fine, but if you are doing
    // something more advanced with your connection configuration, then
    // you should check carefully as to whether this is actually going to do
    // what you think it should do.
    client = mysql.createConnection(client.config);
    replaceClientOnDisconnect(client);
    client.connect(function (error) {
      if (error) {
        // Well, we tried. The database has probably fallen over.
        // That's fairly fatal for most applications, so we might as
        // call it a day and go home.
        //
        // For a real application something more sophisticated is
        // probably required here.
        process.exit(1);
      }
    });
  });
}

// And run this on every connection as soon as it is created.
replaceClientOnDisconnect(client);

/**
 * Every operation requiring a client should call this function, and not
 * hold on to the resulting client reference.
 *
 * @return {Connection}
 */
exports.getClient = function () {
  return client;
};

score 0 · Answer 3 · edited May 23 '17 at 12:01

This answer was extracted from another link nodejs mysql Error: Connection lost The server closed the connection

The extracted code;

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();

Node.js process cannot recover after MySQL turned off, then turn on

3 Answers3