Events Overview

An introduction to creating and managing scheduled events, which are named database objects containing SQL statements to be executed by the Event Scheduler.

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}