In the simplest terms, an event is any task that can be run according to a schedule. Many popular DBMSes include support for events. These are also known as "scheduled events" or as "temporal triggers" because events are triggered by time, as opposed to triggers, which are invoked by database operations such as table updates. Database events may be utilized for a variety of tasks such as optimizing database tables, cleaning up logs, archiving data, or to generate reports during off-peak times.

In today's blog, we'll learn how to view and activate database events. In subsequent blogs, we'll learn how to configure events for various tasks.

Events vs. Scheduled Tasks

Although database event are similar to a cron job in UNIX or a task scheduler in Windows, they differ in that events are managed and invoked at the database level as opposed to the Operating System (OS). Hence, database events are configured using the database's Data Definition Language (DDL) whereas cron jobs and scheduled tasks are defined using that OS's particular commands and/or tools.

Configuring the Event Scheduler Thread

Events are executed by a special thread. You can see the event scheduler thread and its current state by typing the "SHOW PROCESSLIST" command at the mysql> prompt, provided that you have have the PROCESS privilege:

mysql>  SHOW PROCESSLIST;
+----+-----------------+-----------------+--------+---------+------+-----------------------------+------------------+
| Id | User            | Host            | db     | Command | Time | State                       | Info             |
+----+-----------------+-----------------+--------+---------+------+-----------------------------+------------------+
|  2 | event_scheduler | localhost:49670 | NULL   | Daemon  |    3 | Waiting for next activation |                  |
|  3 | root            | localhost:49671 | NULL   | Sleep   |   43 |                             | NULL             |
|  4 | root            | localhost:49672 | NULL   | Sleep   |  180 |                             | NULL             |
|  5 | root            | localhost:56134 | sakila | Query   |    0 | starting                    | SHOW PROCESSLIST |
|  6 | root            | localhost:56136 | sakila | Sleep   | 1025 |                             | NULL             |
+----+-----------------+-----------------+--------+---------+------+-----------------------------+------------------+
5 rows in set (0.01 sec)

Activating the Event Scheduler

Both the activating and enabling of the Event Scheduler is done via the global event_scheduler system variable. One of the following three values may be assigned to it:

• ON: This starts the Event Scheduler; the event scheduler thread runs and executes all scheduled events. This is the default value.
  
  When the Event Scheduler is ON, the event scheduler thread is listed in the output of SHOW PROCESSLIST as a daemon process, and its state is represented as "Waiting for next activation", as shown in the output above.
  
  Either "ON" or its numeric equivalent of 1 are acceptable values. Thus, any of the following 4 statements can be used in the mysql client to turn on the Event Scheduler:
  
  
  • SET GLOBAL event_scheduler = ON;
  • SET @@global.event_scheduler = ON;
  • SET GLOBAL event_scheduler = 1;
  • SET @@global.event_scheduler = 1;


• OFF: Stops the Event Scheduler. The event scheduler thread does not run, is not shown in the output of SHOW PROCESSLIST, and no scheduled events are executed.
  
  When the event_scheduler variable is set to OFF (Event Scheduler is stopped), it can be (re)started by setting the value of event_scheduler to ON.
  
  It is also possible to use 0 in place of "OFF", so that any of these 4 statements can be used to turn off the Event Scheduler:
  
  
  • SET GLOBAL event_scheduler = OFF;
  • SET @@global.event_scheduler = OFF;
  • SET GLOBAL event_scheduler = 0;
  • SET @@global.event_scheduler = 0;


• DISABLED: This value puts the Event Scheduler thread to sleep so that the Event Scheduler is non-operational. Moreover, when the Event Scheduler is DISABLED, the event scheduler thread does not appear in the output of SHOW PROCESSLIST.
  
  Note that the Event Scheduler state cannot be changed at runtime when disabled.

Displayed event_scheduler Values

Although ON and OFF have numeric equivalents, DISABLED has none. For that reason, event_scheduler values generated by either a SELECT or SHOW VARIABLES are always displayed using the full text value, i.e., either "OFF", "ON", or "DISABLED". For this reason, "ON" and "OFF" are recommended over 1 and 0 when setting the event_scheduler variable.

mysql> SHOW VARIABLES like 'event_%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.02 sec)