Detailed explanation of mysql scheduled tasks (event events)

1. Brief introduction of the event

An event is a procedural database object that MySQL calls at a specific time. An event can be called once or started periodically. It is managed by a specific thread, the so-called "event scheduler".

Events are similar to triggers in that they are triggered when something happens. A trigger is fired when a statement is started on the database, whereas an event is fired based on a scheduled event. Because of their similarity to each other, events are also called temporary triggers.

Events replace the work that could previously only be performed by the operating system's scheduled tasks, and MySQL's event scheduler can accurately execute one task per second, while the operating system's scheduled tasks (such as CRON under Linux or task scheduling under Windows) can only be executed once a minute.

2 Advantages and disadvantages of events

2.1 Advantages

Some scheduled operations on data no longer rely on external programs, but directly use the functions provided by the database itself.
It is possible to execute a task every second, which is very practical in some environments with high real-time requirements.

2.2 Disadvantages

Timed trigger, cannot be called.

3 Create events

A create event statement creates an event. Each event consists of two main parts. The first part is the event schedule, which indicates when the event is started and at what frequency.

The second part is the event action, which is the code executed when the event is triggered. The event action contains a SQL statement, which may be a simple insert or update statement, or a stored procedure or
benin...end statement block, these two situations allow us to execute multiple SQL statements.

An event can be active (open) or inactive (closed), active means the event scheduler checks if the event action must be called, inactive means the declaration of the event is stored in the catalog but the scheduler does not check if it should be called. After an event is created, it immediately becomes active. An active event can be executed once or multiple times.

3.1 The creation syntax is as follows

  CREATE 
    [DEFINER = { user | CURRENT_USER }] 
    EVENT 
    [IF NOT EXISTS] 
    event_name 
    ON SCHEDULE schedule 
    [ON COMPLETION [NOT] PRESERVE] 
    [ENABLE | DISABLE | DISABLE ON SLAVE] 
    [COMMENT 'comment'] 
    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}

Glossary:

event_name: The name of the event to be created (uniquely determined).
ON SCHEDULE: Schedule the task.
schedule: determines the execution time and frequency of the event (note that the time must be in the future, the past time will be wrong). There are two forms: AT and EVERY.
[ON COMPLETION [NOT] PRESERVE]: Optional. The default is ON COMPLETION NOT PRESERVE, which means that the event will be automatically dropped after the scheduled task is executed; ON COMPLETION PRESERVE will not drop it.
[COMMENT 'comment']: Optional, comment is used to describe the event; equivalent to a comment, with a maximum length of 64 bytes.
[ENABLE | DISABLE]: Set the status of the event. The default setting is ENABLE: the system attempts to execute this event. DISABLE: turns off the event. You can use alter to modify it.
DO event_body: The SQL statement to be executed (can be a compound statement). CREATE EVENT is legal when used within a stored procedure.

3.2 Enable and disable the event scheduler

3.2.1 MySQL event scheduler event_scheduler is responsible for calling events and is closed by default. This scheduler constantly monitors whether an event is to be called. To create an event, the scheduler must be opened.

mysql> show variables like '%event_scheduler%'; 
+-----------------+-------+ 
| Variable_name | Value | 
+-----------------+-------+ 
| event_scheduler | OFF | 
+-----------------+-------+

3.2.2 Enable event scheduler

Via the command line

You can use any of the following command lines

SET GLOBAL event_scheduler = ON; 
SET @@global.event_scheduler = ON; 
SET GLOBAL event_scheduler = 1; 
SET @@global.event_scheduler = 1;

Through the configuration file my.cnf

event_scheduler = 1 #or ON

View Scheduler Threads

mysql> show processlist; 
+----+-----------------+-----------+------+---------+------+------------------------+------------------+ 
| Id | User | Host | db | Command | Time | State | Info | 
+----+-----------------+-----------+------+---------+------+------------------------+------------------+ 
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | 
| 3 | event_scheduler | localhost | NULL | Daemon | 6 | Waiting on empty queue | NULL| 
+----+-----------------+-----------+------+---------+------+------------------------+------------------+

3.2.3 Turn off the event scheduler

Via the command line

You can use any of the following command lines

SET GLOBAL event_scheduler = OFF; 
SET @@global.event_scheduler = OFF; 
SET GLOBAL event_scheduler = 0; 
SET @@global.event_scheduler = 0;

Through the configuration file my.cnf

Add under [mysqld]

event_scheduler = 0 #or OFF, DISABLED

View Scheduler Threads

mysql> show processlist; 
+----+------+-----------+------+---------+------+-------+-----------------+ 
| Id | User | Host | db | Command | Time | State | Info | 
+----+------+-----------+------+---------+------+-------+-----------------+ 
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | 
+----+------+-----------+------+---------+------+-------+-----------------+

3.3 Example: Create a table to record the name and event stamp of each event dispatch

3.3.1 Create a test table

mysql> drop table if exists events_list; 
mysql> create table events_list(event_name varchar(20) not null, event_started timestamp not null);

3.3.2 Create event 1 (start the event immediately)

create event event_now 
on schedule 
at now() 
do insert into events_list values('event_now', now());

View event execution results

mysql> select * from events_list; 
+------------+---------------------+ 
| event_name | event_started | 
+------------+---------------------+ 
| event_now | 2014-07-01 04:06:40 | 
+------------+---------------------+

3.3.3 Create event 2 (start an event every minute)

create event test.event_minute 
on schedule 
every 1 minute  
do insert into events_list values('event_now', now());

View event execution results

mysql> select * from events_list; 
+------------+---------------------+ 
| event_name | event_started | 
+------------+---------------------+ 
| event_now | 2014-07-01 04:26:53 | 
| event_now | 2014-07-01 04:27:53 | 
| event_now | 2014-07-01 04:28:53 | 
+------------+---------------------+

3.3.3 Create event 3 (start the event every second)

CREATE event event_now 
ON SCHEDULE 
EVERY 1 SECOND
DO INSERT INTO event_test VALUES(1);

3.3.4 Create event 4 (calling a stored procedure every second)

CREATE DEFINER=`root`@`localhost` EVENT `eventUpdateStatus` 
ON SCHEDULE EVERY 1 SECOND 
STARTS '2017-11-21 00:12:44' 
ON COMPLETION PRESERVE 
ENABLE 
DO call updateStatus()

3.4 Note:

By default, the created event is stored in the current library. You can also display the library in which the specified event is created.

You can only view the events created in the current library through show events

The event is released after execution. If the event is executed immediately, it will be automatically deleted after execution. You can view the multiple calls or waiting events.

If two events need to be called at the same time, MySQL will determine the order in which they are called. If you want to specify the order, you need to ensure that one event is executed at least 1 second after the other event.

For recursively scheduled events, the end date cannot be before the start date.

A select can be included in an event, but its results disappear, as if it had not been executed.

4 View events

View events of the current library

mysql> show events;

View All Events

mysql> select * from mysql.event;

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.