node.js / node_mysql - stale connections get "NO database selected" error
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开发者_StackOverflow how to do this?
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);
because of node-mysql update following code maybe work:
client._socket.on('connect', function() {
console.log("use ",DB_NAME);
client.query('use '+DB_NAME);
});
When using the node-mysql-promise package, you may want to use this code to do the same:
dbConn.pool.on('connection', function() {
dbConn.pool.query("USE myDBname");
} );
精彩评论