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
(also known as a 「cron job」) or the Windows Task
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 18章トリガ. 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:
In MySQL 5.1.12 and later, an event is uniquely identified by its name and the schema to which it is assigned. (Previously, an event was also unique to its 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 「
BEGIN ... END 複合ステートメント構文」). An event's
timing can be either one-time or
recurrent. A one-time 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 「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 「
ALTER EVENT Syntax」.
The definer of an event is the user who created the event,
unless the event has been altered, in which case the definer
is the user who issued the last
statement effecting that event. An event can be modified by
any user having the
EVENT privilege on the
database for which the event is defined. (Prior to MySQL
5.1.12, only an event's definer, or a user having privileges
mysql.event table, could modify a
given event.) See 「The Event Scheduler and MySQL Privileges」.
An event's action statement may include most SQL statements permitted within stored routines.
Events are executed by a special event scheduler
thread; when we refer to the Event Scheduler, we
actually refer to this thread. When running, the event scheduler
thread and its current state can be seen by users having the
SUPER privilege in the output of
PROCESSLIST, as shown in the discussion that follows.
The global variable
whether the Event Scheduler is enabled and running on the server.
Beginning with MySQL 5.1.12, it has one of these 3 values, which
affect event scheduling as described here:
OFF: The Event Scheduler is stopped. The
event scheduler thread does not run, is not shown in the
SHOW PROCESSLIST, and no
scheduled events are executed.
OFF is the
default value for
When the Event Scheduler is stopped
OFF), it can be started by setting the
ON. (See next item.)
ON: The Event Scheduler is started; the
event scheduler thread runs and executes all scheduled events.
When the Event Scheduler is
ON, the event
scheduler thread is listed in the output of
PROCESSLIST as a daemon process, and its state is
represented as shown here:
SHOW PROCESSLIST\G*************************** 1. row *************************** Id: 1 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist *************************** 2. row *************************** Id: 2 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 3 State: Waiting for next activation Info: NULL 2 rows in set (0.00 sec)
Event scheduling can be stopped by setting the value of
DISABLED: This value renders the Event
Scheduler non-operational. When the Event Scheduler is
DISABLED, the event scheduler thread does
not run (and so does not appear in the output of
When the server is running
be toggled between
SET). It is also
possible to use
ON when setting
this variable. Thus, any of the following 4 statements can be used
in the mysql client to turn on the Event
SET GLOBAL event_scheduler = ON; SET @@global.event_scheduler = ON; SET GLOBAL event_scheduler = 1; SET @@global.event_scheduler = 1;
Similarly, 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;
numeric equivalents, the value displayed for
SHOW VARIABLES is always one of
has no numeric equivalent. For this reason,
OFF are usually
setting this variable.
Note that attempting to set
without specifying it as a global variable causes an error:
SET @@event_scheduler = OFF;ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL
Important: It is not possible to
enable or disable the Event Scheduler when the server is running.
That is, you can change the value of
— or from
DISABLED to one of the other
permitted values for this option — only when the server is
stopped. Attempting to do so when the server is running fails with
To disable the event scheduler, use one of the following two methods:
As a command-line option when starting the server:
In the server configuration file (
my.ini on Windows systems), include
the line where it will be read by the server (for example, in
To enable the Event Scheduler, restart the server without the
command line option, or after removing or commenting out the line
event_scheduler=DISABLED in the
server configuration file, as appropriate. Alternatively, you can
0) in place of the
DISABLED value when starting the server.
Note: You can issue
event-manipulation statements when
event_scheduler is set to
DISABLED. No warnings or errors are generated
in such cases (provided that the statements are themselves valid).
However, scheduled events cannot execute until this variable is
1). Once this
has been done, the event scheduler thread executes all events
whose scheduling conditions are satisfied.
In MySQL 5.1.11,
event_scheduler behaved as
follows: this variable could take one of the values
2. Setting it to
event scheduling off, so that the event scheduler thread did not
event_scheduler variable could not be
set to this value while the server was running. Setting it to
1 so that the event scheduler thread ran and
executed scheduled events. In this state, the event scheduler
thread appeared to be sleeping when viewed with
2 (which was the default value), the
Event Scheduler was considered to be 「suspended」; the
event scheduler thread ran and could be seen in the output of
SHOW PROCESSLIST (where
Suspended was displayed in the
State column), but did not execute any
scheduled events. The value of
could be changed only between
2 while the server
was running. Setting it to
OFF) required a server restart, as did changing
its value from
Prior to MySQL 5.1.11,
take one of only the 2 values
ON, and the default value
OFF. It was also
possible to start and stop the event scheduler thread while the
MySQL server was running.
For more information concerning the reasons for these changes in behaviour, see Bug #17619.
For SQL statements used to create, alter, and drop events, see 「Event Scheduler Syntax」.
MySQL 5.1.6 and later provides an
INFORMATION_SCHEMA database. This table
can be queried to obtian information about scheduled events which
have been defined on the server. See
「Event Metadata」, and
INFORMATION_SCHEMA EVENTS テーブル」, for more information.
For information regarding event scheduling and the MySQL privilege system, see 「The Event Scheduler and MySQL Privileges」.