Running mysql query using node blocks the whole process and then timesout
- by lobengula3rd
I have a node javascript that uses mysql npm (Felix).
I have a procedure stored in my DB which I call when the user selects an option to kind of create its own instance of the program.
The user chooses for how long he wants that data to be initialized for him.
This is suppsoed to be between 1 and 2 years.
So if he choose 1 year this query will insert around 20,000 rows into 1 table.
If I run this query and a local DB this takes around 30 seconds (I suppose it is reasonable because its a big query which should be done only once in 1 or 2 years so its ok).
For some reason my node script freezes as if it can't handle any more calls from other users.
The even worse problem is that after like 2 minutes my client ui gets like an error from the server. At this point not all the data that was supposed to enter the DB is entered.
After waiting like another minute all the data finally gets to the DB and only then it will accept new requests.
This is my connection:
this.connection = mysql.createConnection({
host : '********rds.amazonaws.com',
user : 'admin',
password : '******',
database : '*****'
});
and this is my query function:
this.createCourts = function (req, res, next){
connection.query('CALL filldates("' +
req.body['startDate'] + '","' +
req.body['endDate'] + '","' +
req.body['numOfCourts'] + '","' +
req.body['duration'] + '","' +
req.body['sundayOpen'] + '","' +
req.body['mondayOpen'] + '","' +
req.body['tuesdayOpen'] + '","' +
req.body['wednesdayOpen'] + '","' +
req.body['thursdayOpen'] + '","' +
req.body['fridayOpen'] + '","' +
req.body['saturdayOpen'] + '","' +
req.body['sundayClose'] + '","' +
req.body['mondayClose'] + '","' +
req.body['tuesdayClose'] + '","' +
req.body['wednesdayClose'] + '","' +
req.body['thursdayClose'] + '","' +
req.body['fridayClose'] + '","' +
req.body['saturdayClose'] +
'");', function(err){
if (err){
console.log(err);
}
else
return res.send(200);
});
};
what am i missing here? as i understand connection.query should by async so why is it actually blocking my node script?
thanks.
