Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18031

Allow to create EVENT using compound statements out of stored programs

Details

    • Task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • Events
    • 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 ;

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          elenst Elena Stepanova added a comment -

          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.

          elenst Elena Stepanova added a comment - 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.
          jakdaw Chris Wilson added a comment -

          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!

          jakdaw Chris Wilson added a comment - 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!
          elenst Elena Stepanova added a comment -

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

          elenst Elena Stepanova added a comment - I'm converting it to a feature request, then.
          markus makela markus makela added a comment -

          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

          markus makela markus makela added a comment - 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

          People

            Unassigned Unassigned
            jakdaw Chris Wilson
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.