How do I use recursion in node.js mysql without breaking the connection?
I am using iojs and node-mysql. This is my first foray into asynchronous server side programming. It's essentially a batch job: run it all the time, exit. I am specifically trying to do this on a table full of revisions:
For each document edited last year; for each revision of this document made in the last year; get a preliminary revision and split its contents with the current version.
So, I use the results of one query (per document) to strip away an arbitrary number of additional queries (per revision) that themselves have to recursively (get a preliminary revision).
I cannot figure out how to close the database connection. Nearby, as I can tell, recursion is a confusing factor: if I remove this from the code, I can close the db connection. But I need to go back.
Here is a minimal example (assume require and config is OK) that does the behavior I see in my program.
var con = mysql.createConnection(db_config);
con.connect();
con.query('SELECT field_2 FROM test_table', function(err, rows) {
if (err) throw err;
rows.forEach(function(row) {
second_query(row.field_2);
});
// using this here works if there is no recursion, even if second_query runs long
// using this here does not work if there is recursion
// removing this allows the program to run to completion, but the event loop never exits
con.end()
});
function second_query(key) {
con.query('SELECT * FROM test_table_2 WHERE field_2 > ?', [key], function(err, rows) {
if (err) throw err;
if (rows.length > 0) {
rows.forEach(function(row) {
console.log(row.field_2);
});
second_query(key + 1);
}
});
}
I have tried very hard to solve this problem by logging database queries to accumulator and decreasing accumulators as each query completes, but this has not yet yielded predictable success and it makes the code painful to work with.
source to share
I like it async.queue
for this type of workload. You get trivially customizable concurrency for free, but it's always easier to debug with concurrency 1.
var mysql = require("mysql");
// concurrency 1. Adjust to taste once it working
var queue = require("async").queue(secondQuery, 1);
var dbOptions = {
host: process.env.DOCKER_IP,
database: "hoosteeno",
user: "root",
password: "password"
};
var con = mysql.createConnection(dbOptions);
con.connect();
con.query("SELECT field_2 FROM test_table", function (error, rows) {
if (error) throw error;
rows.forEach(function (row) {
queue.push(row.field_2);
});
});
function secondQuery (key, callback) {
var sql = "SELECT * FROM test_table_2 WHERE field_2 > ?";
con.query(sql, [key], function (error, rows) {
if (error) {
callback(error);
return;
}
if (rows.length > 0) {
rows.forEach(function (row) {
console.log(row.field_2);
});
queue.push(key + 1);
}
callback();
});
}
queue.drain = function () {
con.end();
};
source to share
I think your problem has to do with detecting the completion of all your asynchronous SQL queries. I have a few ideas here.
Here's one approach (disclaimer: untested!) That doesn't change the structure of your code too much. I use allQueriesRan
to track when all your requests have been released and I use it pendingQueries
as a counter to keep track of the number of requests we are still waiting for.
var allQueriesRan = false;
var pendingQueries = 0;
function second_query(key) {
pendingQueries++;
con.query('SELECT * FROM test_table_2 WHERE field_2 > ?', [key], function(err, rows) {
pendingQueryies--;
if (allQueriesRan && pendingQueries === 0) {
// We've finished our recursion and we've allowed all queries to return
con.end();
}
if (err) throw err;
if (rows.length > 0) {
rows.forEach(function(row) {
console.log(row.field_2);
});
second_query(key + 1);
} else {
allQueriesRan = true;
}
});
}
The promise library can also keep your code concise if you want to go down that rabbit hole. Like kriskowal / the Q . Q, for example, allows you to return a recursive function of a promise object that can be "resolved" later, ie. After returning all your inquiries. You can tie this promise to a call .then()
to close your DB connection at the right time. This is what your code looks like using this approach:
var deferred = Q.defer();
function second_query(key) {
con.query('SELECT * FROM test_table_2 WHERE field_2 > ?', [key], function(err, rows) {
if (err) {
return deferred.reject(err);
}
if (rows.length > 0) {
rows.forEach(function(row) {
console.log(row.field_2);
});
second_query(key + 1);
} else {
deferred.resolve();
}
});
return deferred.promise;
}
second_query(yourKey)
.then(function() {
console.log('All done!');
})
.fail(err, function(err) {
throw err;
})
.finally(function() {
con.end();
});
Note that one of the convenience functions is that when the request ever returns err
, the call deferred.reject()
will short-circuit your thread of execution to the handler .fail()
at the very bottom.
source to share