gettting an error while defining the event name on mysqlworkbench 5.5

Question

I created a stored procedure like the following:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`MailMe`@`%` PROCEDURE `sp_archivev3`()
BEGIN

INSERT INTO 
     send.sgev3_archive(a_bi,
                        b_vc,
                        c_int,
                        d_int,
                        e_vc,
                        f_vc,
                        g_vc,
                        h_vc,
                        i_dt,
                        j_vc,
                        k_vc,
                        l_vc,
                        m_dt,
                        n_vch,
                        o_bit)
SELECT     a_bi,
           b_vc,
           c_int,
           d_int,
           e_vc,
           f_vc,
           g_vc,
           h_vc,
           i_dt,
           j_vc,
           k_vc,
           l_vc,
           m_dt,
           n_vch,
           o_bit

FROM   send.sgev3

WHERE m_dt BETWEEN  CURDATE() - INTERVAL 1 DAY AND CURDATE();


END

Now, I am trying to create an event inside the above stored procedure as follows:

-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`MailMe`@`%` PROCEDURE `sp_archivev3`()

EVENT archivescheduler

ON SCHEDULE

EVERY 10 SECOND

DO BEGIN

INSERT INTO 
     send.sgev3_archive(a_bi,
                        b_vc,
                        c_int,
                        d_int,
                        e_vc,
                        f_vc,
                        g_vc,
                        h_vc,
                        i_dt,
                        j_vc,
                        k_vc,
                        l_vc,
                        m_dt,
                        n_vch,
                        o_bit)
SELECT     a_bi,
           b_vc,
           c_int,
           d_int,
           e_vc,
           f_vc,
           g_vc,
           h_vc,
           i_dt,
           j_vc,
           k_vc,
           l_vc,
           m_dt,
           n_vch,
           o_bit

FROM   send.sgev3

WHERE m_dt BETWEEN  CURDATE() - INTERVAL 1 DAY AND CURDATE();


END

I am getting a cross symbol on the line EVENT archivescheduler in the above code. I am following the following documentation on the MySQL Website. Am I doing something wrong?

What I suspect is that since I have already created the stored procedure before and I am trying to create one, this could create some problem. Please let me know what I am doing wrong?

As small screenshot from the MySQLWORKbench of the error: error

Thanks

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

Per documentation you can't create event inside procedure body. See this post Create an event inside a procedure - SQL.

You will have to create the procedure first and then call it from event like below

DELIMITER $$

CREATE DEFINER=`MailMe`@`%` PROCEDURE `sp_archivev3`()
BEGIN

INSERT INTO 
     send.sgev3_archive(a_bi,
                        b_vc,
                        c_int,
                        d_int,
                        e_vc,

<Rest of the code goes here>

Then create event calling the procedure

DELIMITER $$  
CREATE EVENT archivescheduler
ON SCHEDULE EVERY 10 SECOND
DO BEGIN
    CALL `sp_archivev3`();
END $$
DELIMITER ;

Another Pointer: In case your stored procedure don't fire from event; you may need to check whether GLOBAL EVENT SCHEDULER is in DISABLE state. You can turn it on using below setting

SET GLOBAL event_scheduler = ON;

edited May 23 '17 at 10:25

Community

1
1

answered Jun 13 '14 at 22:17

Rahul

76,197
13
71
125

Thanks. So basically I need to create another stored procedure where I can create event code that you mentioned above and run that stored procedure one time. This will run the stored procedure `sp_archive()` every 10 sec ? – rocky Jun 13 '14 at 22:36
@rocky, NO; have your SP and then create the event separately ... call your SP within event as have shown in answer. Bottomline, you can't create event within procedure body. – Rahul Jun 13 '14 at 22:38
So basically you are saying that the answer you have written baove needs to be run in MySQL work bench just like a SQL statement? So far I have seen only Stored Procedure related code where we start from `DELIMETER $$ CREATE EVENT ... & so on ..`. I don't see any option of creating an `EVENT` just like we have an option for creating a `Routine`. Please clarify. Thanks – rocky Jun 16 '14 at 05:31
@rocky, Yes; `create event` yes again a separate routine (kind of) cause it's alltogether a separate DB object. Look into MySQL doc for more info. – Rahul Jun 16 '14 at 10:51
Could you tell me where exactly you are referring to in your documentation http://www.peregrinesalon.com/wp-content/uploads/2009/03/mysql-stored-procedures.pdf ? The documentation is 98 pages documentation. Because, creating a routine and then defining event is again "Defining an Event inside a Procedure" , which is not possible. Thanks – rocky Jun 17 '14 at 18:00
@rocky, that's how it should be. It's more a bug to MySQL (check in google or MySQL Spec). See this doc http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html. 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)` – Rahul Jun 17 '14 at 18:27

gettting an error while defining the event name on mysqlworkbench 5.5

1 Answers1

Linked