mysql 5.5 查看事件,在mysqlworkbench 5.5上定义事件名称时获取错误

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:

Thanks

解决方案

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,

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;