EVENT

An event is a predefined SQL task that runs automatically at a specific time or at regular intervals. Each event includes trigger conditions (a scheduling plan) and the SQL statements or stored procedures to be executed. Event definitions are stored in the database and managed alongside it, ensuring they execute as scheduled even after a server restart.

Unlike triggers, which execute SQL in response to data operations, events are triggered by time-based schedules. They enable users to automate the execution of SQL statements or stored procedures based on a predefined timeline. This functionality greatly enhances the database's automation capabilities, making it ideal for tasks such as routine data maintenance, statistical reporting, and scheduled notifications.

Characteristics:

• Scheduling rules:

  • Specific time execution: Events can be scheduled to execute once at an exact point in time.
  • Periodic execution: Supports repeated execution at intervals such as seconds, minutes, hours, days, or months.
  • Complex scheduling: By combining keywords like EVERY, STARTS, and ENDS, complex scheduling logic can be implemented, such as executing only on weekdays or within a specific date range.

• SQL support:

  • You can execute any valid SQL statements in an EVENT, including data insertion, update, and deletion.
  • Supports transaction handling to ensure atomicity and consistency of event execution.

• Dynamic control:

  • The event scheduler can be dynamically enabled or disabled at runtime without restarting the service.
  • Supports modification and deletion of existing events to adapt to changing business requirements.

Privileges requirements

To use events, you must have the GRANT EVENT privilege. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

Start and stop an event

Events are executed by a special event scheduler thread, which can be enabled or disabled using the following methods.

• Start the event scheduler:

  obclient> SET GLOBAL event_scheduler = ON;
  

• Stop the event scheduler:

  obclient> SET GLOBAL event_scheduler = OFF;
  

Considerations

• If neither START TIME nor END TIME is set for EVERY 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_name should not be modified; otherwise, it may fail to end correctly.
• If an event fails 16 consecutive times, it will stop running.