计划任务未运行 [英] Scheduled task doesn't run

问题描述

CREATE EVENT `set_trips_finished`
  ON SCHEDULE EVERY 1 DAY STARTS '2015-08-25 01:50:00'
  ON COMPLETION PRESERVE
DO BEGIN

  UPDATE trips
  SET status = 0
  WHERE date(created_at) < curdate();

END;

是计划任务.但是这些字段不会更新.当我只运行查询时 - 字段更新得很好.

is the scheduled task. But the fields don't get updated. When I run just the query - the fields get updated fine.

我有另一个计划任务，语法完全相同，只是计划在 5 分钟后运行，并且运行良好.

I have another scheduled task, with the very same syntax, just scheduled to run a 5 minutes later, and it runs fine.

我不明白为什么这个任务不会运行，或者查询是否没有更新表......有什么建议吗?

I don't understand why wouldn't this task run, or whether the query does not update the table... Any suggestions?

更新

我删除了另一个计划任务(正在运行的任务)，然后重新设置它们，现在都没有触发...

I deleted the other scheduled task (the one that was working), and set them both again, and now neither fires...

推荐答案

查看事件调度程序是否正在运行:

See if event scheduler is even running:

show variables where variable_name='event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+

不

创建我的测试表:

create table trips
(   id int auto_increment primary key,
    status int not null,
    created_at date not null
);
insert trips(status,created_at) values (0,'2014-09-09');

创建我的活动:

DELIMITER $$
CREATE EVENT `set_trips_finished`
  ON SCHEDULE EVERY 1 MINUTE STARTS '2015-08-23 00:00:00'
  ON COMPLETION PRESERVE
DO BEGIN

  UPDATE trips
  SET status = status+1
  WHERE date(created_at) < curdate();

END;$$
DELIMITER ;

按架构名称列出所有事件:

List all events by schema name:

show events from so_gibberish;

or

show events\G; -- <--------- I like this one from mysql> prompt
show events; -- <--------- from workbench / sqlyog


*************************** 1. row ***************************
                  Db: so_gibberish
                Name: set_trips_finished
             Definer: GuySmiley@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2015-08-23 00:00:00
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci

查看可能会更新状态的数据:

Look at data that might get status updated:

select * from trips;
+----+--------+------------+
| id | status | created_at |
+----+--------+------------+
|  1 |      0 | 2014-09-09 |
+----+--------+------------+

好吧，我可以等一整天，事件甚至都没有开启

Well I can wait all day long, events aren't even turned on

SET GLOBAL event_scheduler = ON;    -- turn her on

show variables where variable_name='event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+

等几分钟(注意我的活动每分钟都在运行)

Wait a few minutes (note my event runs every minute)

select * from trips;
+----+--------+------------+
| id | status | created_at |
+----+--------+------------+
|  1 |      3 | 2014-09-09 |
+----+--------+------------+

活动已运行 3 次.好的，看起来不错.

Event has run 3 times. Ok, looks good.

SET GLOBAL event_scheduler = OFF;   -- turn her off if so desired

这篇关于计划任务未运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！