database Why the database cannot recognize timestamp when creating event

Question

I want to create an event to reset field starting from 2016-02-23 00:00:00

CREATE EVENT reset_count
ON SCHEDULE
EVERY 72 HOUR STARTS '2016-02-23 00:00:00'
ON COMPLETION PRESERVE
ENABLE
DO BEGIN
UPDATE table_1 SET count1 = 0;

And then, the following error appears: 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 7.

It seems that the database cannot recognize the '2016-02-23 00:00:00'. Why?

The `syntax to use near ''` points to an error at the end of the statement. It looks like it is only missing the `END` keyword to go with the `BEGIN` you have. — Michael Berkowski, Feb 24 '16 at 02:46
But the `BEGIN/END` are probably not needed in this case because you only execute one `UPDATE` statement in this event. They would be needed to group multiple statements. — Michael Berkowski, Feb 24 '16 at 02:47
When I typed UPDATE table_1 SET count1 = 0; and then press "enter", the error came immediately without allowing press any words anymore. — Keroro Chan, Feb 24 '16 at 02:52
Hello do you use phpmyadmin or not ? i have been in this issue too, i will give trick if you use phpmyadmin — Freddy Sidauruk, Feb 24 '16 at 03:03
Are you using the MySQL command line client? You likely need a delimiter http://stackoverflow.com/questions/10259504/delimiters-in-mysql — Michael Berkowski, Feb 24 '16 at 03:14

1000111 · Accepted Answer · 2016-02-24T04:44:47.020

1

You used BEGIN without END and may be a delimiter issue too.

Try the following :

DROP EVENT IF EXISTS `reset_count`;
CREATE EVENT `reset_count`
ON SCHEDULE EVERY 72 HOUR STARTS '2016-02-23 00:00:00'
ON COMPLETION PRESERVE
ENABLE
DO
UPDATE table_1 SET count1 = 0;

If a delimiter issue arises:

delimiter //
CREATE EVENT `reset_count`
ON SCHEDULE EVERY 72 HOUR STARTS '2016-02-23 00:00:00'
ON COMPLETION PRESERVE
ENABLE
DO
UPDATE table_1 SET count1 = 0;
//

EDIT:

Note:

Check whether event scheduler is on.

SELECT @@event_scheduler;
If the event scheduler is OFF then you need to set event_scheduler ON.

SET GLOBAL event_scheduler = ON;

More:

BEGIN ... END syntax is used for writing compound statements, which can appear within stored programs (stored procedures and functions, triggers, and events). A compound statement can contain multiple statements, enclosed by the BEGIN and END keywords.

Reference

edited Feb 24 '16 at 04:44

answered Feb 24 '16 at 03:14

1000111

13,169
2
28
37

Please let me know if this works for you @Keroro Chan – 1000111 Feb 24 '16 at 03:30
The first one works. But, when do I need "BEGIN/END"? – Keroro Chan Feb 24 '16 at 04:09
Usually `BEGIN` `END` is used in stored programs. You must use `BEGIN` ....`END` if you write compound statements. For single statement it is optional. But you cannot use `BEGIN` without `END`. @KeroroChan – 1000111 Feb 24 '16 at 04:12
1

I got it. Can you give an example what are compound statements? – Keroro Chan Feb 24 '16 at 04:16
Suppose you have another event the purposes of which are to insert some value into `table_1` and to update some rows in `table_2`. Then these two statements (`insert `and `update`) would be considered `compound statements`. – 1000111 Feb 24 '16 at 04:25
I tested the first code you provided. The system does not reset the field, but the event is there. How come? – Keroro Chan Feb 24 '16 at 04:27
the field will be reset when the event is scheduled. Look the event is scheduled in every 72 hours interval starting from '2016-02-23 00:00:00'. Since the start date is already a past date so the next schedule is 2016-02-26 00:00:00. – 1000111 Feb 24 '16 at 04:33

database Why the database cannot recognize timestamp when creating event

1 Answers1