MySQL Event历史记录

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

drop table if exists `mysql`.`udf_event_history`;
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

 

参考