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 specifying start or end times using time expressions and functions.
Notice
By default, EVENT is disabled. To enable it, execute the SET GLOBAL event_scheduler = 1; command. 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-compatible mode.
Syntax
CREATE
[DEFINER = user]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE] //Default is ENABLE
[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 you specify
EVERY intervalwithoutSTART 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. Ensure the execution time is less than the scheduling interval.
- The maximum execution time (24 hours) for one-time events cannot be set.
- Creating another event from an existing event is not recommended.
- 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 for 16 consecutive times, it will stop running.
Parameters
| Parameter | Description |
|---|---|
| IF NOT EXISTS | Indicates whether to create the event if it already exists. 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 | Defines 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 supported units include YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. |
| ON COMPLETION [NOT] PRESERVE | Defines whether to retain the event after it completes execution. By default, events are not retained (NOT PRESERVE), meaning they are automatically deleted after execution. |
| ENABLE, DISABLE, DISABLE ON SLAVE | Defines the state of the event. By default, events are created in the ENABLE state, meaning they are immediately enabled upon creation. DISABLE creates the event in a disabled state, and it will not execute automatically until manually enabled. DISABLE ON SLAVE is only applicable in replication 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 single SQL statements and BEGIN END for multiple SQL statements. |
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;