每 1 分钟发生一次 MYSQL 事件不起作用

Question

首先，这是 OpenCart

我有两个表:

1. oc_product (product_id, model, price, event_start, event_end and etc.)
2. oc_product_to_category(product_id, category_id)

每个产品都有开始日期和结束日期。我创建了 MYSQL 事件来捕获每个具有过期日期的产品 (event_end < NOW()) 以将其存储在 id = 68 的类别“存档”中

这是MYSQL EVENT的代码

CREATE EVENT move_to_archive_category
ON SCHEDULE EVERY 1 MINUTE
STARTS NOW()
DO
INSERT INTO `oc_product_to_category` (product_id, category_id) 
SELECT product_id, 68 as category_id 
FROM oc_product p WHERE p.event_end < NOW() AND p.event_end <> '0000-00-00';

当事件开始时它正常工作!但是，当我进行管理并发布过期的新产品时，我等了 1 分钟才能在“存档”类别中看到该产品，但没有任何反应。

我在“SHOW PROCESSLIST”中看到，一切正常:

event_scheduler     localhost   NULL    Daemon  67  Waiting for next activation     NULL

而且“SHOW EVENTS”看起来不错

Db  Name    Definer     Time zone   Type    Execute at  Interval value  Interval field  Starts  Ends    Status  Originator  character_set_client    collation_connection    Database Collation
events  move_to_archive_category    root@localhost  SYSTEM  RECURRING   NULL    1   MINUTE  2016-08-15 13:37:54     NULL    ENABLED     1   utf8    utf8_general_ci     utf8_general_ci

我在本地工作，不是现场

有什么想法吗？

提前致谢! :)

Answer 1

我建议打开声纳。我的个人资料页面上挂着 3 个事件链接。所以我创建了一些帮助表(也可以在这些链接中看到)来帮助打开声纳以查看您的事件中发生了什么。请注意，您可以像我在这些链接中所做的那样扩展它以进行性能跟踪。

请记住，事件的成功或失败(在您的脑海中)基于数据，并且它们是默默地进行的。但是跟踪正在发生的事情，你可以在其中发展时大大提高你的幸福水平。

事件:

DROP EVENT IF EXISTS move_to_archive_category;
DELIMITER $$
CREATE EVENT move_to_archive_category
  ON SCHEDULE EVERY 1 MINUTE STARTS '2015-09-01 00:00:00'
  ON COMPLETION PRESERVE
DO
BEGIN
    DECLARE incarnationId int default 0;
    DECLARE evtAlias varchar(20);

    SET evtAlias:='move_2_archive';
    INSERT incarnations(usedBy) VALUES (evtAlias);
    SELECT LAST_INSERT_ID() INTO incarnationId; 

    INSERT EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged)
    SELECT incarnationId,evtAlias,1,'Event Fired, begin looking',now();

    INSERT INTO `oc_product_to_category` (product_id, category_id) 
    SELECT product_id, 68 as category_id 
    FROM oc_product p WHERE p.event_end < NOW() AND p.event_end <> '0000-00-00';

    -- perhaps collect metrics for above insert and use that in debugMsg below
    -- perhaps with a CONCAT into a msg
    INSERT EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged)
    SELECT incarnationId,evtAlias,10,'INSERT finished',now();

    -- pretend there is more stuff
    -- ... 
    -- ... 

    INSERT EvtsLog(incarnationId,evtName,step,debugMsg,dtWhenLogged)
    SELECT incarnationId,evtAlias,99,'Event Finished',now();

END $$
DELIMITER ;

表格:

create table oc_product_to_category
(   product_id INT not null,
    category_id INT not null
);

create table oc_product
(   product_id INT not null,
    event_end datetime not null
);

drop table if exists incarnations;
create table incarnations
(   -- NoteA
    -- a control table used to feed incarnation id's to events that want performance reporting.
    -- The long an short of it, insert a row here merely to acquire an auto_increment id
    id int auto_increment primary key,
    usedBy varchar(50) not null
    -- could use other columns perhaps, like how used or a datetime
    -- but mainly it feeds back an auto_increment
    -- the usedBy column is like a dummy column just to be fed a last_insert_id()
    -- but the insert has to insert something, so we use usedBy
);

drop table if exists EvtsLog;
create table EvtsLog
(   id int auto_increment primary key,
    incarnationId int not null, -- See NoteA (above)
    evtName varchar(20) not null,   -- allows for use of this table by multiple events
    step int not null,  -- facilitates reporting on event level performance
    debugMsg varchar(1000) not null,
    dtWhenLogged datetime not null
    -- tweak this with whatever indexes your can bear to have
    -- run maintenance on this table to rid it of unwanted rows periodically
    -- as it impacts performance. So, dog the rows out to an archive table or whatever.
);

开启事件:

show variables where variable_name='event_scheduler'; -- OFF currently
SET GLOBAL event_scheduler = ON; -- turn her on
SHOW EVENTS in so_gibberish; -- confirm

确认 Evt 正在触发:

SELECT * FROM EvtsLog WHERE step=1 ORDER BY id DESC; -- verify with our sonar

有关这些帮助表的更多详细信息，请访问我的事件个人资料页面上的这些链接。几乎只是性能跟踪和报告的一个链接。

您还会注意到，在您最初关注的实际表格中有任何数据时，这并不重要。这可以稍后出现，并且可以通过将自定义字符串 CONCAT 执行到字符串变量(用于计数等)来在 evt 日志表中进行报告。并在第 # 步(例如第 10 步或第 20 步)中进行报告。

关键是，如果没有这样的东西，你完全是瞎子，不知道发生了什么。