Events Overview
Contents
Events are named database objects containing SQL statements that are to be executed at a later stage, either once off, or at regular intervals.
They function very similarly to the Windows Task Scheduler or Unix cron jobs.
Creating, modifying or deleting events requires the EVENT privilege.
Creating Events
Events are created with the CREATE EVENT statement.
Example
CREATE EVENT test_event ON SCHEDULE EVERY 1 MINUTE DO UPDATE test.t1 SET a = a + 1;
Executing Events
Events are only executed if the event scheduler is running. This is determined by the value of the event_scheduler system variable, which needs to be set to On for the event scheduler to be running.
You can check if the Event scheduler is running with:
SHOW PROCESSLIST; +----+-----------------+-----------+------+---------+------+-----------------------------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-----------------+-----------+------+---------+------+-----------------------------+------------------+----------+ | 40 | root | localhost | test | Sleep | 4687 | | NULL | 0.000 | | 41 | root | localhost | test | Query | 0 | init | SHOW PROCESSLIST | 0.000 | | 42 | event_scheduler | localhost | NULL | Daemon | 30 | Waiting for next activation | NULL | 0.000 | +----+-----------------+-----------+------+---------+------+-----------------------------+------------------+----------+
If the event scheduler is not running and event_scheduler has been set to OFF, use:
SET GLOBAL event_scheduler = ON;
to activate it. If event_scheduler has been set to Disabled, you cannot change the value at runtime. Changing the value of the event_scheduler variable requires the SUPER privilege.
Since MariaDB 10.0.22, setting the event_scheduler system variable will also try to reload the mysql.event table if it was not properly loaded at startup.
Viewing Current Events
A list of current events can be obtained with the SHOW EVENTS statement. This only shows the event name and interval - the full event details, including the SQL, can be seen by querying the Information Schema EVENTS table, or with SHOW CREATE EVENT.
If an event is currently being executed, it can be seen by querying the Information Schema PROCESSLIST table, or with the SHOW PROCESSLIST statement.
Example
SHOW EVENTS\G;
*************************** 1. row ***************************
Db: test
Name: test_event
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2013-05-20 13:46:56
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
SHOW CREATE EVENT test_event\G
*************************** 1. row ***************************
Event: test_event
sql_mode:
time_zone: SYSTEM
Create Event: CREATE DEFINER=`root`@`localhost` EVENT `test_event` ON SCHEDULE EVERY 1 MINUTE STARTS '2013-05-20 13:46:56' ON COMPLETION NOT PRESERVE ENABLE DO UPDATE test.t1 SET a = a + 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
Altering Events
An event can be changed with the ALTER EVENT statement.
Example
ALTER EVENT test_event ON SCHEDULE EVERY '2:3' DAY_HOUR;
Dropping Events
Events are dropped with the DROP EVENT statement. Events are also also automatically dropped once they have run for the final time according to their schedule, unless the ON COMPLETION PRESERVE clause has been specified.
Example
DROP EVENT test_event; Query OK, 0 rows affected (0.00 sec)
