Shulou(Shulou.com)05/31 Report--

This article is about how mysql creates event and saves event to execute history. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Create a save information table

Click (here) to collapse or open

CREATE TABLE mysql.t_event_history (

Dbname VARCHAR (128) NOT NULL DEFAULT

Eventname VARCHAR (128) NOT NULL DEFAULT

Starttime DATETIME NOT NULL DEFAULT 0000-00-0000: 00:00

Endtime DATETIME DEFAULT NULL

Issuccess INT (11) DEFAULT NULL

Duration INT (11) DEFAULT NULL

Errormessage VARCHAR (512) DEFAULT NULL

Randno INT (11) DEFAULT NULL

PRIMARY KEY (dbname,eventname,starttime)

KEY ix_endtime (endtime)

KEY ix_starttime_randno (starttime,randno)

) ENGINE=INNODB DEFAULT CHARSET=utf8

two。 Create an event template

Click (here) to collapse or open

DELIMITER $$

CREATE EVENT job_del_expire_privileges ON SCHEDULE

# modify the following scheduling information

EVERY 10 minute STARTS current_timestamp ON COMPLETION PRESERVE ENABLE DO

BEGIN

DECLARE r_code CHAR (5) DEFAULT '00000'

DECLARE r_msg TEXT

DECLARE v_error INTEGER

DECLARE v_starttime DATETIME DEFAULT NOW ()

DECLARE v_randno INTEGER DEFAULT FLOOR (RAND () * 100001)

INSERT INTO mysql.t_event_history (dbname,eventname,starttime,randno) VALUES (DATABASE (), 'job_del_expire_privileges', vaunted starttime no)

BEGIN

# exception handling segment

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

BEGIN

SET v_error = 1

GET DIAGNOSTICS CONDITION 1 r_code = RETURNED_SQLSTATE, r_msg = MESSAGE_TEXT

END

# here is the actual called user program procedure

CALL automaintain.pro_del_expire_request ()

END

UPDATE mysql.t_event_history SET endtime=NOW (), issuccess=ISNULL (v_error), duration=TIMESTAMPDIFF (SECOND,starttime,NOW ()), errormessage=CONCAT ('error=',r_code,', message=',r_msg), randno=NULL WHERE starttime=v_starttime AND randno=v_randno

END$$

DELIMITER

Thank you for reading! This is the end of this article on "how mysql creates event and saves event execution history". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.