Reputation: 479
API NodeJS loop or error handling issue: Pool Connections not closing (MYSQL)?
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?
Upvotes: 0
Views: 924
Answers (1)
Reputation: 46
You are using the .getConnection method to get a connection instance from the pool of connections. Once you are done with the connection instance you need to close the connection using connection.release();
If this is not done then this connection is not freed and basically, new requests will not have any connection to use
Alternatively, you may use the pool.query method to auto-close the connection after use.
You may check this answer for more details. https://stackoverflow.com/a/57121491/13142033
Upvotes: 1
Related Questions
- Error handling and lost connection in node.js mysql using pool
- NodeJS MySQL pool dies without throw exceptions
- error er_con_count_error too many connections express mysql
- "ER_CON_COUNT_ERROR: Too many connections" Error with pool connections to mysql from node.js
- Node.js connection pool produces infinite loop on error
- Pooling keeps incrementing connections and ends up in ER_CON_COUNT_ERROR
- Node Mysql Pool Not Ending / Closing
- Node.js and MySQL "Too many connections" error
- Release connection after use, connection pool Node.js
- How to ensure connection.end() is called when obtaining a database connection from a pool