mysql connection lost error nodejs

Question

I am connecting my node to mysql using the below code for all my rest apis which i am using in my project; i have put this as a common db connecting file for all my query request.

var mysql = require('mysql');
var db_connect = (function () {
    function db_connect() {
        mysqlConnConfig = {
            host: "localhost",
            user: "username",
            password: "password",
            database: "db_name"
        };
    }
    db_connect.prototype.unitOfWork = function (sql) {
    mysqlConn = mysql.createConnection(mysqlConnConfig);
        try {
            sql(mysqlConn);
        } catch (ex) {

            console.error(ex);
        } finally {
            mysqlConn.end();
        }
    };
    return db_connect;
})();
exports.db_connect = db_connect;

The above code works fine and i will use my query for execution with the 'sql' as below in all of my rest api as below.

var query1 = "SELECT * FROM table1";
 sql.query(query1,function(error,response){
if(error){
    console.log(error);
}
else{
    console.log(response);
    }
 })

everything goes good till now but the problem is i am getting the sql protocol connection error after 8-12 hours of running my forever module

forever start app.js

i am starting my project with the above forever module. after 8-12 hours i am getting the below error and all my rest api are not working or going down.

"stack": ["Error: Connection lost: The server closed the connection.", "    at Protocol.end (/path/to/my/file/node_modules/mysql/lib/protocol/Protocol.js:109:13)", "    at Socket.<anonymous> (/path/to/my/file/node_modules/mysql/lib/Connection.js:102:28)", "    at emitNone (events.js:72:20)", "    at Socket.emit (events.js:166:7)", "    at endReadableNT (_stream_readable.js:913:12)", "    at nextTickCallbackWith2Args (node.js:442:9)", "    at process._tickDomainCallback (node.js:397:17)"],
"level": "error",
"message": "uncaughtException: Connection lost: The server closed the connection.",
"timestamp": "2017-09-13T21:22:25.271Z"

Then i got a solution in my research to configure for handle disconnection as below. But i am struggling to configure my sql connection as below with my 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();

can anyone help me in altering my code with the above code?

Answer 1

SHOW SESSION VARIABLES LIKE '%wait_timeout';
SHOW GLOBAL  VARIABLES LIKE '%wait_timeout';

One of them is set to 28800 (8 hours). Increase it.

Or... Catch the error and reconnect.

Or... Check on how "connection pooling" is handled in your framework.

But... Be aware that network glitches can occur. So, simply increasing the timeout won't handle such glitches.

Or... Don't hang onto a connection so long. It it not playing nice. And it could lead to exceeding max_connections.

(Sorry, I don't understand your application well enough to be more specific about which of these many paths to pursue.)

max_connections

...wait_timeout and max_connections go together in a clumsy way. If the timeout is "too high", the number of connections can keep growing, thereby threatening "too many connections" error. In typical designs, it is better to lower the timeout to prevent clients from wastefully hanging onto a connection for too long.

If your situation is this: "Fixed number of clients that want to stay connected forever", then increase the timeout, but not max_connections (at least not much beyond the fixed number of clients).

Still, if the network hiccups, the connections could break. So, you can still get "connection lost". (However, if everything is on the same machine, this is rather unlikely.)

Answer 2

I have sloved this problem by using pool connection. Try it in this way https://www.npmjs.com/package/mysql

var mysql = require('mysql');
var pool  = mysql.createPool(...);
 
pool.getConnection(function(err, connection) {
  // Use the connection 
  connection.query('SELECT something FROM sometable', function (error, results, fields) {
    // And done with the connection. 
    connection.release();
 
    // Handle error after the release. 
    if (error) throw error;
 
    // Don't use the connection here, it has been returned to the pool. 
  });
});