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(),'-',''));  
    
    /*修改為實際的event名*/
    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 2\G;
*************************** 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

參考