0

I created an event which automatically gets the system current date and matches it with the start and end date of a school year. If the system current date is within the start and end date of the school year, it updates the isCurrentSchoolYear column with 1.

It worked perfect the first time I did this. However, when I changed the date on my PC to test if it will work with year 2020, nothing happens anymore. The event is set to run every 30 seconds.

schoolyear_mt table

(had to manually call the setCurrentSchoolYear() stored procedure to update isCurrentSchoolYear of 2020 to 1 which is supposed to be updated automatically)

enter image description here

======================================================================

enrollmentdb.setSchoolYear event definition:

CREATE EVENT enrollmentdb.e_setCurrentSchoolYear
ON SCHEDULE EVERY 30 second
DO CALL setCurrentSchoolYear();

======================================================================

SELECT @@event_scheduler;

enter image description here

======================================================================

show processlist;

enter image description here

show events from enrollmentdb;

enter image description here

As you can see, Interval Value column shows 30 but the Time column shows 63163099 and continues to increment.

So the problem is it doesn't automatically update the isCurrentSchoolYear column every after 30 seconds. I don't want to manually call the stored proc named setCurrentSchoolYear() to make it work.

CREATE DEFINER=`root`@`localhost` PROCEDURE `setCurrentSchoolYear`()
BEGIN

DECLARE aSyId INT;

DECLARE EXIT HANDLER FOR sqlexception
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

START TRANSACTION;
    -- compare the currentdate 
    SELECT schoolyear_id INTO aSyId FROM schoolyear_mt
    WHERE curdate() >= start_date AND curdate() <= end_date;

    SELECT aSyId;

    IF aSyId IS NOT NULL 
    THEN
        UPDATE schoolyear_mt SET isCurrentSchoolYear = 0 
        WHERE schoolyear_id >= 0;

        UPDATE schoolyear_mt SET isCurrentSchoolYear = 1
        WHERE schoolyear_id = aSyId;
    END IF;

COMMIT;

END

The problem with automatic update happened after I changed my machine's system date to Feb 02, 2020 and also tried Feb 02, 2015 to test if it's effective. What could possible be hindering it from executing manually every 30 seconds?

Any thoughts?

Thank you.

heisenberg
  • 1,784
  • 4
  • 33
  • 62
  • I have no answer for you issue, but the method you use to update that column feels really weird. It's an easy calculation, so wouldn't it be easier to just calculate it on the fly? If you don't want to add a simple comparison to `now()` to your queries, couldn't you just make a view? Or at least run this event just once a year :) – Nanne Feb 17 '17 at 06:50
  • @Nanne Yeah, actually I'm supposed to set it to every 1 year. But for testing purposes, I set it to seconds. – heisenberg Feb 17 '17 at 07:04
  • 1
    Every recurrent event has start time; possible something happened on server after time changing. Run `SELECT * FROM information_schema.events`, it shows more information then SHOW command. Also, try to recreate the event. – Devart Feb 17 '17 at 07:12
  • @Devart Thank you. You're right I found some conflict info. I just had to restart the services and mysql workbench. It now working. The problem occurs after system date's year is changed but after restart of XAMPP, it runs perfect. – heisenberg Feb 17 '17 at 08:20

0 Answers0