Purpose
This statement is used to create and schedule a new event.
Note
Starting from OceanBase Database V4.3.5 BP2, the EVENT feature supports executing call statements to invoke stored procedures, and allows specifying start or end times using time expressions and functions.
Notice
By default, EVENT is disabled. You need to execute the SET GLOBAL event_scheduler = 1; command to enable it. For more information about this configuration item, see event_scheduler.
Privilege requirements
The user executing 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] // By default, the event is enabled 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
- If
EVERY intervalis specified withoutSTART TIME, the event will run indefinitely. - For recurring events, if the execution time exceeds the scheduling interval, multiple events may execute at the same time. Ensure the execution time is less than the interval.
- The maximum execution time for a one-time event is 24 hours.
- It is not recommended to create one event to trigger another.
- 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 properly. - If an event fails to execute 16 times consecutively, it will stop running.
Parameters
| 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 execute. Supports timestamp + INTERVAL, such as "2020-12-01 00:00:00" + INTERVAL 1 DAY. The time must be an integer, and the time unit can be YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND. |
| ON COMPLETION [NOT] PRESERVE | Specifies whether to retain the event after it completes. By default, events are not retained (NOT PRESERVE), meaning they are automatically deleted after execution. |
| ENABLE, DISABLE, DISABLE ON SLAVE | Specifies the state of the event. By default, the event is enabled after creation (ENABLE), meaning it starts immediately. If DISABLE is specified, the event is disabled and will not execute until manually enabled. DISABLE ON SLAVE is only valid in a replication environment, 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 be executed. Currently supports single SQL statements and multiple statements within 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;