Pooling problem using nested store procedure #516
Comments
|
Does the inner most nesting "pool2" get executed and is its output logged to the console? |
|
Yes. |
|
This works fine for first 100+ request, then after it starts giving error |
|
It seems to me at some point the connection is not given back to the pool. Try to "unnest" your code a little bit and re-use the first connection you get. connection.end();
console.log(SP1_ID);
pool.getConnection(function (err, connection) {
console.log("pool2");Here you are calling Therefore instead of ending the first connection, re-use it with the stuff you have inside "pool2". |
|
Another question, why are you using [EDIT]: Using one connection would also help the efficiency of your stored procedures, since they are compiled on the mysql-server for every connection you make to the server. |
|
the solution you gave in the above post, it is not working. i am getting same error after 200+ requests. i was getting this error so i used pool |
|
hello, please check the below code .. this also works for first 100 request then gives error. var express = require('express');
var app = express();
var mysql = require('mysql');
var connection="", adx_db_cs="", adx_db_name="", adx_db_username="", adx_db_password="";
//DB Details
adx_db_cs='localhost';
adx_db_name='adx';
adx_db_username='root';
adx_db_password='sasa';
//End DB Details
app.post('/', function(req, res){
var pool = mysql.createPool({
host : adx_db_cs,
user : adx_db_username,
password : adx_db_password,
database : adx_db_name,
queueLimit : '100',
});
pool.getConnection(function(err, connection) {
console.log("db connected");
connection.end();
console.log("db closed");
});
res.writeHead('200', {'content-type' : 'application/json'});
res.end();
});
app.listen(9000);error which i get |
|
See #528 (comment) and check if it is related to your problem. Since you set queueLimit to 100, and after 100 Connection your script dies it seems to me, that you either never give back connections to the pool or your database server can not handle the load and takes to long to answer 100+ request. |
|
This is untested and without a better understanding of what your trying to do I can't garentee that it will work as you expect it to but it should resolve the issues your having. Please don't simply use the code, review it and try to understand it. var http = require('http'), mysql = require('mysql');
var SP1_ID='', SP1_UserCode='';
var SP2_BidAmount='', SP2_NegotiatedPrice='';
var pool = mysql.createPool({
host : 'localhost',
user : 'root',
password : 'sasa',
database : 'dbname',
});
http.createServer(function (req, res) {
if (req.url == '/') {
pool.getConnection(function(err, connection) {
if(err) {
// You failed to get a connection from the pool
console.error(err);
return;
}
console.log("pool1");
connection.query("call usp_1 ('', '320x50', '3119', '3D Magic Words FREE', 'app')", function(err, results) {
//if (err) throw err; // This will just kill the application when an error occurs?
if (err) {
// Log the error but don't throw it unless you WANT
// your app to die or your going to catch it at
// another level.
connection.end();
console.error(err);
return;
}
/*var i = 0, ilen = results[0].length;
while(i<ilen) {
SP1_ID = results[0][i].ID;
SP1_UserCode = results[0][i].UserCode;
i += 1;
}*/
// This is much clearer for the same effect.
// But what are you actually trying to do?
// This will result in SP1_ID and SP1_UserCode being
// the values of the last record
for(var key in results[0]) {
SP1_ID = results[0][key].ID;
SP1_UserCode = results[0][key].UserCode;
}
//connection.end(); // Don't end the connection you still need it?
console.log(SP1_ID);
connection.query("call usp_2 (?, ?)", [SP1_ID, SP1_UserCode], function(err, results) {
connection.end(); // We've finished with the connection so close it
if(err) {
//connection.end(); // Don't need this, don't know why I added it.
console.error(err);
return;
}
/*var i, ilen;
i=0;
ilen = results[0].length;
while(i<ilen)
{
SP2_BidAmount = results[0][key].BidAmount;
SP2_NegotiatedPrice = results[0][i].NegotiatedPrice;
i += 1;
}*/
// Again this is much clearer for the same effect.
// But what are you actually trying to do?
// This will result in SP2_BidAmount and SP2_NegotiatedPrice being
// the values of the last record
for(var key in results[0]) {
SP2_BidAmount = results[0][key].BidAmount;
SP2_NegotiatedPrice = results[0][key].NegotiatedPrice;
}
console.log(SP2_BidAmount);
});
});
});
};
}).listen(3000);I hope your a student or trying to learn this of your own accord? If you are learning of your own accord then I really would recommend you look at a different language first, node has some very unusual methodologies that take even seasoned developers a while to get there head around. In regard to your second block. You shouldn't be creating your pool in the request. This would result in the recreation of the pool every time. Instead move it outside the callback so that it is globally accessible. In relation to the error, all you are doing in the getConnection block is ending the connection. It is quite possible that you didn't get a connection, the reason why you didn't would be contained in error. You want to do something like this: pool.getConnection(function(err, connection) {
if(err) {
// Couldn't get a connection :(
// No need to close as we didn't get one
console.err(err);
return;
}
// If we got here we have a connection! :D
console.log("db connected");
connection.end();
console.log("db closed");
});@kai-koch I agree that "Nesting callbacks to deep makes code unreadable and difficult to debug" however I'm unaware of a way of passing the connection without doing so for multiple queries that depend on the result of there predecessor. |
|
@apparatusdeus I try to avoid anonymous functions, when ever possible and replace them with "normal" functions or with a kind of factory-pattern. |
* packets: Allow terminating packets of length 0 Fixes mysqljs#514 * packets: Add readPacket unit tests
hello,
Below is my code, i am using connection pooling. my code runs perfectly for first 100+ request i send to the program. but after that it starts giving this error
can u please help me out.
thanks
pranav
The text was updated successfully, but these errors were encountered: