44

I need a persistent MySQL connection for my Node web app. The problem is that this happens about a few times a day:

Error: Connection lost: The server closed the connection.
at Protocol.end (/var/www/n/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:895:16
at process._tickCallback (node.js:415:13)
error: Forever detected script exited with code: 8
error: Forever restarting script for 2 time
info: socket.io started

Here is my connection code:

// Yes I know multipleStatements can be dangerous in the wrong hands.
var sql = mysql.createConnection({
    host: 'localhost',
    user: 'my_username',
    password: 'my_password',
    database: 'my_database',
    multipleStatements: true
});

sql.connect();

function handleDisconnect(connection) {
    connection.on('error', function(err) {
        if (!err.fatal) {
            return;
        }
        if (err.code !== 'PROTOCOL_CONNECTION_LOST') {
            throw err;
        }
        console.log('Re-connecting lost connection: ' + err.stack);
        sql = mysql.createConnection(connection.config);
        handleDisconnect(sql);
        sql.connect();
    });
}

handleDisconnect(sql);

As you can see, the handleDisconnect code does not work..

apscience
  • 7,033
  • 11
  • 55
  • 89

3 Answers3

70

Use the mysql connection pool. It will reconnect when a connection dies and you get the added benefit of being able to make multiple sql queries at the same time. If you don't use the database pool, your app will block database requests while waiting for currently running database requests to finish.

I usually define a database module where I keep my queries separate from my routes. It looks something like this...

var mysql = require('mysql');
var pool  = mysql.createPool({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret'
});

exports.getUsers = function(callback) {
  pool.getConnection(function(err, connection) {
    if(err) { 
      console.log(err); 
      callback(true); 
      return; 
    }
    var sql = "SELECT id,name FROM users";
    connection.query(sql, [], function(err, results) {
      connection.release(); // always put connection back in pool after last query
      if(err) { 
        console.log(err); 
        callback(true); 
        return; 
      }
      callback(false, results);
    });
  });
});
Gareth Parker
  • 5,012
  • 2
  • 18
  • 42
Daniel
  • 38,041
  • 11
  • 92
  • 73
  • 17
    Is there a way to use pools without refactoring everything? I have dozens of SQL queries in the app. – apscience Jun 10 '13 at 07:01
  • btw, how can we know mysql server maximum connections at a time ? – Ddo Nov 08 '13 at 05:45
  • Check your MySQL config for "max_connections". http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html – Daniel Nov 09 '13 at 20:58
  • 2
    Note: connection.end() currently raises a warning that the function is being deprecated (with the behavior being changed in the next version or something), and connection.release() is recommended instead. – Josh1billion May 03 '14 at 19:53
  • The downside to using pools is that you cannot pause the connection on a pool. This can come in handy when queue processing items. – Adam Fowler Jan 31 '17 at 19:03
34

I know this is super delayed, but I've written a solution to this that I think might be a bit more generic and usable. I had written an app entirely dependent on connection.query() and switching to a pool broke those calls.

Here's my solution:

var mysql = require('mysql');

var pool = mysql.createPool({
    host     : 'localhost',
    user     : 'user',
    password : 'secret',
    database : 'test',
    port     : 3306
});

module.exports = {
    query: function(){
        var sql_args = [];
        var args = [];
        for(var i=0; i<arguments.length; i++){
            args.push(arguments[i]);
        }
        var callback = args[args.length-1]; //last arg is callback
        pool.getConnection(function(err, connection) {
        if(err) {
                console.log(err);
                return callback(err);
            }
            if(args.length > 2){
                sql_args = args[1];
            }
        connection.query(args[0], sql_args, function(err, results) {
          connection.release(); // always put connection back in pool after last query
          if(err){
                    console.log(err);
                    return callback(err);
                }
          callback(null, results);
        });
      });
    }
};

This instantiates the pool once, then exports a method named query. Now, when connection.query() is called anywhere, it calls this method, which first grabs a connection from the pool, then passes the arguments to the connection. It has the added effect of grabbing the callback first, so it can callback any errors in grabbing a connection from the pool.

To use this, simply require it as module in place of mysql. Example:

var connection = require('../middleware/db');

function get_active_sessions(){
  connection.query('Select * from `sessions` where `Active`=1 and Expires>?;', [~~(new Date()/1000)], function(err, results){
    if(err){
      console.log(err);
    }
    else{
      console.log(results);
    }
  });
}

This looks just like the normal query, but actually opens a pool and grabs a connection from the pool in the background.

Adam Yost
  • 3,616
  • 23
  • 36
21

In response to @gladsocc question:

Is there a way to use pools without refactoring everything? I have dozens of SQL queries in the app.

This is what I ended up building. It's a wrapper for the query function. It will grab the connection, do the query, then release the connection.

var pool = mysql.createPool(config.db);

exports.connection = {
    query: function () {
        var queryArgs = Array.prototype.slice.call(arguments),
            events = [],
            eventNameIndex = {};

        pool.getConnection(function (err, conn) {
            if (err) {
                if (eventNameIndex.error) {
                    eventNameIndex.error();
                }
            }
            if (conn) { 
                var q = conn.query.apply(conn, queryArgs);
                q.on('end', function () {
                    conn.release();
                });

                events.forEach(function (args) {
                    q.on.apply(q, args);
                });
            }
        });

        return {
            on: function (eventName, callback) {
                events.push(Array.prototype.slice.call(arguments));
                eventNameIndex[eventName] = callback;
                return this;
            }
        };
    }
};

And I use it like I would normally.

db.connection.query("SELECT * FROM `table` WHERE `id` = ? ", row_id)
          .on('result', function (row) {
            setData(row);
          })
          .on('error', function (err) {
            callback({error: true, err: err});
          });
Community
  • 1
  • 1
Vlad Goran
  • 463
  • 4
  • 11
  • I just tried doing this, however i get `ReferenceError: db is not defined` – Brandy Jul 07 '17 at 22:47
  • @BrandonStewart in the second code sample you are in another file, so you would need to grab the connection as you would normally, `var db = require('./db.js');` somewhere up top – Vlad Goran Jul 11 '17 at 10:17
  • It worked perfectly, thank you very much for the help. – Ger Feb 26 '19 at 23:04