Of Stack

Event scheduling basics in MySQL

  • 2020-11-20 06:17:24
  • OfStack

There is often a need for timed tasks to be performed on the MySQL table, such as statistics, migration, deleting unwanted data, and so on. The previous practice was to use Linux cron to run the script regularly, but it was found that such extra dependence was sometimes inconvenient. For example, when deploying single machine with multiple instances, it was necessary to manually configure different cron tasks separately, and corresponding users and permissions needed to be configured separately. It is easy to miss cron tasks when a new environment is deployed.

MySQL provides Event Scheduler, which, like crontab under Linux, can run tasks once or more depending on time schedule.

The complete Event Schduler creation statement reads 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}

1. The scheduling Scheduler
Schedules in MySQL can be run only once or repeated at specified intervals. Its definition is in the ON SCHEDULE clause defined by event. The format of this clause is as follows: 


ON SCHEDULE
AT timestamp [+ INTERVAL interval]  ... 
| EVERY interval
  [STARTS timestamp [+ INTERVAL interval]  ... ]
  [ENDS timestamp [+ INTERVAL interval]  ... ]

Where timestamp must include "minutes and minutes of month, year and day", which participate in the expression evaluation and result in datetime or timestamp type.

The time interval interval can be as follows: 


< digital > {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
      WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
      DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

The implications are clear, as in YEAR; QUARTER quarter; YEAR_MONTH + month; MINUTE_SECOND minutes + seconds.

Supplement:

YEAR | QUARTER | MONTH | YEAR_MONTH the background is converted to MONTH and the other time intervals are converted to SECOND
The time in ON SCHEDULE USES the time zone information in the session when it was created, time_zone, which defaults to the server's global time_zone and may be manually updated later. These times are converted to UTC times and stored in the ES63en.event table.
1.1 run
AT either specifies the time directly or USES a time expression to calculate a certain point in time.

Example:

AT '2006-02-10 23:59:00 'Specify the exact running time, local time zone.
AT current_timestamp + INTERVAL '1:15 'MINUTE_SECOND specifies to run after 1 minute and 15 seconds.
2. Run multiple times
EVERY sets the time interval for running. [+ INTERVAL interval] cannot be specified here.

Specifying STARTS, ENDS is optional.

STARTS specifies when the first run is repeated. If not specified, it runs the first time at event creation, which is equivalent to STARTS CURRENT_TIMESTAMP!
ENDS tells MySQL at what point the repeat run ends. If not specified, MySQL repeats forever.
Example:

EVERY 5 WEEK runs once every 5 weeks, the first time at creation.
EVERY 3 DAY STARTS '2013-12-4:09:10:00' run the first time from '2013-12-4:09:10:00' every 3 days.
EVERY 2 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 10 MINUTE ENDS '2014-12-31 23:59:59 'Start in 10 minutes and run once every two months until the end of 2014.
2. Event events
1. Enable Event Scheduler
Event is executed by a specific Event Scheduler thread. During the run, the current status information can be viewed through show full processlist, such as:

7384313 event_scheduler localhost [NULL] Daemon 3 Waiting on empty queue [NULL]

The default event scheduling Event Scheduler feature is not enabled and requires configuration of the global parameter event_scheduler, which can be set dynamically to take effect immediately.

event_scheduler has the following three values:

OFF/0 off, default. There is no event scheduling without running the Event Scheduler thread. Setting it to ON enables it immediately.
Enable ON / 1.
DISABLED disabled. Also do not run the Event Scheduler thread. Settings are only useful when the MySQL service is started. When event_scheduler is ON or OFF, event_scheduler cannot be set to DISABLED at run time. If event-ES163en =DISABLED is configured at startup, the runtime cannot be set to ON/OFF. In other words, you can set it to DISABLED when the MySQL service starts, and then disable event_scheduler completely, without dynamic adjustment.
So, to enable event_scheduler, the runtime executes: 


set global event_scheduler=on

To be enabled with MySQL service 1, add to /etc/ my.cnf 


[mysqld]
event-scheduler=on

2. Create the syntax for the event 


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}

Parameter details:
DEFINER: User who checks permissions as events are executed.
ON SCHEDULE schedule: Defines the time and interval for execution.
ON COMPLETION [NOT] PRESERVE: Defines whether an event is executed once or permanently. By default, it is executed once, i.e. NOT PRESERVE.
ENABLE | DISABLE | DISABLE ON SLAVE: defines whether the event is turned on or off after creation, and is closed from above. DISABLE ON SLAVE is automatically added if the statement creating the event from the master is automatically synchronized from the server.
COMMENT 'comment': Comment that defines the event.

3. Change the syntax of the event 


ALTER
  [DEFINER = { user | CURRENT_USER }]
  EVENT event_name
  [ON SCHEDULE schedule]
  [ON COMPLETION [NOT] PRESERVE]
  [RENAME TO new_event_name]
  [ENABLE | DISABLE | DISABLE ON SLAVE]
  [COMMENT 'comment']
  [DO event_body]

