Table of Contents
CREATE EVENT statement is used to create and schedule a new event to be executed once or periodically by Valentina Server.
event_definition_statement : CREATE EVENT [IF NOT EXISTS] event_name [FOR [DATABASE] db_name] ON SCHEDULE shedule_definition [ ON COMPLETION [ NOT ] PRESERVE ] [ ENABLED | DISABLED ] [ COMMENT literal ] DO sql_statement shedule_definition : AT timestamp [+ INTERVAL interval_value ] | EVERY interval_value [STARTS timestamp] [ENDS timestamp] interval_value : integer_value time_keyword time_keyword : YEAR | MONTH | WEEK | DAY | HOUR | MINUTE | SECOND
IF NOT EXISTS
If an event named event_name already exists in the same schema, no action is taken, and no error results. (However, a warning is generated.)
NOTE: 'IF NOT EXISTS' syntax Valentina has taken from MySQL world.
The name of a new event. Must be unique in the scope of Valentina Server.
FOR [DATABASE] db_name
Specifies a database name as known to Valentina Server, for which this event is created.
Starting from v6.2 this can be skipped. In this case, the event is created on the “server-level” (not related to any database).
ON SCHEDULE schedule_definition
Specifies when, how often, and for how long the sql_statement defined for the event repeats. This clause takes one of two forms:
AT timestampclause is used for a one-time event
EVERYclause is used for actions that are to be repeated at a regular interval.
[STARTS timestamp] [ENDS timestamp]
Optional arguments of EVERY clause.
STARTS specifies when the event should start repeating. You can use
STARTS. Not using
STARTS is the same as using
ENDS specifies when the event should end repeating. You can use
ENDS. Not using
ENDS means that the event continues executing indefinitely.
[ON COMPLETION [NOT] PRESERVE]
Normally, once an event has expired, it is immediately dropped. You can override this behavior by specifying
ON COMPLETION PRESERVE. Using of
ON COMPLETION NOT PRESERVE specifies the default behavior to be explicit.
[ENABLE | DISABLE]
It allows for creating a new event in the disabled state. On default, a new event is created in the enabled state.
Specifies an optional comment string for this event.
sql_statement to be executed by this event.
- UP TO v9.7: before executing this command you must switch active database to be 'masterdb', for this you need use SQL command “USE master;”
timestamp. Timestamp value must include both the date and time. The timestamp must also be in the future — you cannot schedule an event to take place in the past.
- You may use
CURRENT_TIMESTAMPto specify the current date and time. In this case, the event acts as soon as it is created.
intervalvalue consists of two parts, a quantity and a unit of time, and follows the same syntax rules that cover intervals used in the DATE_ADD() function.
- You can also combine intervals. For example,
AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAYis equivalent to “three weeks and two days from now”. Each portion of such a clause must begin with + INTERVAL.
This example creates an event that will do a backup of database 'db1' each midnight.
USE master; // not required since v9.7 CREATE EVENT backup_db1_24 FOR db1 ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP '2007-01-25 23:59:59' DO BACKUP DATABASE
This example increments field f1 in all records of table T1
USE master; // not required since v9.7 CREATE EVENT myevent FOR db1 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE T1 SET f1 = f1 + 1;
This example creates an event that will do a backup of database db1 each midnight and send notifications by email.
USE master; // not required since v9.7 CREATE EVENT backup_db1_24 FOR db1 ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP '2007-01-25 23:59:59' DO BEGIN BACKUP DATABASE WITH DIAGNOSE; SET subject = 'Backup ' + NOW() + ' OK.'; __MAIL __FROM 'email@example.com' __TO 'firstname.lastname@example.org' __SUBJECT subject __BODY '' __SMTP 'smtp://localhost' __PORT 25 __USER 'email@example.com' __PASSWORD '123456' __SSL true; EXCEPTION WHEN OTHERS THEN SET subject = 'Backup ' + NOW() + ' FAILED.'; SET errorMsg = HEX(@@LastErrorNumber) + ' ' + @@LastErrorMessage; __MAIL __FROM 'firstname.lastname@example.org' __TO 'email@example.com' __SUBJECT subject __BODY errorMsg __SMTP 'smtp://localhost' __PORT 25 __USER 'firstname.lastname@example.org' __PASSWORD '123456' __SSL true; END