CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

How to tell if a mySql event succeeded or not?

I have around 5 mySql events that autorun 15 minutes after each is done.

They all update a table with sum() of votes, starting at 00:00.

Is there any way to tell if an event failed? If it failed the other events need not run. By failed I mean internally (sql gone wild) or it didn't execute at all.

One way I thought of doing this, was to have the event called a stored procedure or function on its success, which in turn would call the others. Is something like this possible?

Upvotes: 1

Views: 2775

Answers (2)

BlitZ
BlitZ

Reputation: 12168

You may try to use EXIT HANDLER:

DROP EVENT IF EXISTS `myDB`.`myEvent`;

CREATE EVENT IF NOT EXISTS `myDB`.`myEvent`
ON SCHEDULE
    EVERY 15 MINUTE
COMMENT 'Some comment'
DO
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
    BEGIN
    -- 1. Flag something, that should be tested in chained events
    --    if they should be skipped. I.e.:

    --    INSERT INTO `myDB`.`EventTable` (`event`, `failed`, `when`)
    --    VALUES ('myEvent', TRUE, NOW());

    --    OR

    --    UPDATE
    --        `myDB`.`EventTable`
    --    SET
    --        `failed` = TRUE,
    --        `when`   = NOW()
    --    WHERE
    --        `event` = 'myEvent';

    -- 2. Quit.
    END;

-- Do something, that may produce SQLEXCEPTION or SQLWARNING...
END;

Upvotes: 2

Devart
Devart

Reputation: 121902

Create a table where you could store information about the events. Then, add an INSERT statement in each event at the end of the code. The table need to store the information you need to check in events.

Upvotes: 2

Related Questions