Handle disconnect in Node.js and MySQL

How to fix “Error: Connection lost: The server closed the connection".

Node.js and MySQL is one of the necessary binding needed for any web application.
MySQL is one of the most popular open source database in world. There are many modules which can be used forMySQL and node.js binding but one of the most popular is node-mysql. But there is main problem with MySQL is you may lose the connection to a MySQL server due to network problems, the server timing you out,the server being restarted, or crashing. All of these events are considered fatal errors, and will have the err.code = ‘PROTOCOL_CONNECTION_LOST’.
So in this post i am going to show you the ways by which you can Handle disconnect in Node.js and MySQL.

1.You can use following code to reconnect your connection on 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)
});
}

//and then call this method
handleDisconnect();

2.Use a Pooling Strategy for Open Connections
A connection pool like generic-pool can be set to recycle idle connections on a regular basis,
ensuring that they rarely last long enough to experience odd issues.
The following example for MySQL clients is adapted from the documentation for the generic-pool package:

var pool = require("generic-pool");

var pool = poolModule.Pool({
name : 'mysql',
create : function(callback) {
var Client = require('mysql').Client;
var c = new Client();
c.user = 'root';
c.password = 'password';
c.database = 'mydb';
c.connect();
callback(null, c);
},
destroy : function(client) { client.end(); },
max : 10,
// optional. if you set this, make sure to drain() (see step 3)
min : 2,
// specifies how long a resource can stay idle in pool before being removed
idleTimeoutMillis : 30000,
// if true, logs via console.log - can also be a function
log : true
});

// acquire connection - callback function is called
// once a resource becomes available
pool.acquire(function(err, client) {
if (err) {
// handle error - this is generally the err from your
// factory.create function
}
else {
client.query("select * from foo", [], function() {
// return object back to pool
pool.release(client);
});
}
});

// Only call this once in your application -- at the point you want
// to shutdown and stop using this pool.
pool.drain(function() {
pool.destroyAllNow();
});

Add a Comment

Your email address will not be published. Required fields are marked *