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?

Rick James · Accepted Answer · 2017-09-19 13:40:19Z

1

+100

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.)

edited Sep 19, 2017 at 13:40

answered Sep 19, 2017 at 5:00

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Jagadeesh Over a year ago

is increasing those timeout will give any other bug?

Jagadeesh Over a year ago

do you think that will be the reason for my problem?

Rick James Over a year ago

added a discussion of max_connections

Jagadeesh Over a year ago

now i have increased my timeout to 1 year and querying my database every 5 seconds and this issue is not raised again? is that a good practice?

Wang Wen'an Over a year ago

@Jagadeesh I think "querying my database every 5 seconds" is enough, it act as a heartbeat and MySQL will not close the connections, just leave the wait_timeout as defaults;

|

Denis Lisitskiy · Accepted Answer · 2017-09-22 19:29:23Z

0

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. 
  });
});

answered Sep 22, 2017 at 19:29

Denis Lisitskiy

1,34311 silver badges15 bronze badges

Collectives™ on Stack Overflow

mysql connection lost error nodejs

2 Answers 2

14 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related