[MDEV-18031] Allow to create EVENT using compound statements out of stored programs Created: 2018-12-18  Updated: 2022-04-28

Status: Open
Project: MariaDB Server
Component/s: Events
Fix Version/s: None

Type: Task Priority: Major
Reporter: Chris Wilson Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: None


 Description   

I fear this is going to get reclassified as a documentation bug rather than the behaviour being changed, but worth a shot:-

At present if one tries to create an EVENT within a compound statement but outside of a stored routine, the attempt fails with:

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

For example:

DELIMITER //
IF @production IS NOT NULL THEN
CREATE EVENT IF NOT EXISTS report_stats ON SCHEDULE EVERY 5 MINUTE DO CALL report_stats();
ELSE
CREATE EVENT IF NOT EXISTS report_stats ON SCHEDULE EVERY 1 MINUTE DO CALL report_stats("Test");
END IF //
DELIMITER ;

 Comments   
Comment by Elena Stepanova [ 2018-12-28 ]

Can you explain what you mean by behaviour being changed? Has it ever worked before?

If it did and it stopped working, it is a code bug, otherwise I indeed tend to see it as a documentation shortage:

  • anonymous compound statement blocks have the same limitations as stored procedures. Currently documentation only says that such blocks allow everything that stored procedures do, it doesn't say that they forbid everything that stored procedures do. Maybe it needs clarification;
  • we don't seem to have a section of SP limitations, either, and they have many, including non-obvious ones, so the section is definitely needed.

And for the note, the error message "Recursion of EVENT DDL statements is forbidden when body is present" is really awful for any situation, and not suitable for this one (either within or outside an SP) at all.

Comment by Chris Wilson [ 2019-01-02 ]

No; I don't believe that the behaviour has ever changed - I was just hoping that this report might trigger this new feature rather than a fix to the documentation!

Comment by Elena Stepanova [ 2019-01-02 ]

I'm converting it to a feature request, then.

Comment by markus makela [ 2022-04-28 ]

This also affects creation of events inside stored procedures:

CREATE OR REPLACE PROCEDURE StartProcedure()
 
BEGIN
 
     CREATE EVENT my_event ON SCHEDULE AT CURRENT_TIMESTAMP DO SELECT 1;
 
END

Generated at Thu Feb 08 08:40:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.