Getting error for a failed event
I am trying to figure out what happens if the event fails, this is my sql event:
DELIMITER $$
CREATE EVENT IF NOT EXISTS `cdr2015_daily_update`
ON SCHEDULE EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 3 HOUR)
DO
BEGIN
DECLARE EXIT HANDLER FOR MYSQLEXCEPTION, MYSQLWARNING
BEGIN
insert into events_state values ('cdr2015_daily_update', 'true', now(), 'unknown', 'unknown')
END;
insert into
cdr2015_v2 (clid, src, dst, dcontext, channel, dstchannel)
select
calldate, clid, src, dst, dcontext, channel, dstchannel
from cdr where DATE_FORMAT(calldate, '%Y-%m-%d') = subdate(current_date, 1); -- yesterday calls
END; $$
DELIMITER ;
Every day at 03:00, calls are backed up the day before. This event will fail, but I would like to know about the error, for example:
`Error Code: 1136. Column count doesn't match value count at row 1` , is it possible to catch this error and insert it into another table?
This is the events_state table:
CREATE TABLE `events_state` (
`event` varchar(255) DEFAULT NULL,
`failed` varchar(255) DEFAULT NULL,
`fail_date` datetime,
`reason1` varchar(255) DEFAULT NULL,
`reason2` varchar(255) DEFAULT NULL,
`RID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`RID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
The event now inserts the following:
insert into events_state values ('cdr2015_daily_update', 'true', now(), 'unknown', 'unknown')
Can it be changed to something like this?
insert into events_state values ('cdr2015_daily_update', 'true', now(), MYSQLEXCEPTION, MYSQLWARNING)
Also I was unable to fire this event as I am getting error on both END;
, here are some screenshots from mysql-workbench:
Update: . Tried changing the second one END
from END; $$
to END;
, the workbench won't display any errors, but it won't run the query.
Update 2: Changed line insert ... 'unknown')
up insert ... 'unknown');
, added ;
at the end. The request is now running, but im getting another error:Error Code: 1319. Undefined CONDITION: EXCEPTION
source to share
This is the correct request:
SET GLOBAL event_scheduler = ON;
DELIMITER $$
CREATE EVENT IF NOT EXISTS `cdr2015_daily_update`
ON SCHEDULE EVERY 1 DAY
STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 3 HOUR)
DO
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
insert into events_state values ('cdr2015_daily_update', 'true', now(), 'unknown', 'unknown', 0);
END;
insert into
cdr2015 (calldate, clid, src, dst, dcontext, channel, dstchannel)
select
calldate, clid, src, dst, dcontext, channel, dstchannel
from cdr where DATE_FORMAT(calldate, '%Y-%m-%d') = subdate(current_date, 1)
limit 10;
END; $$
DELIMITER ;
My MySQL version is 5.1, so error handling won't be possible. "Inserting" an error text from an exception is only possible from version 5.6, links:
source to share