DB Error: connection lost: server Closed the connection

Question

I'm facing this error when the there are no requests to mysql it goes to idles state and we face this db error. I'm working with node, mysql deployed onto openshift cluster.

How do I keep the db connection alive such that the server never closes the connection? PFA Please, lemme know is there any solutions? I'm stuck for past 2 weeks

Update - Following is the code I'm using

                  `var connection;
                    function handleDisconnect() {
                    connection = mysql.createConnection({
                        host: config.db.host,
                        user: config.db.user,
                        password: config.db.password,
                        database: config.db.database,
                        port: config.db.port,
                    }); // 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();`

Answer 1

Since, you are using Node.js, you could use the connection pool.

pooling-connections

Below is a snippet from the link. Notice, connection.release(); It doesn't destroy the connection, but allows the connection to be used again.

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
});
pool.getConnection(function(err, connection) {
  if (err) throw err; // not connected!

  // Use the connection
  connection.query('SELECT something FROM sometable', function (error, results, fields) {
    // When done with the connection, release it.
    connection.release();

    // Handle error after the release.
    if (error) throw error;

    // Don't use the connection here, it has been returned to the pool.
  });
});