CODEWITHSUNDEEP
mysql
benbenben
benbenben

Reputation: 159

Cannot create event scheduler on mysql

Hi I am trying to create an event scheduler but I always get this error and I don't know why

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END SET id = 0 WHILE id' at line 8

below is my code

DELIMITER |

CREATE EVENT `reset_kizuki` 
ON SCHEDULE EVERY 1 MONTH STARTS '2017-02-01 00:00:00' 
DO 
BEGIN


DECLARE id INTEGER
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END
SET id = 0

WHILE id < 12 DO
UPDATE kizuki 
SET methodID_1 = 0, method1_date = NULL, methodID_2 = 0, 
method2_date = NULL, is_PRM = 0 WHERE user_id = id
SET id = id + 1
END WHILE

END |

DELIMITER ;

and I somehow cannot put any ';' in my code

can anyone help me? Thank you

Upvotes: 0

Views: 667

Answers (2)

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4211

Create stored procedure first to make it more cleaner:

Create PROCEDURE proc_event_sample()
BEGIN
DECLARE id INTEGER;
SET id = 0;
while id<12 do
UPDATE kizuki  SET methodID_1 = 0, method1_date = NULL, methodID_2 = 0, 
method2_date = NULL, is_PRM = 0 where user_id = id;
SET id = id + 1;
end while;
END;

Then

CREATE EVENT `event_sample` 
ON SCHEDULE EVERY 1 SECOND STARTS '2016-04-21 00:00:00' 
ON COMPLETION NOT PRESERVE ENABLE DO 
call proc_event_sample()

You can test the procedure wisely by:

call proc_event_sample()

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17665

Your code syntaxs correctly for me (doesn't mean it works) with ; in all the right places 

drop event if exists  `reset_kizuki`;

DELIMITER |

CREATE EVENT `reset_kizuki` 
ON SCHEDULE EVERY 1 MONTH STARTS '2017-02-01 00:00:00' 
DO 
BEGIN


DECLARE id INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
SET id = 0;

WHILE id < 12 DO
UPDATE kizuki 
SET methodID_1 = 0, method1_date = NULL, methodID_2 = 0, 
method2_date = NULL, is_PRM = 0 WHERE user_id = id;
SET id = id + 1;
END WHILE;

END |

DELIMITER ;

Upvotes: 0

Related Questions