Events

An event is a predefined SQL task that can be automatically executed at the specified time or at an specified interval. Each event consists of the trigger condition (namely, scheduling plan) and SQL statement or stored procedure to execute. Event definitions are stored in the database and maintained together with the database to ensure that the events can be executed as scheduled even after the server is restarted.

In a trigger, SQL execution is triggered by data operations. In an event, SQL execution is triggered over time. An event allows an SQL statement or stored procedure to be automatically executed based on the predefined schedule. This feature significantly enhances the automatic processing capabilities of databases and applies to various scenarios such as periodic data maintenance, statistics summary, and scheduled notification.

Features

• Scheduling rules:

  • By time: You can specify to schedule an event at a specific point in time.
  • Periodically: You can specify to schedule an event at an interval of the second, minute, hour, day, or month level.
  • Complex scheduling: You can use a combination of keywords such as EVERY, STARTS, and ENDS to implement complex scheduling, for example, to schedule an event only on workdays or during certain periods.

• SQL support:

  • Any valid SQL statement, including INSERT, UPDATE, and DELETE, can be executed through an event.
  • Events can be executed as transactions, with the atomicity and consistency of transaction execution ensured.

• Dynamic control:

  • You can dynamically enable or disable the event scheduler without the need to restart the services.
  • You can modify and delete existing events based on business needs.

Enable or disable the event scheduler

An event is executed by the thread of a special event scheduler. You can enable or disable the event scheduler as follows:

• Enable the event scheduler.

  obclient> SET GLOBAL event_scheduler = ON;
  

• Disable the event scheduler.

  obclient> SET GLOBAL event_scheduler = OFF;
  

Considerations

• If START TIME and END TIME are not specified for EVERY interval, the event will be executed infinitely.
• For a repeatable event, if its execution duration exceeds the scheduling interval, multiple identical events will be executed at the same time. You need to make sure that the execution duration is shorter than the scheduling interval.
• You cannot set a maximum execution duration. The maximum execution duration of one-off events is 24 hours.
• We recommend that you do not create an event based on another.
• You cannot modify the scheduling plan of an ongoing event.
• You cannot rename an ongoing event. Otherwise, the event cannot be correctly executed.
• If an event fails for 16 consecutive times, it will be stopped.

References

You can use the CREATE EVENT, ALTER EVENT, and DROP EVENT statements to create, modify, and drop events.

• CREATE EVENT
• ALTER EVENT
• DROP EVENT