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?
Best Answer
You should never have to drop and create new events automatically.
You should just make the event repeatable:
IncidentsSummaryRecorder();
Here is that code
Give it a Try !!!