Why am I getting this error? How do I solve this? I am trying to update the status column in my database using Node-cron. Also, I have another question, can I use another method to update the status column automatically without any interval or trigger? To reduce the performance affected on the server.

const db = require("../config/databaseConfig");
const cron = require("node-cron");

function updateStatus(callback) {
  var dbConn = db.getConnection();

  // Connect to the database
  dbConn.connect(function (err) {
    if (err) {
      return callback(err, null);
    }
    // Get the current date
    const currentDate = new Date();
    // Select all events from the 'events' table where the start date is before the current date and the end date is after the current date
    dbConn.query(
      "SELECT * FROM practiceme.events WHERE eventStart < ? AND eventEnd > ?",
      [currentDate, currentDate],
      (error, results) => {
        if (error) return callback(error, null);

        // For each row in the results, update the status of the event to 'ongoing'
        results.forEach((row) => {
          dbConn.query(
            'UPDATE practiceme.events SET status = "ongoing" WHERE eventId = ?',
            [row.id],
            (error) => {
              if (error) return callback(error, null);
            }
          );
        });
      }
    );
  });
}

function updateStatus2(callback) {
  var dbConn = db.getConnection();
  dbConn.connect(function (err) {
    if (err) {
      // Return an error if there was a problem connecting to the database
      return callback(err, null);
    }
    // Get the current date
    const currentDate = new Date();
    // Select all events from the 'events' table where the end date is before the current date
    dbConn.query(
      "SELECT * FROM practiceme.events WHERE eventEnd < ?",
      [currentDate],
      (error, results) => {
        if (error) return callback(error, null);

        // For each row in the results, update the status of the event to 'past'
        results.forEach((row) => {
          dbConn.query(
            'UPDATE practiceme.events SET status = "past" WHERE eventId = ?',
            [row.id],
            (error) => {
              if (error) return callback(error, null);
            }
          );
        });
      }
    );
    // Close the connection to the database
    dbConn.end();
  });
}

// Run the 'updateStatus' function every minute using the cron library
cron.schedule("* * * * *", () => {
  console.log("Updating status column every minute.");
  updateStatus((error, results) => {
    if (error) {
      console.log("Error updating status column:", error);
    } else {
      console.log("Status column updated successfully.");
    }
  });

  updateStatus2((error, results) => {
    if (error) {
      console.log("Error updating status column:", error);
    } else {
      console.log("Status column updated successfully.");
    }
  });
});

CodePudding user response：

You should close the connection after the callbacks have been executed, otherwise, the dbConn.end() line will be executed prior to the queries.
Try to move the dbConn variable in the schedule and pass it as a parameter:

const db = require('../config/databaseConfig');
const cron = require('node-cron');

function updateStatus(dbConn, callback) {
  // Get the current date
  const currentDate = new Date();
  // Select all events from the 'events' table where the start date is before the current date and the end date is after the current date
  dbConn.query(
    'SELECT * FROM practiceme.events WHERE eventStart < ? AND eventEnd > ?',
    [currentDate, currentDate],
    (error, results) => {
      if (error) return callback(error, null);

      // For each row in the results, update the status of the event to 'ongoing'
      results.forEach((row) => {
        dbConn.query(
          'UPDATE practiceme.events SET status = "ongoing" WHERE eventId = ?',
          [row.id],
          (error) => {
            if (error) return callback(error, null);
          }
        );
      });
    }
  );
}

function updateStatus2(dbConn, callback) {
  // Get the current date
  const currentDate = new Date();
  // Select all events from the 'events' table where the end date is before the current date
  dbConn.query(
    'SELECT * FROM practiceme.events WHERE eventEnd < ?',
    [currentDate],
    (error, results) => {
      if (error) return callback(error, null);

      // For each row in the results, update the status of the event to 'past'
      results.forEach((row) => {
        dbConn.query(
          'UPDATE practiceme.events SET status = "past" WHERE eventId = ?',
          [row.id],
          (error) => {
            if (error) return callback(error, null);
          }
        );
      });
    }
  );
}

// Run the 'updateStatus' function every minute using the cron library
cron.schedule('* * * * *', () => {
  console.log('Updating status column every minute.');
  var dbConn = db.getConnection();
  dbConn.connect(function (err) {
    if (err) throw err;
    updateStatus(dbConn, (error, results) => {
      if (error) {
        console.log('Error updating status column:', error);
      } else {
        console.log('Status column updated successfully.');
      }
      updateStatus2(dbConn, (error, results) => {
        if (error) {
          console.log('Error updating status column:', error);
        } else {
          console.log('Status column updated successfully.');
        }
        // Close the connection to the database
        dbConn.end();
      });
    });
  });
});