Nodejs mysql Error: connection lost Server closed connection

Question

when I use node mysql, from 12:00 to 2:00 an error appears where the TCP connection is disconnected by the server. This is the complete 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 a solution . However, after I try this way, the problem also arises. Now I do not know how to do it. Is anyone facing this issue?

Here is how I wrote the following 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 using this code to handle server shutdown:

 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 parts after connection = mysql.createConnection(db_config);

Answer 2

I do not remember my initial use of this mechanism. Currently, I cannot come up with a single valid use case.

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

TL; dr; Do not use this method.

---

A pragmatic solution is to get MySQL to maintain the connection:

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

I prefer this solution to connection pooling and disconnect processing because it does not require structuring your code so that it is aware of the connection. Executing the request every 5 seconds ensures that the connection remains active and PROTOCOL_CONNECTION_LOST does not happen.

In addition, this method ensures that you support the same connection , not reconnection. It is important. Think about what happens if your script uses LAST_INSERT_ID() and the mysql connection was dropped without your knowledge?

However, this only ensures that the connection wait_timeout ( wait_timeout and interactive_timeout ) does not occur. This will fail, as expected, in all other scenarios. Therefore, be sure to handle other errors.

Answer 3

To simulate a lost connection, try

 connection.destroy(); 

More information here: https://github.com/felixge/node-mysql/blob/master/Readme.md#terminating-connections

Answer 4

Creating and destroying connections in each query can be difficult, I had some headaches when migrating the server when I decided to install MariaDB instead of MySQL. For some reason, the wait_timeout parameter had a default value of 10 seconds in the etc / my.cnf file (this leads to the fact that persistence cannot be implemented). Then the decision was set at 28800, which is 8 hours. Well, I hope someone helps with this "güevonada" ... excuse me for my poor English.