node.js / node_mysql - stale connections get "NO database selected" error

Question

We have a node.js app that uses node_msyql, a great little library for accessing MySQL databases.

Unfortunately, if our connection is not used for maybe 8-10 hours, the next time we try to run a query, we get a "No database selected" error back from the server. We need to add a "USE db" somewhere, but I can't figure out where.

Now, it makes sense to me that connections would go stale, and it seems as though node_mysql is refreshing those stale connections, but there doesn't seem to be a way to make sure that the right db is connected. I was looking for a .connected() callback or event or something that would let me make sure the correct DB was alway USE'd, but no luck so far.

Any suggestions how to do this?

Answer 1

Ys, client tries to reconnect. You can try to query 'use db' on reconnect using code like this:

client._connection.on('connect', function() { client.query('use db'); })

This is where reconnection happen in the node-mysql ('end' handler): https://github.com/felixge/node-mysql/blob/master/lib/mysql/client.js

var connection = self._connection = new Stream(),
    parser = self._parser = new Parser();

connection
  .on('error', function(err) {
    var connectionError = err.code && err.code.match(/ECONNREFUSED|ENOTFOUND/);
    if (connectionError) {
      if (cb) {
        cb(err);
        return;
      }
    }

    self.emit('error', err);
  })
  .on('data', function(b) {
    parser.write(b);
  })
  .on('end', function() {
    if (self.ending) {
      self.connected = false;
      self.ending = false;
      return;
    }

    if (!self.connected) {
      return;
    }

    self.connected = false;
    self._prequeue(connect);
  });
connection.connect(self.port, self.host);