Events Overview

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 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)

See Also

• CREATE EVENT

• SHOW CREATE EVENT

• ALTER EVENT

• DROP EVENT

• Event Limitations

• Automating MariaDB Tasks with Events

_{This page is licensed: CC BY-SA / Gnu FDL}