nodejs mysql Error: Connection lost The server closed the connection

Question

when I use node mysql, an error is appear between 12:00 to 2:00 that the TCP connection is shutdown by the server. This is the full message:

Error: Connection lost: The server closed the connection. at Protocol.end (/opt/node-v0.10.20-linux-x64/IM/node_modules/mysql/lib/protocol/Protocol.js:73:13) at Socket.onend (stream.js:79:10) at Socket.EventEmitter.emit (events.js:117:20) at _stream_readable.js:920:16 at process._tickCallback (node.js:415:13) 

There is the solution. However, after I try by this way, the problem also appear. now I do not know how to do. Does anyone meet this problem?

Here is the way I wrote follow the solution:

    var handleKFDisconnect = function() {     kfdb.on('error', function(err) {         if (!err.fatal) {             return;         }         if (err.code !== 'PROTOCOL_CONNECTION_LOST') {             console.log("PROTOCOL_CONNECTION_LOST");             throw err;         }         log.error("The database is error:" + err.stack);          kfdb = mysql.createConnection(kf_config);          console.log("kfid");          console.log(kfdb);         handleKFDisconnect();     });    };    handleKFDisconnect(); 

Answer 1

Try to use this code to handle server disconnect:

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

In your code i am missing the parts after connection = mysql.createConnection(db_config);

Answer 2

I do not recall my original use case for this mechanism. Nowadays, I cannot think of any valid use case.

Your client should be able to detect when the connection is lost and allow you to re-create the connection. If it important that part of program logic is executed using the same connection, then use transactions.

tl;dr; Do not use this method.

---

A pragmatic solution is to force MySQL to keep the connection alive:

setInterval(function () {     db.query('SELECT 1'); }, 5000); 

I prefer this solution to connection pool and handling disconnect because it does not require to structure your code in a way thats aware of connection presence. Making a query every 5 seconds ensures that the connection will remain alive and PROTOCOL_CONNECTION_LOST does not occur.

Furthermore, this method ensures that you are keeping the same connection alive, as opposed to re-connecting. This is important. Consider what would happen if your script relied on LAST_INSERT_ID() and mysql connection have been reset without you being aware about it?

However, this only ensures that connection time out (wait_timeout and interactive_timeout) does not occur. It will fail, as expected, in all others scenarios. Therefore, make sure to handle other errors.