为何我的MySQL每日定时事件cleanup_fights_1无法正常执行？

云原生阿岚

2026-7-1

问题描述

我创建了一个每日执行的MySQL事件cleanup_fights_1，但它无法按每日频率自动运行。手动执行事件中的SQL查询可以正常工作，相关配置及信息如下：

事件定义

CREATE EVENT cleanup_fights_1
    ON SCHEDULE
        EVERY 1 DAY STARTS '2020-01-12 12:00:00'
    DO
        DELETE f.* 
        FROM fights AS f 
        INNER JOIN (
            SELECT fighter1,fighter2,COUNT(*) as num_scorecards 
            FROM scorecards 
            WHERE private LIKE 'NO' AND flagged LIKE 'NO' 
            GROUP BY fighter1,fighter2 
            HAVING num_scorecards < 5 
            ORDER BY num_scorecards DESC) AS s 
        ON f.fighter1 = s.fighter1 
            AND f.fighter2 = s.fighter2 
            AND f.date > '2014-05-04';

事件状态（`show events`输出）

mysql> 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 |
+-------------------+------------------+--------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| rajohns_judgecard | cleanup_fights_1 | rajohns_08@% | SYSTEM    | RECURRING | NULL       | 1              | DAY            | 2020-01-12 12:00:00 | NULL | ENABLED |          0 | utf8                 | utf8_general_ci      | latin1_swedish_ci  |

事件创建语句（`show create event cleanup_fights_1`输出）

mysql> show create event cleanup_fights_1;
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Event            | sql_mode                                                                                                                                  | time_zone | Create Event                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | character_set_client | collation_connection | Database Collation |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| cleanup_fights_1 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | SYSTEM    | CREATE DEFINER=`rajohns_08`@`%` EVENT `cleanup_fights_1` ON SCHEDULE EVERY 1 DAY STARTS '2020-01-12 12:00:00' ON COMPLETION NOT PRESERVE ENABLE DO DELETE f.* FROM fights AS f INNER JOIN (SELECT fighter1,fighter2,COUNT(*) as num_scorecards FROM scorecards WHERE private LIKE 'NO' AND flagged LIKE 'NO' GROUP BY fighter1,fighter2 HAVING num_scorecards < 5 ORDER BY num_scorecards DESC) AS s ON f.fighter1 = s.fighter1 AND f.fighter2 = s.fighter2 AND f.date > '2014-05-04' | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

用户权限（`show grants`输出）

mysql> show grants;
+-----------------------------------------------------------+
| Grants for rajohns_08@%                                   |
+-----------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'rajohns_08'@'%'           |
| GRANT ALL PRIVILEGES ON `exampleDB`.* TO 'rajohns_08'@'%' |
+-----------------------------------------------------------+

事件调度器状态（`SHOW VARIABLES LIKE 'event_scheduler'`输出）

mysql> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.04 sec)

排查与解决方法

检查MySQL错误日志
事件执行失败的报错信息通常会写入MySQL错误日志，先定位日志路径：
```
SHOW VARIABLES LIKE 'log_error';
```
打开对应日志文件，搜索cleanup_fights_1关键词，查看是否有执行失败的具体原因。
验证时区一致性
事件调度时间依赖MySQL时区设置，若系统时区与MySQL时区不一致，可能导致执行时间异常：
```
-- 查看MySQL全局时区
SHOW GLOBAL VARIABLES LIKE 'time_zone';
-- 查看当前会话时区
SHOW SESSION VARIABLES LIKE 'time_zone';
```
Linux系统可通过cat /etc/timezone查看系统时区，若存在差异，修改MySQL配置文件my.cnf（或my.ini）统一时区：
```
default-time-zone = '+08:00'
```
修改后重启MySQL服务生效。

添加执行日志验证事件触发状态
手动执行SQL正常不代表事件实际触发，可给事件增加日志记录：

先创建日志表：

CREATE TABLE event_exec_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(100),
    exec_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    affected_rows INT
);

修改事件插入日志：

ALTER EVENT cleanup_fights_1
DO
BEGIN
    DELETE f.* 
    FROM fights AS f 
    INNER JOIN (
        SELECT fighter1,fighter2,COUNT(*) as num_scorecards 
        FROM scorecards 
        WHERE private LIKE 'NO' AND flagged LIKE 'NO' 
        GROUP BY fighter1,fighter2 
        HAVING num_scorecards < 5 
        ORDER BY num_scorecards DESC) AS s 
    ON f.fighter1 = s.fighter1 
        AND f.fighter2 = s.fighter2 
        AND f.date > '2014-05-04';
    INSERT INTO event_exec_log (event_name, affected_rows)
    VALUES ('cleanup_fights_1', ROW_COUNT());
END;

等待事件执行时间后，查看event_exec_log表：若有记录说明事件已执行，若无则需排查调度器触发逻辑。

检查MySQL版本兼容性
部分旧版本MySQL（如5.6早期版本）存在事件调度器bug，若使用版本较旧，建议升级至稳定版本，或查阅官方文档确认是否有相关修复记录。

内容的提问来源于stack exchange，提问作者Adam Johns

为何我的MySQL每日定时事件cleanup_fights_1无法正常执行？

问题描述 #

事件定义 #

事件状态（show events输出） #

事件创建语句（show create event cleanup_fights_1输出） #

用户权限（show grants输出） #

事件调度器状态（SHOW VARIABLES LIKE 'event_scheduler'输出） #

排查与解决方法 #