21.1. Event Scheduler Overview
MySQL Events are tasks that run according to a schedule.
Therefore, we sometimes refer to them as
scheduled events. When you create an event,
you are creating a named database object containing one or more
SQL statements to be executed at one or more regular intervals,
beginning and ending at a specific date and time. Conceptually,
this is similar to the idea of the Unix crontab
(also known as a “cron job”) or the Windows Task
Scheduler.
Scheduled tasks of this type are also sometimes known as
“temporal triggers”, implying that these are objects
that are triggered by the passage of time. While this is
essentially correct, we prefer to use the term
events in order to avoid confusion with
triggers of the type discussed in Chapter 20, Triggers.
Events should more specifically not be confused with
“temporary triggers”. Whereas a trigger is a database
object whose statements are executed in response to a specific
type of event that occurs on a given table, a (scheduled) event is
an object whose statements are executed in response to the passage
of a specified time interval.
While there is no provision in the SQL Standard for event
scheduling, there are precedents in other database systems, and
you may notice some similarities between these implementations and
that found in the MySQL Server.
MySQL Events have the following major features and properties:
An event is uniquely identified by: its name; the schema to
which it is assigned; and the user who created it (definer).
An event performs a specific action according to a schedule.
This action consists of an SQL statement, which can be a
compound statement in a BEGIN ... END block
if desired (see Section 19.2.5, “BEGIN ... END Compound Statement Syntax”). An event's
timing can be either transient or
recurrent. A transient event executes
one time only. A recurrent event repeats its action at a
regular interval, and the schedule for a recurring event can
be assigned a specific start day and time, end day and time,
both, or neither. (By default, a recurring event's schedule
begins as soon as it is created, and continues indefinitely,
until it is disabled or dropped.)
Users can create, modify, and drop scheduled events using SQL
statements intended for these purposes. Syntactically invalid
event creation and modification statements fail with an
appropriate error message. A user may include
statements in an event's action which require privileges that
the user does not actually have. The event creation
or modification statement succeeds but the event's action
fails. See Section 21.4, “The Event Scheduler and MySQL Privileges” for details.
-
Many of the properties of an event can be set or modified
using SQL statements. These properties include the event's
name, timing, persistence (that is, whether it is preserved
following the expiration of its schedule), status (enabled or
disabled), action to be performed, and the schema to which it
is assigned. See Section 21.2.2, “ALTER EVENT Syntax”.
The definer of an event cannot be changed; it is always the
user who created the event. An event can be modified only by
the event's definer, or by a user having privileges on the
mysql.event table (see
Section 21.4, “The Event Scheduler and MySQL Privileges”.)
An event's action statement may include most SQL statements
permitted within stored routines.
MySQL 5.1.6 introduces a global variable
event_scheduler which determines whether the
Event scheduler is enabled for the server. This variable defaults
to OFF or 0, meaning that
event scheduling is not available:
mysql> SHOW GLOBAL VARIABLES LIKE 'event%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.01 sec)
mysql> SELECT @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| 0 |
+-------------------+
1 row in set (0.00 sec)
In order to enable event scheduling, you must first issue one of
these statements:
SET GLOBAL event_scheduler = ON;
or
SET GLOBAL event_scheduler = 1;
or
SET @@global.event_scheduler = ON;
or
SET @@global.event_scheduler = 1;
Note: You can issue
event-manipulation statements when
event_scheduler is set to
OFF or 0. No warnings or
errors are generated in such cases (so long as the statements are
themselves valid). However, scheduled events cannot execute until
this variable is set to ON or
1. (Once this has been done, all events whose
scheduling conditions are met become active.)
Note: Since
event_scheduler is a global variable, you must
have the SUPER privilege to set its value.
You can also enable event scheduling by starting
mysqld with
--event_scheduler=1 or more simply
--event_scheduler. (1 is the
default value in this case.)
For SQL statements used to create, alter, and drop events, see
Section 21.2, “Event Scheduler Syntax”.
MySQL 5.1.6 and later provides an EVENTS table
in the INFORMATION_SCHEMA database. This table
can be queried to find out about the events which exist on the
server. See Section 21.3, “Event Metadata”.
For information regarding event scheduling and the MySQL privilege
system, see Section 21.4, “The Event Scheduler and MySQL Privileges”.
