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.
Events are created with the CREATE EVENT statement.
CREATE EVENT test_event ON SCHEDULE EVERY 1 MINUTE DO UPDATE test.t1 SET a = a + 1;
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.
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.
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
An event can be changed with the ALTER EVENT statement.
ALTER EVENT test_event ON SCHEDULE EVERY '2:3' DAY_HOUR;
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.
DROP EVENT test_event; Query OK, 0 rows affected (0.00 sec)
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/events/