Why might this node.js mysql query be preventing my program from ending?-CodePudding

I am trying to fix a bug in my node.js application, which is preventing a program I have written from ending successfully.

The script is returning the required results, but something is still running in the background which appears to be preventing it from terminating.

Through process of elimination, I have been able to narrow down the culprit to this getPlayers() function; but I am not sure why the problem is being caused.

Firstly, I am exporting the database pool from a module, like so:

require('dotenv').config()

const mysql = require("mysql"),
      pool = mysql.createPool({
        host: process.env.dbHost,
        user: process.env.dbUser,
        password: process.env.dbPassword,
        database: process.env.dbDatabase
      })

pool.connectionLimit = 15

module.exports = pool

And this is the query that appears to be causing all of the problems:

const getPlayers = () => {
  return new Promise((resolve, reject) => {
    db.query('SELECT * FROM cc_players', (err, res) => {
      if(err)
        console.log(err)
      
      resolve(res)
    })
  })
}

If I comment out the db.query() function, and simply resolve an empty array, the script terminates as expected. Is there something about the database query that could be causing the script to continue running in the background?

CodePudding user response：

You created a pool, which will keep connections open for re-use. As long as there's open sockets, Node.js will not exit.

CodePudding user response：

You have to use alternative way because create a pool will keep the connection opened in background, so you can use createConnection function instead of createPool then you can close the connection manually

let mysql = require('mysql');

let connection = mysql.createConnection({
    host: process.env.dbHost,
    user: process.env.dbUser,
    password: process.env.dbPassword,
    database: process.env.dbDatabase
  });

const getPlayers = () => {
  return new Promise((resolve, reject) => {
    db.query('SELECT * FROM cc_players', (err, res) => {
      if(err)
        console.log(err)
      
      resolve(res)
    })
  })
}

And once get the callback you can close the connection

getPlayer().then(res => {
    .
    .
    .
    connection.end();
})

CodePudding user response：

Because you're creating a pool, previously opened SQL connections will not be closed, and instead kept for later uses. This is why Node.JS never exits.

To fix this, the mysql package provides a pool.end function to close all connections in the pool. You should call it when your script is ready to exit, perhaps like so:

function onExit() {
  // ...

  // Assuming ``db`` is the pool you created
  db.end()

  // ...
}

Beware that no further SQL operations can be performed on the pool after pool.end is called.