CREATE EVENT

Description

This statement is used to create and schedule a new event.

info

In seekdb, the EVENT supports executing the call statement to invoke stored procedures, and supports time expressions and functions when setting the start or end time.

tip

By default, EVENT is not enabled. You need to execute the SET GLOBAL event_scheduler = 1; command to enable it. For more information about this configuration item, seeevent_scheduler.

Privilege requirements

The user executing this statement must have the CREATE privilege. For more information about seekdb privileges, see seekdb's privilege classification.

Syntax

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE] //Default is ENABLE
    [COMMENT 'string']
    DO event_body;

schedule: {
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
}

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Usage and considerations

• If EVERY interval is specified without START TIME, the event will run indefinitely.
• For recurring events, if the execution time exceeds the schedule interval, multiple events may run at the same time. Users must ensure the execution time is less than the schedule interval.
• The maximum execution time for a one-time event is 24 hours.
• It is not recommended to create another event within an event.
• Once an event has started, its schedule cannot be modified.
• Once an event has started, its name (event_name) should not be changed, as this may prevent it from ending correctly.
• If an event fails to execute 16 times consecutively, it will stop running.

Parameter description

Parameter	Description
IF NOT EXISTS	Specifies that if the event already exists, it should not be created. If the event exists and IF NOT EXISTS is not specified, an error will be returned.
event_name	Specifies the name of the event to be created.
DEFINER	Optional. Specifies the user who will execute the event. Currently, custom users are not supported, and the default is the current user.
ON SCHEDULE schedule	Specifies the time and frequency at which the event will run. Supports timestamp + INTERVAL, such as "2020-12-01 00:00:00" + INTERVAL 1 DAY. The timestamp must be an integer, and the time unit supports YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.
ON COMPLETION [NOT] PRESERVE	Specifies whether to retain the event after it completes. By default, the event is not retained (NOT PRESERVE), meaning it will be automatically deleted after execution.
ENABLE, DISABLE, DISABLE ON SLAVE	Specifies the state of the event. By default, the event is enabled (ENABLE) immediately after creation. DISABLE creates the event but disables it, so it will not execute until manually enabled. DISABLE ON SLAVE is only used in replication environments, where the event will not execute on the server.
COMMENT 'string'	Optional. Adds a comment to the event.
DO event_body	Specifies the SQL statement to execute. Currently supports single SQL statements and BEGIN END for multiple SQL statements.

Examples

CREATE EVENT myevent
    ON SCHEDULE AT '2024-07-01 00:00:00' + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;