Purpose
This statement is used to create and schedule a new event.
Note
Starting from OceanBase Database V4.2.5 BP2, the EVENT feature supports executing call statements to invoke stored procedures, and allows the use of time expressions and functions when setting the start or end time.
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 created in the ENABLE state
[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 run at the same time. You need to ensure that the execution time is less than the scheduling interval.
- The maximum execution time (24 hours) for one-time events cannot be set.
- It is not recommended to create one event based on 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 correctly. - If an event fails to execute 16 times consecutively, it will stop running.
Parameters
| Parameter | Description |
|---|---|
| IF NOT EXISTS | Specifies whether to create the event only if it does not already exist. 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 | An optional parameter that 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 supports YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. |
| ON COMPLETION [NOT] PRESERVE | Specifies whether to retain the event after it has completed execution. 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 created in the ENABLE state, meaning it will be immediately enabled upon creation. If the event is created in the DISABLE state, it will not execute automatically until manually enabled. If the event is created in the DISABLE ON SLAVE state, it will not execute on the server. |
| COMMENT 'string' | An optional parameter that adds a comment to the event. |
| DO event_body | Specifies the SQL statement to be executed. Currently, single SQL statements and multiple SQL statements within BEGIN END are supported. |
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;