2

I need to create an event in mysql that runs at the end of every month 11 PM.I have tried creating an event that creates another events with code below:

DELIMITER | 
CREATE EVENT `2014-10-31_CALL_ISRSP` ON SCHEDULE AT '2014-10-31 23:50:00' 
DO 
BEGIN 
call myStoredProcedure(); 
set @creatNextEvnt := Concat("CREATE EVENT ",LAST_DAY( NOW() + INTERVAL 1 MONTH ),"_CALL_ISRSP ON SCHEDULE AT '",LAST_DAY( NOW() + INTERVAL 1 MONTH )," 23:50:00' DO BEGIN call IncidentsSummaryRecorder(); set @creatNextEvnt := Concat(\"CREATE EVENT \",LAST_DAY( NOW() + INTERVAL 1 MONTH ),\"_CALL_ISRSP ON SCHEDULE AT '\",LAST_DAY( NOW() + INTERVAL 1 MONTH ),\" 23:50:00'\"); PREPARE cer FROM @creatNextEvnt; EXECUTE cer; DEALLOCATE PREPARE cer; set @delLastEvent := Concat(\"DROP IF EXISTS EVENT \",LAST_DAY(NOW()),\"_CALL_ISRSP\");  PREPARE del FROM @delLastEvent; EXECUTE del; DEALLOCATE PREPARE del; END;");
select @creatNextEvnt;
PREPARE cer FROM @creatNextEvnt; 
EXECUTE cer; 
DEALLOCATE PREPARE cer; 
set @delLastEvent := Concat("DROP IF EXISTS EVENT ",LAST_DAY(NOW()),"_CALL_ISRSP");  
PREPARE delFROM @delLastEvent; 
EXECUTE del; 
DEALLOCATE PREPARE del; 
END|

but this gives me an error that prepared statement does not support this type of command yet.

I tried storing my SQL strings to a file and executing them within the same event, but I do not know how to execute the file I created. Any help?

migrated from serverfault.com Oct 13 '14 at 10:04

This question came from our site for system and network administrators.

  • Not sure if it works inside stored procedures, but the keyword is source dev.mysql.com/doc/refman/5.0/en/mysql-batch-commands.html – Jean-Karim Bockstael Sep 19 '14 at 8:34
  • No i tried that. Only works on MYSQL Command Line Client – Masoud Sep 19 '14 at 8:43
  • i have newer heard about mysql events, but good old simple cronjob in linux or scheduled task in windows could probably do the job too. just let the cronjob handle timing and use it to execute script in mysql – simplexio Oct 13 '14 at 12:36
  • Yup... Unless ur server is join to an AD and you don't have execution rights :( – Masoud May 7 '15 at 11:30
1

You should never have to drop and create new events automatically.

You should just make the event repeatable:

  • Every minute, check the date and time
  • If the hour is 23 and minute is 50 on the last day of the month, call IncidentsSummaryRecorder();

Here is that code

DELIMITER $$

CREATE EVENT ISRSP
ON SCHEDULE
    EVERY 1 MINUTE
    STARTS '2014-10-31 23:50:00'
    ON COMPLETION PRESERVE
DO
BEGIN
    DECLARE rightnow DATETIME;
    DECLARE hh,mm TINYINT;

    SET rightnow = NOW();
    SET hh = HOUR(rightnow);
    SET mm = MINUTE(rightnow);

    IF DATE(rightnow) = LAST_DAY(DATE(rightnow)) THEN
        IF hh = 23 THEN
            IF mm = 50 THEN
                CALL IncidentsSummaryRecorder();
            END IF;
        END IF;
    END IF;

END $$

DELIMITER ;

Give it a Try !!!

  • Thanks. This will solve the problem. But how is this in term of performance? I mean it runs every min and executes the code once?! It is not gonna interrupt other transactions in Server right? – Masoud Sep 22 '14 at 1:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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