Purpose
This statement is used to create and schedule a new event.
Note
For OceanBase Database V4.2.5, the EVENT feature supports executing call statements to invoke stored procedures starting from V4.2.5 BP2. Additionally, when setting the start or end time, time expressions and functions are now supported.
Notice
By default, the EVENT feature is disabled. To enable it, execute the command SET GLOBAL event_scheduler = 1;. For more information about this parameter, see event_scheduler.
Privilege requirements
The user who executes this statement must have the CREATE privilege. For more information about privileges in OceanBase Database, 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] //An event is enabled by default after creation.
[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 and limitations
- If neither
START TIMEnorEND TIMEis set forEVERY interval, the event will run indefinitely. - For recurring events, if the execution time of an event exceeds the scheduling interval, multiple events may run simultaneously. Users must ensure that the execution time is shorter than the scheduling interval.
- You cannot set a maximum execution time (the maximum execution time for a one-time event is 24 hours).
- It is not recommended to create one event through another event.
- Once an event has started, its scheduling plan cannot be modified.
- Once an event has started, its
event_nameshould not be modified; otherwise, it may fail to end correctly. - If an event fails 16 consecutive times, it will stop running.
Parameters
| Parameter | Description |
|---|---|
| IF NOT EXISTS | Indicates that the event will not be created if it already exists. If an event exists and IF NOT EXISTS is not specified, an error will be thrown. |
| event_name | Specifies the name of the event to be created. |
| DEFINER | Optional. Specifies the user who executes the event. Currently, custom users are not supported, and the default is the current user. |
| ON SCHEDULE schedule | Defines the time and frequency for the event execution. Supports timestamp + INTERVAL, such as "2020-12-01 00:00:00" + INTERVAL 1 DAY. The time must be an integer, and supported time units include YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. |
| ON COMPLETION [NOT] PRESERVE | Defines whether to retain the event after it has finished execution. By default, events are not retained (NOT PRESERVE), meaning the event will be automatically deleted after execution. |
| ENABLE, DISABLE, DISABLE ON SLAVE | Defines the state of the event. By default, events are created with the ENABLE state, meaning they are enabled immediately after creation. DISABLE creates the event in a disabled state, and it will not execute automatically until manually enabled. DISABLE ON SLAVE is used only in replicated environments, and the event will not execute on the server. |
| COMMENT 'string' | Optional. Adds a comment to the event. |
| DO event_body | Defines the SQL statement to be executed. Currently supports a single SQL statement or multiple SQL statements enclosed in BEGIN END. |
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;