0

I have coded an event as below to run every Monday and Wednesday, but it runs only on Monday and doesn't on Wednesday.

delimiter |
CREATE EVENT IF NOT EXISTS evnt_demo_ref_fs_test
ON SCHEDULE EVERY 1 DAY 
ON COMPLETION PRESERVE enable
DO 
   BEGIN
    DECLARE errorCode CHAR(5) DEFAULT '00000';
    DECLARE errorMessage TEXT DEFAULT '';
    
DECLARE exit HANDLER FOR SQLEXCEPTION 
    BEGIN
    GET DIAGNOSTICS CONDITION 1
        /*errorCode = RETURNED_SQLSTATE, errorMessage = MESSAGE_TEXT;*/
        @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
        SELECT @p1 as RETURNED_SQLSTATE  , @p2 as MESSAGE_TEXT;
        INSERT INTO demo_log_fs (CODE, message) SELECT CONCAT('ERR_CODE: ', @p1) AS STATUS, CONCAT('MySQL ERROR: ', @p1, ': ', @p2) AS MESSAGE;
        ROLLBACK;
    END;
    /*IF DAYOFWEEK(curdate()) BETWEEN 5 and 7 THEN */
    IF WEEKDAY(curdate()) BETWEEN 0 AND 2 THEN 
INSERT INTO demo_log_fs (CODE, message) values ('0000','demo refresh started');
INSERT INTO demo_log_fs (CODE, message) values ('0000','Deletes started');
delete from drop_curr_fs;
delete from drop_hist_fs;
INSERT INTO demo_log_fs (CODE, message) values ('0000','Deletes Completed');
INSERT INTO demo_log_fs (CODE, message) values ('0000','Inserts Started');
insert into drop_curr_fs select  * From     master  ;
insert into drop_hist_fs select  * From     hist_master ;
INSERT INTO demo_log_fs (CODE, message) values ('0000','Inserts Completed');
INSERT INTO demo_log_fs (CODE, message) values ('0000','demo refresh Completed');
end if;
END |
delimiter ;

How do I achieve this?

2
  • it should run from Monday till Wednesday, how did you determine that it didn't run on Wednesday?
    – nbk
    Jun 28, 2021 at 14:41
  • Would this work ? IF WEEKDAY(curdate()) EQUAL 0 OR 2 THEN rather than BETWEEN? Jun 29, 2021 at 11:48

2 Answers 2

0

Apply the KISS design principle:

  1. Write a Stored Procedure that does most of the work.
  2. Fire that SP from 2 events, one each Monday, one each Wed.

Note that all the timing (Mon/Wed) information is in the Event; none is in the SP. Nice and clean separation.

0

Thank you for your time on this.

I figured out the solution. The issue was with the BETWEEN keyword, that triggers the event between the mentioned dates. So I changed it to IN keyword and it worked fine.

Thank you, Mushtaq Ahmed B

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.