Delete the syntax of the event 


DROP EVENT [IF EXISTS] event_name

5. Do clauses
The concrete logic of events is implemented in the Do clause, and almost any MySQL statement that can be run in a stored program can be used in event.

1) Simple SQL example: 


CREATE EVENT e_hourly
  ON SCHEDULE
   EVERY 1 HOUR
  COMMENT  ' Clears out sessions table each hour.'
  DO
   DELETE FROM site_activity.sessions;

2) Complex SQL example: 


delimiter |
CREATE EVENT e
  ON SCHEDULE
   EVERY 5 SECOND
  DO
   BEGIN
    DECLARE v INTEGER;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
    SET v = 0;
    WHILE v < 5 DO
     INSERT INTO t1 VALUES (0);
     UPDATE t2 SET s1 = s1 + 1;
     SET v = v + 1;
    END WHILE;
  END |
delimiter ;

3) SQL restriction in Do clause

Basically, Do can use any OF the SQL statements that are allowed in the stored program (Stored Routine), with some restrictions in the stored program and some additional restrictions in event.

The following statements in Stored Routine are not allowed:

LOCK TABLES/UNLOCK TABLES LOAD DATA and LOAD TABLE

Support dynamic SQL(PREPARE, EXECUTE, DEAALOCATE PREPARE)! However, PREPARE itself is not allowed to execute some statements.

INSERT DELAYED will not take effect
Limitations of EVENT:

If the Do clause contains the ALTER EVENT clause, it can be created, but an error occurs at runtime.
Do not use SELECT or SHOW in the Do clause as a mere statement of the query because its output cannot be obtained externally. You can use SELECT... Forms like INTO save the query results.


5. Check the EVENT
event information can be viewed as follows: 


ON SCHEDULE
AT timestamp [+ INTERVAL interval]  ... 
| EVERY interval
  [STARTS timestamp [+ INTERVAL interval]  ... ]
  [ENDS timestamp [+ INTERVAL interval]  ... ]
0


3. event schedule
MySQL saves sql_mode at event creation as sql_mode at runtime;
If the task is not processed within one scheduling interval, a new scheduling will still be generated, so that there will be multiple tasks running at the same time. If you want to avoid multiple tasks, use the GET_LOCK() function or row and table locks.

4. Mysql event combat
The test environment
Create 1 test table for testing: 


ON SCHEDULE
AT timestamp [+ INTERVAL interval]  ... 
| EVERY interval
  [STARTS timestamp [+ INTERVAL interval]  ... ]
  [ENDS timestamp [+ INTERVAL interval]  ... ]
1

Practice 1
Ø Create an event that inserts 1 item of data into the test table every 3 seconds, as follows: 


ON SCHEDULE
AT timestamp [+ INTERVAL interval]  ... 
| EVERY interval
  [STARTS timestamp [+ INTERVAL interval]  ... ]
  [ENDS timestamp [+ INTERVAL interval]  ... ]
2

Ø Create an event that clears the test table data after 10 minutes 


CREATE EVENT IF NOT EXISTS test
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO TRUNCATE TABLE test.aaa;

Ø Create an event to clear test table data at 2012-08-23 00:00:00 with the following code: 


ON SCHEDULE
AT timestamp [+ INTERVAL interval]  ... 
| EVERY interval
  [STARTS timestamp [+ INTERVAL interval]  ... ]
  [ENDS timestamp [+ INTERVAL interval]  ... ]
4

Ø Create an event that runs from 21:45 on August 22, 2012 to 10 minutes later and inserts a data item into the test table every 3 seconds, as follows: 


ON SCHEDULE
AT timestamp [+ INTERVAL interval]  ... 
| EVERY interval
  [STARTS timestamp [+ INTERVAL interval]  ... ]
  [ENDS timestamp [+ INTERVAL interval]  ... ]
5


Actual combat 2
A common application scenario is to invoke stored procedures periodically through events. Here is a simple example:
Create a stored procedure that adds cardinality 2 per line to the id2 field of the test table. The stored procedure code is as follows: 


ON SCHEDULE
AT timestamp [+ INTERVAL interval]  ... 
| EVERY interval
  [STARTS timestamp [+ INTERVAL interval]  ... ]
  [ENDS timestamp [+ INTERVAL interval]  ... ]
6

Event setup 2012-08-22 00:00:00 Start running, call stored procedure once every 1, end after 40 days, the code is as follows: 


ON SCHEDULE
AT timestamp [+ INTERVAL interval]  ... 
| EVERY interval
  [STARTS timestamp [+ INTERVAL interval]  ... ]
  [ENDS timestamp [+ INTERVAL interval]  ... ]
7

Related articles: