CREATE EVENT

Purpose

You can use this statement to create and schedule an event.

Privilege requirements

The user who executes this statement must have the CREATE privilege. For more information about OceanBase Database privileges, see Privilege types in MySQL mode.

Syntax

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE] // By default, an event is enabled after being created.
    [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}

Considerations

• If START TIME and END TIME are not specified for EVERY interval, the event will be executed infinitely.
• For a repeatable event, if its execution duration exceeds the scheduling interval, multiple identical events will be executed at the same time. You need to make sure that the execution duration is shorter than the scheduling interval.
• You cannot set a maximum execution duration. The maximum execution duration of one-off events is 24 hours.
• We recommend that you do not create an event based on another.
• You cannot modify the scheduling plan of an ongoing event.
• You cannot rename an ongoing event. Otherwise, the event cannot be correctly executed.
• If an event fails for 16 consecutive times, it will be stopped.

Parameters

Examples

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