MySQL Event历史记录

2019-04-06 07:24

短信预约 信息系统项目管理师 报名、考试、查分时间动态提醒

需求 SQL Server的作业历史（Job）记录是保存在msdb库中的，很方便就查询相关的Job定义，计划和历史记录，而MySQL的event却没有历史记录。为方便查看event是否正常执行以及执行结果，通过以下两个步骤来实现类似的功能。实现 1. 在mysql库创建event执行的历史记录表

CREATE TABLE `mysql`.`udf_event_history` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`event_gid` varchar(36) NOT NULL,
`db_name` varchar(128) NOT NULL DEFAULT "",
`event_name` varchar(128) NOT NULL DEFAULT "",
`start_time` datetime(3) NOT NULL DEFAULT current_timestamp(),
`end_time` datetime(3) DEFAULT NULL,
`is_success` tinyint(4) DEFAULT 0,
`duration` decimal(15,3) DEFAULT NULL,
`error_msg` varchar(512) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_event_git` (`event_gid`),
KEY `idx_db_event_name` (`db_name`,`event_name`),
KEY `idx_s_e_time` (`start_time`,`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2．根据以下建模板创建event

请注意根据实际情况修改相关信息

USE db1;

DELIMITER $$

CREATE DEFINER=`root`@`localhost` EVENT `event_test1` 
ON SCHEDULE EVERY 1 MINUTE STARTS "2019-01-01 00:00:00" 
ON COMPLETION PRESERVE ENABLE DO 
BEGIN  
    DECLARE r_code CHAR(5) DEFAULT "00000";  
    DECLARE r_msg TEXT;  
    DECLARE v_error INT;  
    DECLARE v_start_time DATETIME(3) DEFAULT NOW(3);
    DECLARE v_event_gid VARCHAR(36) DEFAULT UPPER(REPLACE(UUID(),"-",""));  
    
    
    INSERT INTO mysql.udf_event_history (db_name, event_name, start_time, event_gid)  
    VALUES(DATABASE(), "event_test1", v_start_time, v_event_gid);    
      
    BEGIN     
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION    
        BEGIN  
            SET  v_error = 1;  
            GET DIAGNOSTICS CONDITION 1 r_code = RETURNED_SQLSTATE, r_msg = MESSAGE_TEXT;  
        END;  
        
        CALL db1.usp_1();
    END;  
      
    UPDATE mysql.udf_event_history 
    SET end_time = NOW(3), is_success = ISNULL(v_error), duration = TIMESTAMPDIFF(microsecond,start_time, NOW(3)) / 1000000, 
    error_msg = CONCAT("error = ", r_code,", message = ", r_msg)
    WHERE event_gid = v_event_gid;  
      
END$$  
DELIMITER ;

通过查询mysql.udf_event_history表，可以知道event的开始结束时间、是否成功、执行时长、错误信息，等，为管理日常调度计划提供方便。

root@localhost [db1]select * from mysql.udf_event_history limit 2G;
*************************** 1. row ***************************
        id: 1
   db_name: db1
event_name: event_test1
start_time: 2019-12-03 15:44:00.000
  end_time: 2019-12-03 15:44:00.001
is_success: 0
  duration: 0.001
 error_msg: error = 42000, message = PROCEDURE db1.usp_2 does not exist
 event_gid: AB305D8C15A011EAB822005056AB041E
*************************** 2. row ***************************
        id: 2
   db_name: db1
event_name: event_test2
start_time: 2019-12-03 15:46:00.000
  end_time: 2019-12-03 15:46:05.405
is_success: 1
  duration: 5.405
 error_msg: NULL
 event_gid: F2B6197C15A011EAB822005056AB041E