Error #1064 with CREATE EVENT

Question

I have a table:

CREATE TABLE test_time (`id` int(11) not null, `num` int(11) not null, PRIMARYKEY(`id`);

After that, I create an event:

CREATE
EVENT testEvent
ON
SCHEDULE EVERY 1 SECOND 
DO 
BEGIN
UPDATE
    test_time
SET
    num = num + 1 
WHERE 
    id = 1;

UPDATE
    test_time
SET
    num = num + 1 
WHERE 
    id = 2;
END

and MySQL said: (note: line 12 is id = 1; )

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 12

Why is error and how can I fix it?

What MySQL client are you using to create this? Did you [set an alternate `DELIMITER`](http://stackoverflow.com/questions/10259504/delimiters-in-mysql/10259528#10259528)? — Michael Berkowski, Jun 28 '14 at 02:51
You should change the delimiter before you `CREATE EVENT` with something like `DELIMITER $$` and change it back after the final `END` with `DELIMITER ;` — , Jun 28 '14 at 03:09
thanks @MichaelBerkowski, when I set alternate DELIMETER, MySQL said: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1", but event was created! And I don't know why! — Nghi Ho, Jun 28 '14 at 03:36
It must be on the resetting `delimiter` statement after last `end; $$` of the code. I am not sure how you placed in your code. — Ravinder Reddy, Jun 28 '14 at 12:01

score 0 · Answer 1 · answered Jun 28 '14 at 23:42

You need to change your delimiter before creating your event of the CREATE will stop at the first semi-colon. Once done you need to change it back.

CREATE TABLE if not exists test_time 
   (`id` int(11) not null, `num` int(11) not null, PRIMARY KEY(`id`));

drop event if exists testEvent;

Delimiter $$                 /* New delimiter set here */
CREATE EVENT testEvent
  ON SCHEDULE EVERY 1 SECOND 
DO 
  BEGIN
    UPDATE test_time SET num = num + 1 WHERE  id = 1;  /* old delimiter not actioned */
    UPDATE test_time SET num = num + 1 WHERE  id = 2;
  END $$                                               /* New delimiter to end CREATE */

delimiter ;            /* Reset delimiter */

Error #1064 with CREATE EVENT

1 Answers1