Case Analysis of Scheduled Task and Event Scheduling in mysql

This paper describes the planning task and event scheduling of mysql with examples. Share it for your reference, as follows:

An mysql event is a task that runs based on a predefined schedule, so it is sometimes referred to as a predetermined event. The mysql event is also called a "time trigger" because it is triggered by time, not by updating the table like Trigger 1. mysql events are similar to cron jobs in UNIX or task schedulers in Windows. We can use mysql events when tuning database tables, cleaning logs, archiving data, or generating complex reports during off-peak hours.

mysql uses a special thread called Event Scheduling Thread to execute all scheduled events. We can view the status of the event scheduler thread by executing the following command:

SHOW PROCESSLIST;

Execute the above query statement and get the following results:

mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+------+----------+------------------+
| Id | User | Host      | db    | Command | Time | State  | Info       |
+----+------+-----------------+----------+---------+------+----------+------------------+
| 2 | root | localhost:50405 | NULL   | Sleep  | 1966 |     | NULL       |
| 3 | root | localhost:50406 | yiibaidb | Sleep  | 1964 |     | NULL       |
| 4 | root | localhost:50407 | yiibaidb | Query  |  0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+----------+---------+------+----------+------------------+
3 rows in set

By default, the event scheduler thread is not enabled. To enable and start the event scheduler thread, you need to execute the following command:

SET GLOBAL event_scheduler = ON;

Now that you see the state of the event scheduler thread, execute the SHOW PROCESSLIST command again, and the result is as follows:

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
| Id | User      | Host      | db    | Command | Time | State         | Info       |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
| 2 | root      | localhost:50405 | NULL   | Sleep  | 1986 |            | NULL       |
| 3 | root      | localhost:50406 | luyaran | Sleep  | 1984 |            | NULL       |
| 4 | root      | localhost:50407 | luyaran | Query  |  0 | starting        | SHOW PROCESSLIST |
| 5 | event_scheduler | localhost    | NULL   | Daemon |  6 | Waiting on empty queue | NULL       |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
4 rows in set

To disable and stop the event scheduler thread, set event_scheduler to OFF by executing the SET GLOBAL command:

SET GLOBAL event_scheduler = OFF;

As we know, an event is a named object containing an SQL statement, and creating an event is similar to creating other database objects (such as stored procedures or triggers), however, stored procedures are only executed when called directly; Triggers, when an event (such as insert, update, or delete) associated with 1 table occurs, can be triggered when the event is executed at 1 or more regular intervals. So, what about events? Let's try to use the CREATE EVENT statement to create events. Let's look at the syntax structure first:

CREATE EVENT [IF NOT EXIST] event_name
ON SCHEDULE schedule
DO
event_body

Let's look at the meaning of the parameters in sql above in detail:

First, specify the event name after the CREATE EVENT clause. The event name must be 1-only in the database schema.

Next, add a table after the ON SCHEDULE clause. If the event is a single event, use the syntax: AT timestamp [+ INTERVAL], and if the event is a loop event, use the EVERY clause: EVERY interval STARTS timestamp [+ INTERVAL] ENDS timestamp [+ INTERVAL]

Third, put the DO statement after the DO keyword. Note that stored procedures can be called within the event body. If you have composite SQL statements, you can put them in an BEGIN END block.

Let's first create an messages table for demonstration:

CREATE TABLE IF NOT EXISTS messages (
  id INT PRIMARY KEY AUTO_INCREMENT,
  message VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL
);

Let's use the CREATE EVENT statement to create an event:

CREATE EVENT IF NOT EXISTS test_event_01
ON SCHEDULE AT CURRENT_TIMESTAMP
DO
 INSERT INTO messages(message,created_at)
 VALUES('Test MySQL Event 1',NOW());

Check the messages table; You will see that there is 1 record, which means that the event is executed when it is created:

mysql> SELECT * FROM messages;
+----+--------------------+---------------------+
| id | message      | created_at     |
+----+--------------------+---------------------+
| 1 | Test MySQL Event 1 | 2017-08-03 04:23:11 |
+----+--------------------+---------------------+
1 row in set

To display all events for the database (testdb), use the following statement:

SHOW EVENTS FROM testdb;

