CODEWITHSUNDEEP
mysqlmysql-workbench
Alpha2k
Alpha2k

Reputation: 2241

Retrieving error for failed event

I'm trying to find out what happen if an event failed, this is my event sql:

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 does an backup of the calls the day before. This event will fail for sure, but I'd like to know the error, something like:

`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

Now event is inserting this:

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 couldn't manage to run this event since I'm getting error on both END;, here are some screenshots from mysql-workbench:

Test Test2

Update: Tried changing second END from END; $$ to END; , workbench wont display any error but it wont run the query either.

Update 2: Changed line insert ... 'unknown') to insert ... 'unknown'); , added ; at end of line. The query now runs, but im getting another error: Error Code: 1319. Undefined CONDITION: EXCEPTION

Upvotes: 4

Views: 2004

Answers (2)

Alpha2k
Alpha2k

Reputation: 2241

This is the correct query:

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 the error handling wont be possible. "Inserting" the error text from an exception is only possible from version 5.6 on, references:

Getting SQLEXCEPTION message in procedures MySQL 5.5.x

MySQL Stored Procedure Error Handling

Upvotes: 1

Nighthunter22
Nighthunter22

Reputation: 273

About the error in the END, is only 

> Your code
> ; //this ; is used to close the insert i think
> END $$
> DELIMITER ;

(do not put ';'), try that, it should work now

Upvotes: 0

Related Questions