Still playing around with my first ever API NodeJS app.
My current MYSQL Config is:
const mysql = require('mysql');
var pool = mysql.createPool({
host:'localhost',
user: 'abc',
password: 'abc',
database : 'abc',
multipleStatements: true,
connectionLimit: 10
});
module.exports ={
connection : pool
}
My 'order List' section is a way of getting lists of orders outstanding. This page works fine but after clicking a few buttons that bring up lists the program hangs until i restart the API.
Code:
const express = require('express');
const router = express.Router();
const verify = require('../../verifyToken');
var config = require('../../databaseConfig');
var connection= config.connection;
router.get('/getMainPageCount/',verify,(req,res) => {
const chemistID = req.user._id;
connection.getConnection(function(error,tmpConn){
if(error){
console.log("error in connecting to MYSQL " + error);
res.sendStatus(500);
}else{
var sqlString = "SET @chemistID = ?; CALL getMainPageCount(@chemistID);";
tmpConn.query(sqlString,[chemistID], async (err,rows,fields)=>{
try{
const ListData = rows[1];
//console.log(rows);
//console.log(chemistID);
if (rows && Array.isArray(rows) && rows.length) {
return res.status(200).json({
Result: true,
orders: ListData
})
}
else
{
return res.status(500).json({
Result: false
})
}
} catch (e) {
// this catches any exception in this scope or await rejection
console.log(e);
res.status(500).json({ Result: e });
}
})
}
})
});
router.get('/getOpenRequests/',verify,(req,res) => {
const chemistID = req.user._id;
var sqlString = "SET @chemistID = ?; CALL getOpenRequests(@chemistID);";
connection.getConnection(function(error,tmpConn){
if(error){
console.log("error in connecting to MYSQL " + error);
res.sendStatus(500);
}else{
try{
tmpConn.query(sqlString,[chemistID], async (err,rows,fields)=>{
const ListData = rows[1];
const foundRows = rows[2][0].rowCount;
////console.log(rows[1][0].FirstName);
//console.log(chemistID);
if (rows && Array.isArray(rows) && rows.length) {
return res.status(200).json({
Result: true,
orders: ListData,
rowCount: foundRows})
}
else
{
return res.status(500).json({
Result: false
})
}
})
} catch (e) {
// this catches any exception in this scope or await rejection
console.log(e);
res.status(500).json({ Result: e });
}
}
})
});
router.get('/getOpenOrders/',verify,(req,res) => {
const chemistID = req.user._id;
var sqlString = "SET @chemistID = ?; CALL getOpenOrders(@chemistID);";
connection.getConnection(function(error,tmpConn){
if(error){
console.log("error in connecting to MYSQL " + error);
res.sendStatus(500);
}else{
try{
tmpConn.query(sqlString,[chemistID], async (err,rows,fields)=>{
const ListData = rows[1];
const foundRows = rows[2][0].rowCount;
//console.log(rows[1][0].FirstName);
if (rows && Array.isArray(rows) && rows.length) {
return res.status(200).json({
Result: true,
orders: ListData,
rowCount: foundRows})
}
else
{
return res.status(500).json({
Result: false
})
}
})
} catch (e) {
// this catches any exception in this scope or await rejection
console.log(e);
res.status(500).json({ Result: e });
}
}
})
});
router.get('/getOpenFinalised/',verify,(req,res) => {
const chemistID = req.user._id;
var sqlString = "SET @chemistID = ?; CALL getOpenFinalised(@chemistID);";
connection.getConnection(function(error,tmpConn){
if(error){
console.log("error in connecting to MYSQL " + error);
res.sendStatus(500);
}else{
try{
tmpConn.query(sqlString,[chemistID], async (err,rows,fields)=>{
const ListData = rows[1];
const foundRows = rows[2][0].rowCount;
//console.log(rows[1][0].FirstName);
//console.log(chemistID);
if (rows && Array.isArray(rows) && rows.length) {
return res.status(200).json({
Result: true,
orders: ListData,
rowCount: foundRows
})
}
else
{
return res.status(500).json({
Result: false
})
}
})
} catch (e) {
// this catches any exception in this scope or await rejection
console.log(e);
res.status(500).json({ Result: e });
}
}
})
});
router.get('/getOpenOverdue/',verify,(req,res) => {
const chemistID = req.user._id;
var sqlString = "SET @chemistID = ?; CALL getOpenOverdue(@chemistID);";
connection.getConnection(function(error,tmpConn){
if(error){
console.log("error in connecting to MYSQL " + error);
res.sendStatus(500);
}else{
try{
tmpConn.query(sqlString,[chemistID], async (err,rows,fields)=>{
const ListData = rows[1];
const foundRows = rows[2][0].rowCount;
//console.log(rows[1][0].FirstName);
//console.log(chemistID);
if (rows && Array.isArray(rows) && rows.length) {
return res.status(200).json({
Result: true,
orders: ListData,
rowCount: foundRows})
}
else
{
return res.status(500).json({
Result: false
})
}
})
} catch (e) {
// this catches any exception in this scope or await rejection
console.log(e);
res.status(500).json({ Result: e });
}
}
})
});
This issue was not occuring until I started using this Pool connection plus the error handling.
Now I'm very new to NodeJS (and coding in general) but if i run this on the terminal:
lsof -i tcp:3000
Then i can see everything that is running on that port. When I used ot do this (pre-connection pool) I would just get the node: PID of my Listen.
Now, for about 10 seconds after running any API endpoint I get this:
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
node 18993 myserver 18u IPv6 198273222 0t0 TCP *:hbci (LISTEN)
node 18993 myserver 19u IPv6 198273223 0t0 TCP vps.myserver.com.au:hbci->myIPAddress:59264 (ESTABLISHED)
Now I assume this is the connection still sitting there... so the connection is not being closed? Could this be the issue? I just don't understand what is wrong with my error handling that I get this hang instead of a crash or something
I had to change to pool connection as it was getting lost connections here and there and after reading about it apparently the pool is the best way to go but I must be missing something? Do I need to release the connection?