Executing the above query does not see any rows returned, because the event is automatically deleted when it expires. In our example, it is a once event that expires when the execution is completed. To change this behavior, use the ON COMPLETION PRESERVE clause. The following statement creates another one-time event, which is executed 1 minute after its creation time and will not be deleted after execution:

mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+------+----------+------------------+
| Id | User | Host      | db    | Command | Time | State  | Info       |
+----+------+-----------------+----------+---------+------+----------+------------------+
| 2 | root | localhost:50405 | NULL   | Sleep  | 1966 |     | NULL       |
| 3 | root | localhost:50406 | yiibaidb | Sleep  | 1964 |     | NULL       |
| 4 | root | localhost:50407 | yiibaidb | Query  |  0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+----------+---------+------+----------+------------------+
3 rows in set

0

After waiting for 1 minute, look at the messages table and add another record:

mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+------+----------+------------------+
| Id | User | Host      | db    | Command | Time | State  | Info       |
+----+------+-----------------+----------+---------+------+----------+------------------+
| 2 | root | localhost:50405 | NULL   | Sleep  | 1966 |     | NULL       |
| 3 | root | localhost:50406 | yiibaidb | Sleep  | 1964 |     | NULL       |
| 4 | root | localhost:50407 | yiibaidb | Query  |  0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+----------+---------+------+----------+------------------+
3 rows in set

1

If you execute the SHOW EVENTS statement again, you see that the event is due to the ON COMPLETION PRESERVE clause:

mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+------+----------+------------------+
| Id | User | Host      | db    | Command | Time | State  | Info       |
+----+------+-----------------+----------+---------+------+----------+------------------+
| 2 | root | localhost:50405 | NULL   | Sleep  | 1966 |     | NULL       |
| 3 | root | localhost:50406 | yiibaidb | Sleep  | 1964 |     | NULL       |
| 4 | root | localhost:50407 | yiibaidb | Query  |  0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+----------+---------+------+----------+------------------+
3 rows in set

2

Let's create a loop event that executes once a minute and expires within 1 hour of its creation time:

CREATE EVENT test_event_03
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
  INSERT INTO messages(message,created_at)
  VALUES('Test MySQL recurring Event',NOW());

We should note that we use the STARTS and ENDS clauses to define the validity period of the event. Wait 3, 5 minutes before viewing the messages table data to test and verify the execution of this loop event:

mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+------+----------+------------------+
| Id | User | Host      | db    | Command | Time | State  | Info       |
+----+------+-----------------+----------+---------+------+----------+------------------+
| 2 | root | localhost:50405 | NULL   | Sleep  | 1966 |     | NULL       |
| 3 | root | localhost:50406 | yiibaidb | Sleep  | 1964 |     | NULL       |
| 4 | root | localhost:50407 | yiibaidb | Query  |  0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+----------+---------+------+----------+------------------+
3 rows in set

4

After that, we can use the DROP EVENT statement to delete the event, and look at the syntax structure:

mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+------+----------+------------------+
| Id | User | Host      | db    | Command | Time | State  | Info       |
+----+------+-----------------+----------+---------+------+----------+------------------+
| 2 | root | localhost:50405 | NULL   | Sleep  | 1966 |     | NULL       |
| 3 | root | localhost:50406 | yiibaidb | Sleep  | 1964 |     | NULL       |
| 4 | root | localhost:50407 | yiibaidb | Query  |  0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+----------+---------+------+----------+------------------+
3 rows in set

5

To delete the test_event_03 event, we can use the following sql:

mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+------+----------+------------------+
| Id | User | Host      | db    | Command | Time | State  | Info       |
+----+------+-----------------+----------+---------+------+----------+------------------+
| 2 | root | localhost:50405 | NULL   | Sleep  | 1966 |     | NULL       |
| 3 | root | localhost:50406 | yiibaidb | Sleep  | 1964 |     | NULL       |
| 4 | root | localhost:50407 | yiibaidb | Query  |  0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+----------+---------+------+----------+------------------+
3 rows in set

6

Ok, that's all for this record.

More readers interested in MySQL can check out the topics on this site: "MySQL Index Operation Skills Summary", "MySQL Common Function Summary", "MySQL Log Operation Skills Summary", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Summary" and "MySQL Database Lock Related Skills Summary"

I hope this article is helpful to everyone's MySQL database.