在mysqlworkbench 5.5上定义事件名称时出错 [英] gettting an error while defining the event name on mysqlworkbench 5.5
问题描述
我创建了如下存储过程:
-- --------------------------------------------------------------------------------
-- 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
现在，我正在尝试在上述存储过程中创建一个事件，如下所示:
-- 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
我在上面的代码中的EVENT archivescheduler行上得到了cross符号.我在MySQL网站上关注以下文档 .难道我做错了什么?
我怀疑的是，由于我之前已经创建了存储过程，并且试图创建一个存储过程，所以这可能会引起一些问题.请让我知道我在做什么错吗?
来自错误的MySQLWORKbench的小屏幕截图:
谢谢
对于每个文档，您不能在过程主体内创建事件.参见此帖子在过程内部创建事件-SQL .
您将必须先创建该过程，然后从如下所示的事件中调用它
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>
然后创建调用该过程的事件
DELIMITER $$
CREATE EVENT archivescheduler
ON SCHEDULE EVERY 10 SECOND
DO BEGIN
CALL `sp_archivev3`();
END $$
DELIMITER ;
另一个指针:万一您的存储过程没有从事件中触发，请执行以下操作:您可能需要检查GLOBAL EVENT SCHEDULER是否处于DISABLE状态.您可以使用以下设置将其打开
SET GLOBAL event_scheduler = ON;
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,
<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;
这篇关于在mysqlworkbench 5.5上定义事件名称时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
