Tuesday, December 29, 2015

NodeJS and Promises with MySQL Connector - beware of the unreleased connection

In my NodeJS application I am using the nice MySQL library node-MySQL. I have heavily used Q promises for managing the asynchronous nature of code vs. using callbacks. By using a connection pool, I can reuse connections without having to worry about re-negotiating with the database with every query. My general design is to get a connection from the pool, do whatever I need to do with it and release it. I do not share a connection to another class/service except in the case of "in-transaction" pre/post operations. For example, on update() I have a "postUpdateAdditions()" function that takes the connection as an argument. The rule is it can not release/manage the connection as it is within a transaction.

I was recently getting some strange behavior running my integration tests whereby I was getting out-of-connection pool errors. These appear as timeouts in the system/deadlocks. After spending hours hunting down the problem I discovered two places where I was not properly releasing the connection (and thus I was connection leaking). In general this is not easy to detect (and I even added some logic to my connection manager class to track new and released connections (and to try and flag if they get out of wack)

I came across a strange situation with promises that I didn't think would happen, but it must be a timing related issue.

I had some code like the following:

var defer = q.defer();
connection.query(query_string, function (err, rows) {
    if( err ) {
       defer.reject(err); // rejection will rollback and release in caller
    }
    // do another operation on connection and return to caller
    defer.resolve(somedata);
});

return defer.promise;


Saturday, December 19, 2015

Recently I had to reinstall my operating system and chose Windows 10. I had an issue with my NodeJS 3.x environment and switched to NodeJS 5.x. In doing so, I had issues with the node-gyp again.

It is not clear the order in which I installed everything or if the order matters, but I had to install both the "Visual Studio 2015 Community Edition" as well as the "Visual Studio C++ Build Tools". This was able to solve the various compilation issues I was having.