Table of Contents
This chapter describes the MySQL Event Scheduler as supported in MySQL 6.0, and is divided into the following sections:
Section 20.1, “Event Scheduler Overview”, provides an introduction to and conceptual overview of MySQL Events.
Section 20.2, “Event Scheduler Syntax”, discusses the SQL statements available in MySQL 6.0 for creating, altering, and dropping MySQL Events.
Section 20.3, “Event Metadata”, shows how to obtain information about events and how this information is stored by the MySQL Server.
Section 20.5, “The Event Scheduler and MySQL Privileges”, discusses the privileges required to work with events and the ramifications that events have with regard to privileges when executing.
Section 20.6, “Event Scheduler Limitations and Restrictions”, describes the restrictions and limitations of MySQL's Event Scheduler implementation.
Additional Resources. You may find the MySQL Event Scheduler User Forum of use when working with scheduled events. Here you can discuss the MySQL Event Scheduler with other MySQL users and the MySQL developers.
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 19, 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:
In MySQL 6.0, an event is uniquely identified by its name and the schema to which it is assigned.
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 18.2.5, “BEGIN ... END
Compound Statement Syntax”). 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 Section 20.5, “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 20.2.1, “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 ALTER EVENT
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. See
Section 20.5, “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 SHOW
PROCESSLIST
, as shown in the discussion that follows.
The global variable event_scheduler
determines
whether the Event Scheduler is enabled and running on the server.
In MySQL 6.0, 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 non-operational. When the Event Scheduler is
DISABLED
, the event scheduler thread does
not run (and so does not appear in the output of SHOW
PROCESSLIST
).
When the server is runningevent_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 not possible to enable or disable the Event Scheduler when
the server is running. That is, you can change the value of
event_scheduler
to
DISABLED
— 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 an
error.
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=
command line option, or after removing or commenting out the line
containing DISABLED
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.
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 20.2, “Event Scheduler Syntax”.
MySQL 6.0 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 20.3, “Event Metadata”, and
Section 22.20, “The INFORMATION_SCHEMA EVENTS
Table”, for more information.
For information regarding event scheduling and the MySQL privilege system, see Section 20.5, “The Event Scheduler and MySQL Privileges”.
MySQL 6.0 provides several SQL statements for working with scheduled events:
New events are defined using the CREATE
EVENT
statement. See Section 20.2.2, “CREATE EVENT
Syntax”.
The definition of an existing event can be changed by means of
the ALTER EVENT
statement. See
Section 20.2.1, “ALTER EVENT
Syntax”.
When a scheduled event is no longer wanted or needed, it can
be deleted from the server by its definer using the
DROP EVENT
statement. See
Section 20.2.3, “DROP EVENT
Syntax”. (Whether an event persists past
the end of its schedule also depends on its ON
COMPLETION
clause, if it has one. See
Section 20.2.2, “CREATE EVENT
Syntax”.)
An event can be deleted by any user having the
EVENT
privilege for the database on which
the event is defined. See Section 20.5, “The Event Scheduler and MySQL Privileges”.
ALTER EVENT [DEFINER = {user
| CURRENT_USER }]event_name
[ON SCHEDULEschedule
] [ON COMPLETION [NOT] PRESERVE] [RENAME TOnew_event_name
] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment
'] [DOsql_statement
]
The ALTER EVENT
statement is used to change
one or more of the characteristics of an existing event without
the need to drop and recreate it. The syntax for each of the
DEFINER
, ON SCHEDULE
,
ON COMPLETION
, COMMENT
,
ENABLE
/ DISABLE
, and
DO
clauses is exactly the same as when used
with CREATE EVENT. (See Section 20.2.2, “CREATE EVENT
Syntax”.)
Any user can alter an event defined on a database for which that
user has the EVENT
privilege. When a user
executes a successful ALTER EVENT
statement,
that user becomes the definer for the affected event.
ALTER EVENT
works only with an existing
event:
mysql>ALTER EVENT no_such_event
>ON SCHEDULE
>EVERY '2:3' DAY_HOUR;
ERROR 1517 (HY000): Unknown event 'no_such_event'
In each of the following examples, assume that the event named
myevent
is defined as shown here:
CREATE EVENT myevent ON SCHEDULE EVERY 6 HOUR COMMENT 'A sample comment.' DO UPDATE myschema.mytable SET mycol = mycol + 1;
The following statement changes the schedule for
myevent
from once every six hours starting
immediately to once every twelve hours, starting four hours from
the time the statement is run:
ALTER EVENT myevent ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + 4 HOUR;
To disable myevent
, use this ALTER
EVENT
statement:
ALTER EVENT myevent DISABLE;
The ON SCHEDULE
clause may use expressions
involving built-in MySQL functions and user variables to obtain
any of the timestamp
or
interval
values which it contains.
You may not use stored routines or user-defined functions in
such expressions, nor may you use any table references; however,
you may use SELECT FROM DUAL
. This is true
for both ALTER EVENT
and CREATE
EVENT
statements. References to stored routines,
user-defined functions, and tables in such cases are
specifically disallowed, and fail with an error (see Bug#22830).
An ALTER EVENT
statement that contains
another ALTER EVENT
statement in its
DO
clause appears to succeed; however, when
the server attempts to execute the resulting scheduled event,
the execution fails with an error.
It is possible to change multiple characteristics of an event in
a single statement. This example changes the SQL statement
executed by myevent
to one that deletes all
records from mytable
; it also changes the
schedule for the event such that it executes once, one day after
this ALTER EVENT
statement is run.
ALTER TABLE myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY DO TRUNCATE TABLE myschema.mytable;
To rename an event, use the ALTER EVENT
statement's RENAME TO
clause, as shown here:
ALTER EVENT myevent RENAME TO yourevent;
The previous statement renames the event
myevent
to yourevent
.
There is no RENAME EVENT
statement.
You can also move an event to a different database using
ALTER EVENT ... RENAME TO ...
and
notation, as shown here:
db_name.table_name
ALTER EVENT olddb.myevent RENAME TO newdb.myevent;
In order to execute the previous statement, the user executing
it must have the EVENT
privilege on both the
olddb
and newdb
databases.
A third value may also appear in place of
ENABLED
or DISABLED
;
DISABLE ON SLAVE
is used on a replication
slave to indicate an event which was created on the master and
replicated to the slave, but which is not executed on the slave.
Normally, DISABLE ON SLAVE
is set
automatically as required; however, there are some circumstances
under which you may want or need to change it manually. See
Section 14.3.1.5, “Replication of Invoked Features”, for more
information.
It is necessary to include only those options in an
ALTER EVENT
statement which correspond to
characteristics that you actually wish to change; options which
are omitted retain their existing values. This includes any
default values for CREATE EVENT
such as
ENABLE
.
CREATE [DEFINER = {user
| CURRENT_USER }] EVENT [IF NOT EXISTS]event_name
ON SCHEDULEschedule
[ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment
'] DOsql_statement
;schedule
: ATtimestamp
[+ INTERVALinterval
] | EVERYinterval
[STARTStimestamp
[+ INTERVAL interval]] [ENDStimestamp
[+ INTERVAL interval]]interval
:quantity
{YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
This statement creates and schedules a new event. The minimum
requirements for a valid CREATE EVENT
statement are as follows:
The keywords CREATE EVENT
plus an event
name, which uniquely identifies the event in the current
schema.
An ON SCHEDULE
clause, which determines
when and how often the event executes.
A DO
clause, which contains the SQL
statement to be executed by an event.
This is an example of a minimal CREATE EVENT
statement:
CREATE EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE myschema.mytable SET mycol = mycol + 1;
The previous statement creates an event named
myevent
. This event executes once — one
hour following its creation — by running an SQL statement
that increments the value of the
myschema.mytable
table's
mycol
column by 1.
The event_name
must be a valid MySQL
identifier with a maximum length of 64 characters. It may be
delimited using back ticks, and may be qualified with the name
of a database schema. An event is associated with both a MySQL
user (the definer) and a schema, and its name must be unique
among names of events within that schema. In general, the rules
governing event names are the same as those for names of stored
routines. See Section 7.2, “Schema Object Names”.
If no schema is indicated as part of
event_name
, then the default
(current) schema is assumed. The definer is always the current
MySQL user.
MySQL uses case-insensitive comparisons when checking for the
uniqueness of event names. This means that, for example, you
cannot have two events named myevent
and
MyEvent
in the same database schema.
The DEFINER
clause specifies the MySQL
account to be used when checking access privileges at
triggerexecution time activation time. If a
user
value is given, it should be a
MySQL account in
'
format (the same format used in the user_name
'@'host_name
'GRANT
statement). The user_name
and
host_name
values both are required.
CURRENT_USER
also can be given
as CURRENT_USER()
. The default
DEFINER
value is the user who executes the
CREATE TRIGGER
statement. (This is the same
as DEFINER = CURRENT_USER
.)
IF NOT EXISTS
functions in the same fashion
with CREATE EVENT
as it does when used with a
CREATE TABLE
statement; if an event named
event_name
already exists in the same
schema, no action is taken, and no error results. (However, a
warning is generated in such cases.)
The ON SCHEDULE
clause determines when, how
often, and for how long the
sql_statement
defined for the event
repeats. This clause takes one of two forms:
AT
is used for a one-time event. It specifies that the event
executes one time only at the date and time, given as the
timestamp
timestamp
, which must include
both the date and time, or must be an expression that
resolves to a datetime value. You may use a value which is
of either the DATETIME
or
TIMESTAMP
type for this purpose. The
timestamp
must also be in the
future — you cannot schedule an event to take place in
the past. Trying to do so fails with an error, as shown
here:
mysql>SELECT NOW();
+---------------------+ | NOW() | +---------------------+ | 2006-02-10 23:59:01 | +---------------------+ 1 row in set (0.04 sec) mysql>CREATE EVENT e_totals
->ON SCHEDULE AT '2006-02-10 23:59:00'
->DO INSERT INTO test.totals VALUES (NOW());
ERROR 1522 (HY000): Activation (AT) time is in the past
CREATE EVENT
statements which are
themselves invalid — for whatever reason — fail
with an error.
You may use
CURRENT_TIMESTAMP
to specify
the current date and time. In such a case, the event acts as
soon as it is created.
In order to create an event which occurs at some point in
the future relative to the current date and time —
such as that expressed by the phrase “three weeks from
now” — you can use the optional clause
+ INTERVAL
. The
interval
interval
portion consists of two
parts, a quantity and a unit of time, and follows the same
syntax rules that govern intervals used in the
DATE_ADD()
function (see
Section 10.6, “Date and Time Functions”. The units
keywords are also the same, except that you cannot use any
units involving microseconds when defining an event.
You can also combine intervals. For example, AT
CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2
DAY
is equivalent to “three weeks and two
days from now”. Each portion of such a clause must
begin with + INTERVAL
.
For actions which are to be repeated at a regular interval,
you can use an EVERY
clause. The
EVERY
keyword is followed by an
interval
as described in the
previous dicussion of the AT
keyword.
(+ INTERVAL
is not
used with EVERY
.) For example,
EVERY 6 WEEK
means “every six
weeks”.
It is not possible to combine + INTERVAL
clauses in a single EVERY
clause;
however, you can use the same complex time units allowed in
a + INTERVAL
. For example, “every
two minutes and ten seconds” can be expressed as
EVERY '2:10' MINUTE_SECOND
.
An EVERY
clause may also contain an
optional STARTS
clause.
STARTS
is followed by a
timestamp
value which indicates
when the action should begin repeating, and may also use
+ INTERVAL
in order to
specify an amount of time “from now”. For
example, interval
EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1
WEEK
means “every three months, beginning
one week from now”. Similarly, you can express
“every two weeks, beginning six hours and fifteen
minutes from now” as EVERY 2 WEEK STARTS
CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE
.
Not specifying STARTS
is the same as
using STARTS CURRENT_TIMESTAMP
—
that is, the action specified for the event begins repeating
immediately upon creation of the event.
An EVERY
clause may also contain an
optional ENDS
clause. The
ENDS
keyword is followed by a
timestamp
value which tells MySQL
when the event should stop repeating. You may also use
+ INTERVAL
with
interval
ENDS
; for instance, EVERY 12
HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS
CURRENT_TIMESTAMP + INTERVAL 4 WEEK
is equivalent
to “every twelve hours, beginning thirty minutes from
now, and ending four weeks from now”. Not using
ENDS
means that the event continues
executing indefinitely.
ENDS
supports the same syntax for complex
time units as STARTS
does.
You may use STARTS
,
ENDS
, both, or neither in an
EVERY
clause.
In MySQL 6.0, STARTS
or
ENDS
uses the MySQL server's local time
zone, as shown in the
INFORMATION_SCHEMA.EVENTS
and
mysql.event
tables, as well as in the
output of SHOW EVENTS
.
See Section 22.20, “The INFORMATION_SCHEMA EVENTS
Table”, and
Section 11.5.4.16, “SHOW EVENTS
” for more information.
The ON SCHEDULE
clause may use expressions
involving built-in MySQL functions and user variables to obtain
any of the timestamp
or
interval
values which it contains.
You may not use stored routines or user-defined functions in
such expressions, nor may you use any table references; however,
you may use SELECT FROM DUAL
. This is true
for both CREATE EVENT
and ALTER
EVENT
statements. References to stored routines,
user-defined functions, and tables in such cases are
specifically disallowed, and fail with an error (see Bug#22830).
Normally, once an event has expired, it is immediately dropped.
You can override this behavior by specifying ON
COMPLETION PRESERVE
. Using ON COMPLETION NOT
PRESERVE
merely makes the default non-persistent
behavior explicit.
You can create an event but keep it from being active using the
DISABLE
keyword. Alternatively, you may use
ENABLE
to make explicit the default status,
which is active. This is most useful in conjunction with
ALTER EVENT
(see
Section 20.2.1, “ALTER EVENT
Syntax”).
A third value may also appear in place of
ENABLED
or DISABLED
;
DISABLE ON SLAVE
is set for the status of an
event on a replication slave to indicate that the event was
created on the master and replicated to the slave, but is not
executed on the slave. See
Section 14.3.1.5, “Replication of Invoked Features”.
You may supply a comment for an event using a
COMMENT
clause.
comment
may be any string of up to 64
characters that you wish to use for describing the event. The
comment text, being a string literal, must be surrounded by
quotation marks.
The DO
clause specifies an action carried by
the event, and consists of an SQL statement. Nearly any valid
MySQL statement which can be used in a stored routine can also
be used as the action statement for a scheduled event. (See
Section D.1, “Restrictions on Stored Routines and Triggers”.) For example, the
following event e_hourly
deletes all rows
from the sessions
table once per hour, where
this table is part of the site_activity
schema:
CREATE EVENT e_hourly ON SCHEDULE EVERY 1 HOUR COMMENT 'Clears out sessions table each hour.' DO DELETE FROM site_activity.sessions;
MySQL stores the sql_mode
system variable
setting that is in effect at the time an event is created, and
always executes the event with this setting in force,
regardless of the current server SQL mode.
A CREATE EVENT
statement that contains an
ALTER EVENT
statement in its
DO
clause appears to succeed; however, when
the server attempts to execute the resulting scheduled event,
the execution fails with an error.
The SHOW
statement and
SELECT
statements that merely return a
result set have no effect when used in an event; the output
from these is not sent to the MySQL Monitor, nor is it stored
anywhere. However, you can use statements such as
SELECT INTO
and INSERT ...
SELECT
that store a result. (See the next example in
this section for an instance of the latter.)
Any reference in the DO
clause to a table in
other than the same database schema to which the event belongs
must be qualified with the name of the schema in which the table
occurs.
As with stored routines, you can use multiple statements in the
DO
clause by bracketing them with the
BEGIN
and END
keywords, as
shown here:
DELIMITER | CREATE EVENT e_daily ON SCHEDULE EVERY 1 DAY COMMENT 'Saves total number of sessions then clears the table each day.' DO BEGIN INSERT INTO site_activity.totals (when, total) SELECT CURRENT_TIMESTAMP, COUNT(*) FROM site_activity.sessions; DELETE FROM site_activity.sessions; END | DELIMITER ;
Note the use of the DELIMITER
statement to
change the statement delimiter, as with stored routines. See
Section 18.2.1, “CREATE PROCEDURE
and CREATE
FUNCTION
Syntax”.
More complex compound statements, such as those used in stored routines, are possible in an event. This example uses local variables, an error handler, and a flow control construct:
DELIMITER | CREATE EVENT e ON SCHEDULE EVERY 5 SECOND DO BEGIN DECLARE v INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; SET v = 0; WHILE v < 5 DO INSERT INTO t1 VALUES (0); UPDATE t2 SET s1 = s1 + 1; SET v = v + 1; END WHILE; END | DELIMITER ;
There is no way to pass parameters directly to or from events; however, it is possible to invoke a stored routine with parameters:
CREATE EVENT e_call_myproc ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY DO CALL myproc(5, 27);
In addition, if the event's definer has the
SUPER
privilege, that event may read and
write global variables. As granting this privilege entails a
potential for abuse, extreme care must be taken in doing so.
Generally, any statements which are valid in stored routines may be used for action statements executed by events. For more information about statements allowable within stored routines, see Section 18.2, “Stored Routine Syntax”. You can create an event as part of a stored routine, but an event cannot be created by another event.
DROP EVENT [IF EXISTS] event_name
This statement drops the event named
event_name
. The event immediately
ceases being active, and is deleted completely from the server.
If the event does not exist, the error ERROR 1517
(HY000): Unknown event
'event_name
' results. You
can override this and cause the statement to fail silently by
using IF EXISTS
.
An event can be dropped by any user having the
EVENT
privilege on the database schema to
which the event to be dropped belongs.
Information about events can be obtained as follows:
Querying the EVENTS
table of the
INFORMATION_SCHEMA
database. See
Section 22.20, “The INFORMATION_SCHEMA EVENTS
Table”.
Using the SHOW EVENTS
statement. See
Section 11.5.4.16, “SHOW EVENTS
”.
Using the SHOW CREATE EVENT
statement. See
Section 11.5.4.7, “SHOW CREATE EVENT
”.
A record of events executed on the server can be read from the MySQL Server's error log (see Section 20.5, “The Event Scheduler and MySQL Privileges” for an example).
Information about the state of the Event Scheduler for debugging and troubleshooting purposes can be obtained by running mysqladmin debug (see Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”); after running this command, the error log contains output relating to the Event Scheduler, similar to what is shown here:
Events status: LLA = Last Locked At LUA = Last Unlocked At WOC = Waiting On Condition DL = Data Locked Event scheduler status: State : INITIALIZED Thread id : 0 LLA : init_scheduler:313 LUA : init_scheduler:318 WOC : NO Workers : 0 Executed : 0 Data locked: NO Event queue status: Element count : 1 Data locked : NO Attempting lock : NO LLA : init_queue:148 LUA : init_queue:168 WOC : NO Next activation : 0000-00-00 00:00:00
To enable or disable the execution of scheduled events, it is
necessary to set the value of the global
event_scheduler
variable. This requires the
SUPER
privilege.
The EVENT
privilege governs the creation,
modification, and deletion of events. This privilege can be
bestowed using GRANT
. For example, this
GRANT
statement confers the
EVENT
privilege for the schema named
myschema
on the user
jon@ghidora
:
GRANT EVENT ON myschema.* TO jon@ghidora;
(We assume that this user account already exists, and that we wish for it to remain unchanged otherwise.)
To grant this same user the EVENT
privilege on
all schemas would require the following statement:
GRANT EVENT ON *.* TO jon@ghidora;
The EVENT
privilege has schema-level scope.
Therefore, trying to grant it on a single table results in an
error as shown:
mysql> GRANT EVENT ON myschema.mytable TO jon@ghidora;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please
consult the manual to see which privileges can be used
It is important to understand that an event is executed with the
privileges of its definer, and that it cannot perform any actions
for which its definer does not have the requisite privileges. For
example, suppose that jon@ghidora
has the
EVENT
privilege for
myschema
. Suppose also that this user has the
SELECT
privilege for
myschema
, but no other privileges for this
schema. It is possible for jon@ghidora
to
create a new event such as this one:
CREATE EVENT e_store_ts ON SCHEDULE EVERY 10 SECOND DO INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
The user waits for a minute or so, and then performs a
SELECT * FROM mytable;
query, expecting to see
several new rows in the table. Instead, he finds that the table is
empty. Since he does not have the INSERT
privilege for the table in question, the event has no effect.
If you inspect the MySQL error log
(
),
you can see that the event is executing, but the action it is
attempting to perform fails, as indicated by
hostname
.errRetCode=0
:
060209 22:39:44 [Note] EVEX EXECUTING event newdb.e [EXPR:10] 060209 22:39:44 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0 060209 22:39:54 [Note] EVEX EXECUTING event newdb.e [EXPR:10] 060209 22:39:54 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0 060209 22:40:04 [Note] EVEX EXECUTING event newdb.e [EXPR:10] 060209 22:40:04 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0
Since this user very likely does not have access to the error log, he can verify whether the event's action statement is valid by running it himself:
mysql> INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
ERROR 1142 (42000): INSERT command denied to user
'jon'@'ghidora' for table 'mytable'
Inspection of the INFORMATION_SCHEMA.EVENTS
table shows that e_store_ts
exists and is
enabled, but its LAST_EXECUTED
column is
NULL
:
mysql>SELECT * FROM INFORMATION_SCHEMA.EVENTS
>WHERE EVENT_NAME='e_store_ts'
>AND EVENT_SCHEMA='myschema'\G
*************************** 1. row *************************** EVENT_CATALOG: NULL EVENT_SCHEMA: myschema EVENT_NAME: e_store_ts DEFINER: jon@ghidora EVENT_BODY: SQL EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP()) EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 5 INTERVAL_FIELD: INTERVAL_SECOND SQL_MODE: NULL STARTS: 0000-00-00 00:00:00 ENDS: 0000-00-00 00:00:00 STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2006-02-09 22:36:06 LAST_ALTERED: 2006-02-09 22:36:06 LAST_EXECUTED: NULL EVENT_COMMENT: 1 row in set (0.00 sec)
To rescind the EVENT
privilege, use the
REVOKE
statement. In this example, the
EVENT
privilege on the schema
myschema
is removed from the
jon@ghidora
user account:
REVOKE EVENT ON myschema.* FROM jon@ghidora;
Revoking the EVENT
privilege from a user does
not delete or disable any events that may have been created by
that user.
An event is not migrated or dropped as a result of the renaming or dropping of the user who created it.
For example, suppose that that user jon@ghidora
has been granted the EVENT
and
INSERT
privileges on the
myschema
schema. This user then creates the
following event:
CREATE EVENT e_insert ON SCHEDULE EVERY 7 SECOND DO INSERT INTO myschema.mytable;
After this event has been created, root
revokes
the EVENT
privilege for
jon@ghidora
. However,
e_insert
continues to execute, inserting a new
row into mytable
each seven seconds. The same
would be true if root
had issued either of
these statements:
DROP USER jon@ghidora;
RENAME USER jon@ghidora TO
someotherguy@ghidora;
You can verify that this is true by examining the
mysql.event
table (discussed later in this
section) or the INFORMATION_SCHEMA.EVENTS
table
(see Section 22.20, “The INFORMATION_SCHEMA EVENTS
Table”) before and after issuing a
DROP USER
or RENAME USER
statement.
Event definitions are stored in the mysql.event
table. To drop an event created by another user account, the MySQL
root
user (or another user with the necessary
privileges) can delete rows from this table. For example, to
remove the event e_insert
shown previously,
root
can use the following statement:
DELETE FROM mysql.event WHERE db = 'myschema' AND definer = 'jon@ghidora' AND name = 'e_insert';
It is very important to match the event name, database schema
name, and user account when deleting rows from the
mysql.event
table. This is because the same
user can create different events of the same name in different
schemas.
Users' EVENT
privileges are stored in the
Event_priv
columns of the
mysql.user
and mysql.db
tables. In both cases, this column holds one of the values
'Y
' or 'N
'.
'N
' is the default.
mysql.user.Event_priv
is set to
'Y
' for a given user only if that user has the
global EVENT
privilege (that is, if the
privilege was bestowed using GRANT EVENT ON
*.*
). For a schema-level EVENT
privilege, GRANT
creates a row in
mysql.db
and sets that row's
Db
column to the name of the schema, the
User
column to the name of the user, and the
Event_priv
column to 'Y
'.
There should never be any need to manipulate these tables
directly, since the GRANT EVENT
and
REVOKE EVENT
statement perform the required
operations on them.
Five status variables provide counts of event-related operations (but not of statements executed by events — see Section 20.6, “Event Scheduler Limitations and Restrictions”). These are:
Com_create_event
: The number of
CREATE EVENT
statements executed since the
last server restart.
Com_alter_event
: The number of
ALTER EVENT
statements executed since the
last server restart.
Com_drop_event
: The number of DROP
EVENT
statements executed since the last server
restart.
Com_show_create_event
: The number of
SHOW CREATE EVENT
statements executed since
the last server restart.
Com_show_events
: The number of
SHOW EVENTS
statements executed since the
last server restart.
You can view current values for all of these at one time by
running the statement SHOW STATUS LIKE
'%event%';
.
This section lists restrictions and limitations applying to event scheduling in MySQL 6.0.
Case sensitivity of event identifiers.
Event names are handled in case-insensitive fashion. For
example, this means that you cannot have two events in the same
database with the names anEvent
and
AnEvent
.
Modification of events by stored routines and triggers. An event may not be created, altered, or dropped by a trigger, stored routine, or another event. An event also may not create, alter, or drop triggers or stored routines. (Bug#16409, Bug#18896)
Resolution of event timings.
Event timings using the intervals YEAR
,
QUARTER
, MONTH
, and
YEAR_MONTH
are resolved in months; those
using any other interval are resolved in seconds. There is no
way to cause events scheduled to occur at the same second to
execute in a given order. In addition — due to rounding,
the nature of threaded applications, and the fact that a
non-zero length of time is required to create events and to
signal their execution — events may be delayed by as much
as 1 or 2 seconds. However, the time shown in the
INFORMATION_SCHEMA.EVENTS
table's
LAST_EXECUTED
column or the
mysql.event
table's
last_executed
column is always accurate to
within one second of the time the event was actually executed.
(See also Bug#16522.)
Effects on statement counts.
Each execution of the statements contained in the body of an
event takes place in a new connection; thus, these statements
has no effect in a given user session on the server's statement
counts such as Com_select
and
Com_insert
that are displayed by
SHOW STATUS
. However, such counts
are updated in the global scope. (Bug#16422)
Start times. Events cannot be created with a start time that is in the past.
Latest time supported. Events do not support times later than the end of the Unix Epoch; this is approximately the end of the year 2038. Such dates are specifically disallowed by the Event Scheduler. (Bug#16396)
Server SQL mode.
The SQL_MODE
column in
INFORMATION_SCHEMA.EVENTS
shows the server
SQL mode that was in effect when the event was created.
Dropping or altering events.
DROP USER
drops all events for which that
user was the definer; DROP SCHEMA
drops all
events associated with the dropped schema.
Database object references in ON SCHEDULE
clauses.
References to stored routines, user-defined functions, and
tables in the ON SCHEDULE
clauses of
CREATE EVENT
and ALTER
EVENT
statements are not supported. These sorts of
references are disallowed. (See Bug#22830 for more
information.)
Disallowed statements. Generally speaking, statements which are not permitted in stored routines or in SQL prepared statements are also not allowed in the body of an event. See Section D.1, “Restrictions on Stored Routines and Triggers”, and Section 11.7, “SQL Syntax for Prepared Statements”, for more information.