MySQL—监控定时任务执行存储过程，返回结果存表

在项目中遇到需要从ODS层数据提取转换到DW层数据，采用存储过程+定时JOB的方式来实现目标。

这个时候又提出一个需求，监控数据转换存储过程，如有异常需及时排故。

代码如下：

首先建立针对存储过程运行情况的监控表：

CREATE TABLE `dm_proc_log` (
  `UUID` varchar(36) COLLATE utf8mb4_bin NOT NULL COMMENT '主键',
  `PROC_NAME` varchar(60) COLLATE utf8mb4_bin DEFAULT '' COMMENT '存储过程名称',
  `START_TIME` datetime DEFAULT NULL COMMENT '开始时间',
  `END_TIME` datetime DEFAULT NULL COMMENT '结束时间',
  `EXEC_COST_TIME` float DEFAULT NULL COMMENT '执行时间',
  `EXEC_STATUS` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '执行状态',
  `REMARK` varchar(200) COLLATE utf8mb4_bin DEFAULT '' COMMENT '异常记录',
  PRIMARY KEY (`UUID`),
  KEY `IDX_START_TIME` (`START_TIME`),
  KEY `IDX_END_TIME` (`END_TIME`)
) ENGINE=InnoDB 
DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_bin 
COMMENT='存储过程监控日志';

然后需要在存储过程中加监控代码，

首先声明几个后面会用到的变量 以及 异常处理的语法。

关于返回结果的编码值returned_sqlstate可以在MySQL的官网查阅，然后根据自己实际需求更改。

我这里简单粗暴输出了所有的异常，因为'0000'代表运行正常。

DROP PROCEDURE IF EXISTS 存储过程命名;

CREATE PROCEDURE 存储过程命名()
BEGIN
    DECLARE RESULT_CODE CHAR(5) DEFAULT '00000';
    DECLARE MSG TEXT;
    DECLARE V_EXEC_SECOND FLOAT;
    DECLARE V_UUID VARCHAR(36);
    DECLARE V_BEGIN_TIME DATETIME;
    DECLARE V_END_TIME DATETIME;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
            GET DIAGNOSTICS CONDITION 1
                RESULT_CODE = returned_sqlstate ,MSG= MESSAGE_TEXT; 
        end;


    SET V_UUID = UUID();
    SET V_BEGIN_TIME = NOW();
    INSERT INTO dm_proc_log(UUID, PROC_NAME, START_TIME)
    VALUES (V_UUID, '这里输入存储过程命名', V_BEGIN_TIME);

    START TRANSACTION ; 

    # -----------
    # 业务逻辑部分，INSERT、UPDATE、DELETE
    # -----------
    
    
    IF RESULT_CODE != '00000' THEN
        ROLLBACK;

        SET V_END_TIME = NOW();
        SET V_EXEC_SECOND = TIMESTAMPDIFF(SECOND, V_BEGIN_TIME, V_END_TIME);

        UPDATE dm_proc_log
        SET END_TIME=V_END_TIME,
            EXEC_STATUS='N',
            EXEC_COST_TIME=V_EXEC_SECOND,
            REMARK=MSG
        WHERE UUID = V_UUID;
    ELSE
        COMMIT;
        SET V_END_TIME = NOW();
        SET V_EXEC_SECOND = TIMESTAMPDIFF(SECOND, V_BEGIN_TIME, V_END_TIME);

        UPDATE dm_proc_log
        SET END_TIME=V_END_TIME,
            EXEC_STATUS='Y',
            EXEC_COST_TIME=V_EXEC_SECOND,
            REMARK=''
        WHERE UUID = V_UUID;
    end if;
    COMMIT;
end;

加完监控语句之后，可以先运行一下存储过程，然后看存储过程监控表是否有数据。

虽然写存储过程对于需求修改很方便，但是维护起来是真的麻烦，所以尽量每个存储过程修改之前进行备份，修改完都进行测试。

第二步就是用定时任务JOB来控制存储过程的运行。

DROP EVENT IF EXISTS 这里输入定时任务名称;

CREATE EVENT 这里输入定时任务名称
    ON SCHEDULE every 1 DAY
        starts '2021-04-23 04:00:00'
    on completion not preserve
    enable
    DO CALL 这里输入存储过程名称();

定时任务其实就只有一句Call 存储过程()，但是时间的设定需要注意服务器的时间是世界时(UTC)还是北京时。

如果存储过程较多的话同时又需要注意防范死锁问题。