Prevent node-mysql connection closed error
I have been using nodemon and forever to keep my MySQL backed Node.js app running. I had a small issue where the app would crash after 24-36 hours with MySQL closing the connection. I just assumed it was due to poor coding on my part but didn't really know where my mistake was until now......
exports.getData = function getData(what, callback) {
pool.getConnection(function(err, connection) {
connection.on('error', function(err) {
console.log(err); // 'ER_BAD_DB_ERROR'
});
var sql = "SELECT * FROM table WHERE column = '" + what + "'";
connection.query(sql, function(err, rows) {
connection.on('error', function(err) {
console.log(err); // 'ER_BAD_DB_ERROR'
});
callback(rows);
});
connection.release();
});
}This code works, not all the time but 99% of the time it works great.
The problem as it turns out is where I was releasing the connection....
exports.getData = function getData(what, callback) {
pool.getConnection(function(err, connection) {
connection.on('error', function(err) {
console.log(err); // 'ER_BAD_DB_ERROR'
});
var sql = "SELECT * FROM table WHERE column = '" + what + "'";
connection.query(sql, function(err, rows) {
connection.on('error', function(err) {
console.log(err); // 'ER_BAD_DB_ERROR'
});
connection.release(); //Here is where it should be.
callback(rows);
});
//Not Here
});
}I was calling back to other functions, which sometimes themselves where callbacks or would do other long-running tasks and never releasing the connection.
Since I had a pool of connections I should have been releasing the connection when I was done with it......imagine that.
** TL;DR **
Always put your toys up when you are done with them.