Scedule Event Mysql not accepting syntax BEGIN END

Question

I must have done something really stupid, but the following is correct:

CREATE EVENT delete_old
ON SCHEDULE
EVERY 1 DAY
COMMENT 'Clears old cache data from the DB.'
DO
DELETE FROM summoners 
WHERE `date` < (NOW() - INTERVAL 7 DAY);

Where the next bit seems to throw a syntax error on the last 2 lines:

CREATE EVENT delete_old
ON SCHEDULE
EVERY 1 DAY
COMMENT 'Clears old cache data from the DB.'
DO BEGIN
DELETE FROM summoners 
WHERE `date` < (NOW() - INTERVAL 7 DAY);
END;

The syntax to my knowledge is correct, however MySQL Workbench does not agree. I intend to do multiple tables inside the BEGIN - END section, that is why i need it.

I hope someone can figure out what goes wrong here, i am at a loss. Thanks in advance, Smiley

Answer 1

You have to change the DELIMITER to something that doesn't appear in your event body.

I just tried it (MySQL Workbench 6.0.6, MySQL 5.6) and it works fine. Here's a screenshot:

Answer 2

It's because ; in body breaks your command in the middle. Use different delimiter.

DELIMITER | 

CREATE EVENT delete_old
ON SCHEDULE
EVERY 1 DAY
COMMENT 'Clears old cache data from the DB.'
DO BEGIN
DELETE FROM summoners 
WHERE `date` < (NOW() - INTERVAL 7 DAY);
END;

| DELIMITER ;