MySQL定时事件CopyTodayPrices未执行数据插入问题求助

问题背景 #

我创建了一个定时事件CopyTodayPrices，用于将Daily_Prices表的数据同步至Prices_History表，事件代码如下：

CREATE EVENT CopyTodayPrices
ON SCHEDULE EVERY 1 DAY
STARTS '2024-08-13 20:25:00'
ON COMPLETION NOT PRESERVE
ENABLE
DO
BEGIN
    -- Ignore if prices are old (if the unique index throws an error)
    INSERT IGNORE INTO Prices_History (`Kreditorennr`, `Bar Code`, `Tagespreis`, `Preis vom`)
    SELECT `Kreditorennr`, `Bar Code`, `Tagespreis`, `Preis vom`
    FROM Daily_Prices;
END;

但该事件未向Prices_History表插入任何数据。手动在DBeaver中执行事件内BEGIN到END之间的代码，可成功插入所有数据。

已确认的信息：

• 事件调度器正常运行：
  执行SHOW PROCESSLIST;结果显示：

• 事件已正确调度：
  执行SHOW EVENTS;结果显示：

排查步骤 #

1. 核对时区一致性 #

事件时区设置为SYSTEM，需确认数据库系统时区与你手动执行时的会话时区是否匹配：

-- 查看系统时区
SELECT @@system_time_zone;
-- 查看当前会话时区
SELECT @@session.time_zone;

若两者不一致，事件实际执行时间可能偏离预期的20:25:00，导致执行时Daily_Prices表还未生成当天数据。

2. 验证INSERT IGNORE的忽略逻辑 #

INSERT IGNORE会跳过所有插入错误（包括唯一键冲突、数据类型不兼容等），需确认：

• 查看Prices_History表的唯一约束，确认是否因重复数据导致所有插入被忽略：

SHOW CREATE TABLE Prices_History;

• 手动执行后检查Prices_History是否已包含Daily_Prices的全部数据，若已存在，事件执行时会因唯一键冲突跳过插入，这属于正常逻辑，但需确认事件执行前Daily_Prices是否有新增数据。

3. 检查事件执行的权限 #

事件定义者为root@%，需确认该用户是否拥有Daily_Prices的SELECT权限和Prices_History的INSERT权限（虽然root权限通常足够，但仍需排除权限差异）：

SHOW GRANTS FOR 'root'@'%';

4. 查看事件执行日志 #

启用MySQL通用日志，追踪事件执行的具体细节：

-- 临时开启通用日志
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log'; -- 替换为你的日志路径

等待事件执行后，查看日志文件，确认事件是否触发、执行的SQL是否存在错误。

5. 确认事件执行时Daily_Prices的数据状态 #

事件执行时间点（20:25:00），Daily_Prices可能为空（比如其他定时任务先清空或还未写入数据）。可以修改事件添加日志记录，验证执行时的数据集：

-- 先创建日志表
CREATE TABLE event_log (log_time DATETIME, event_name VARCHAR(100), row_count INT);

-- 修改事件代码
CREATE EVENT CopyTodayPrices
ON SCHEDULE EVERY 1 DAY
STARTS '2024-08-13 20:25:00'
ON COMPLETION NOT PRESERVE
ENABLE
DO
BEGIN
    DECLARE row_count INT;
    SELECT COUNT(*) INTO row_count FROM Daily_Prices;
    INSERT INTO event_log (log_time, event_name, row_count) VALUES (NOW(), 'CopyTodayPrices', row_count);
    
    INSERT IGNORE INTO Prices_History (`Kreditorennr`, `Bar Code`, `Tagespreis`, `Preis vom`)
    SELECT `Kreditorennr`, `Bar Code`, `Tagespreis`, `Preis vom`
    FROM Daily_Prices;
END;

事件执行后查看event_log，确认执行时间和查询到的行数，判断是否因Daily_Prices为空导致无数据插入。

6. 显式指定数据库名 #

事件调度器的默认数据库可能不是nocodb，导致执行时找不到表。修改SQL语句，显式添加数据库前缀：

INSERT IGNORE INTO nocodb.Prices_History (`Kreditorennr`, `Bar Code`, `Tagespreis`, `Preis vom`)
SELECT `Kreditorennr`, `Bar Code`, `Tagespreis`, `Preis vom`
FROM nocodb.Daily_Prices;

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