MySQL event scheduler EventScheduler

EventScheduler, replace some timer tasks that can only be completed by the operating system Task Scheduler> yes. For example, crontabe in Linux can be executed only once per minute, while the MySQL event scheduler can execute a task every second, this is very practical in some environments that require higher real-time performance.

The event scheduler is scheduled to trigger execution, which can also be called a "temporary trigger ". The trigger only executes some statements for the events generated by a table, while the event scheduler executes some statements at a certain interval. Events are managed by a specific thread, that is, the so-called "event scheduler ". After the event scheduler is enabled, an account with SUPER permissions can execute show processlist to view the thread. You can dynamically control whether the event scheduler is enabled by setting the global variable event_scheduler.

(Root: localhost :) test> set global event_scheduler = ON; (root: localhost :) test> show processlistG **************************** 4. row ************************* Id: 46147 User: event_schedulerHost: localhostdb: NULLCommand: daemonTime: 1 State: Waiting on empty queueInfo: NULL

As shown above, the thread owner is event_scheduler.

Application cases

This case uses the feature of event scheduler to call a stored procedure every second to determine whether the SLAVE is running normally. if the SLAVE is disabled, ignore the zero error and restart the SLAVE.

First, create a stored procedure

Delimiter // create procedure 'slave _ monitor' () beginSELECT VARIABLE_VALUE INTO @ SLAVE_STATUS FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'slave _ running'; IF ('on '! = @ SLAVE_STATUS) thenset global SQL _SLAVE_SKIP_COUNTER = 0; SLAVE START; END IF; end; // delimiter;


Because the statement like show slave status cannot be called in the stored procedure, the exact copy error message and error code cannot be obtained, and the various conditions of SLAVE stop cannot be further processed.

Next, create a task.

Create event if not exists 'slave _ monitor'
On schedule every 5 SECOND
ON COMPLETION PRESERVE
DO
CALL Slave_Monitor ();

A task is created and executed every five seconds. after the task is completed, the task is retained instead of deleted. Of course, the task in this example will not end unless it is manually disabled.

If you want to temporarily close a task during running, execute the alter event statement:

(Root: localhost :) test> alter event 'slave _ Monitor' ON
Completion preserve disable;
(Root: localhost :) test> alter event 'slave _ Monitor' ON
Completion preserve enable;