1

I am trying to create a procedure which create an event in MySQL.

For example, something similar to this:

delimiter //
CREATE PROCEDURE schedule_my_db_job() 
  BEGIN

    CREATE EVENT MY_DB_JOB 
    ON SCHEDULE EVERY 1 MINUTE DO 
    BEGIN 
        SELECT * FROM information_schema.events; -- for example
    END;      

  END//
delimiter ;

But it gives me following error when I try to create this procedure.

ERROR 1576 (HY000): Recursion of EVENT DDL statements is forbidden when body is present

As I understand MySQL allow stored procedures to have CREATE EVENT sql statement inside. (Quotation from https://dev.mysql.com/doc/refman/5.6/en/create-event.html "You can create an event as part of a stored routine, but an event cannot be created by another event")

Could you please let me know what is the error here?

Thanks.

2
  • Try without the Begin and End for your select inside the event: CREATE EVENT e_hourly ON SCHEDULE EVERY 1 HOUR COMMENT 'Clears out sessions table each hour.' DO DELETE FROM site_activity.sessions; – greenlitmysql Nov 19 '15 at 15:36
  • Thanks, but this also gives me the same error. It seems MySQL doesn't allow event creation inside a stored procedure. See dev.mysql.com/doc/refman/5.6/en/… – Manjula Nov 20 '15 at 2:26
0

It seems there is a conflict in MySQL documentation. I think CREATE EVENT Syntax section in MySQL documentation is not correctly updated in later versions. Although "CREATE EVENT Syntax" sections says

You can create an event as part of a stored routine, but an event cannot be created by another event.

In Event Scheduler Restrictions section in mysql documentation, It says

An event may not be created, altered, or dropped by a stored routine, trigger, or another event. An event also may not create, alter, or drop stored routines or triggers. (Bug #16409, Bug #18896)

1
  • Has this been rectified in 5.6.23? – MontyPython Aug 29 '16 at 4:05

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.