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 using time expressions and functions when setting the start or end time.
Notice
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, 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 run simultaneously. Ensure the execution time is less than the 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 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 | An optional parameter specifying 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, e.g., "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 will run immediately. If DISABLE is specified, the event will not run until manually enabled. DISABLE ON SLAVE is only used in replication environments, where the event will not run on the server. |
| COMMENT 'string' | An optional parameter to add a comment to the event. |
| DO event_body | Specifies the SQL statement to execute. 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;