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
PROCESS
privilege in the output of
SHOW PROCESSLIST
, as shown in the
discussion that follows.
The global event_scheduler
system
variable determines 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
output of SHOW PROCESSLIST
, and
no scheduled events are executed. OFF
is
the default value for
event_scheduler
.
When the Event Scheduler is stopped
(event_scheduler
is
OFF
), it can be started by setting the
value of event_scheduler
to
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
SHOW PROCESSLIST
as a daemon
process, and its state is represented as shown here:
mysql> 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
event_scheduler
to
OFF
.
DISABLED
: This value renders the Event
Scheduler nonoperational. When the Event Scheduler is
DISABLED
, the event scheduler thread does
not run (and so does not appear in the output of
SHOW PROCESSLIST
). In addition,
the Event Scheduler state cannot be changed at runtime.
If the Event Scheduler status has not been set to
DISABLED
,
event_scheduler
can be toggled
between ON
and OFF
(using
SET
). It is
also possible to use 0
for
OFF
, and 1
for
ON
when setting this variable. 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;
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;
Although ON
and OFF
have
numeric equivalents, the value displayed for
event_scheduler
by
SELECT
or SHOW
VARIABLES
is always one of OFF
,
ON
, or DISABLED
.
DISABLED
has no numeric
equivalent. For this reason, ON
and
OFF
are usually preferred over
1
and 0
when setting this
variable.
Note that attempting to set
event_scheduler
without
specifying it as a global variable causes an error:
mysql< SET @@event_scheduler = OFF;
ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL
variable and should be set with SET GLOBAL
It is possible to set the Event Scheduler to
DISABLED
only at server startup. If
event_scheduler
is
ON
or OFF
, you cannot set
it to DISABLED
at runtime. Also, if the Event
Scheduler is set to DISABLED
at startup, you
cannot change the value of
event_scheduler
at runtime.
To disable the event scheduler, use one of the following two methods:
As a command-line option when starting the server:
--event-scheduler=DISABLED
In the server configuration file (my.cnf
,
or my.ini
on Windows systems), include
the line where it will be read by the server (for example, in
a [mysqld]
section):
event_scheduler=DISABLED
To enable the Event Scheduler, restart the server without the
--event-scheduler=DISABLED
command-line option, or after removing or commenting out the line
containing event-scheduler=DISABLED
in the server configuration file, as appropriate. Alternatively,
you can use ON
(or 1
) or
OFF
(or 0
) in place of the
DISABLED
value when starting the server.
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 set to ON
(or
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
0
(or OFF
),
1
(or ON
), or
2
. Setting it to 0
turned
event scheduling off, so that the event scheduler thread did not
run; the 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
SHOW PROCESSLIST
. When
event_scheduler
was set to
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
event_scheduler
could be changed
only between 1
(or ON
) and
2
while the server was running. Setting it to
0
(or OFF
) required a server
restart, as did changing its value from 0
(or
OFF
) to 1
(or
ON
) or 2
.
Prior to MySQL 5.1.11,
event_scheduler
could take one of
only the 2 values 0
|OFF
or
1
|ON
, and the default value
was 0
|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 behavior, see Bug#17619.
Beginning with MySQL 5.1.17, starting the MySQL server with the
--skip-grant-tables
option causes
event_scheduler
to be set to
DISABLED
, overriding any other value set either
on the command line or in the my.cnf
or
my.ini
file (Bug#26807).
For SQL statements used to create, alter, and drop events, see Section 19.4.3, “Event Syntax”.
MySQL 5.1.6 and later provides an
EVENTS
table in the
INFORMATION_SCHEMA
database. This table can be
queried to obtain information about scheduled events which have
been defined on the server. See Section 19.4.4, “Event Metadata”,
and Section 20.20, “The INFORMATION_SCHEMA EVENTS
Table”, for more information.
For information regarding event scheduling and the MySQL privilege system, see Section 19.4.6, “The Event Scheduler and MySQL Privileges”.
User Comments
Add your own comment.