To enable or disable the execution of scheduled events, it is
necessary to set the value of the global
event_scheduler system variable.
This requires privileges sufficient to set global system
variables. See Section 126.96.36.199, “System Variable Privileges”.
EVENT privilege governs the
creation, modification, and deletion of events. This privilege can
be bestowed using
EVENT privilege for the
myschema on the user
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
privilege on all schemas, use the following statement:
GRANT EVENT ON *.* TO jon@ghidora;
EVENT privilege has global or
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
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, the table is empty. Since
the user does not have the
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
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, it is possible to verify whether the event's action statement is valid by executing it directly:
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
e_store_ts exists and is enabled, but its
LAST_EXECUTED column is
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: 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)
REVOKE EVENT ON myschema.* FROM jon@ghidora;
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 renaming or dropping the user who created it.
CREATE EVENT e_insert ON SCHEDULE EVERY 7 SECOND DO INSERT INTO myschema.mytable;
After this event has been created,
EVENT privilege for
e_insert continues to execute, inserting a new
mytable each seven seconds. The same
would be true if
root had issued either of
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 21.8, “The INFORMATION_SCHEMA EVENTS Table”) before and
after issuing a
DROP USER or
RENAME USER statement.
Event definitions are stored in the
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 name = 'e_insert';
It is very important to match the event name and database schema
name when deleting rows from the
table. This is because different events of the same name can exist
in different schemas.
EVENT privileges are stored
Event_priv columns of the
tables. In both cases, this column holds one of the values
Y' or '
N' is the default.
mysql.user.Event_priv is set to
Y' for a given user only if that user has the
EVENT privilege (that is, if
the privilege was bestowed using
GRANT EVENT ON
*.*). For a schema-level
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 '
There should never be any need to manipulate these tables
directly, since the
REVOKE EVENT statements
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.8, “Restrictions on Stored Programs”). These are:
Com_create_event: The number of
CREATE EVENTstatements executed since the last server restart.
Com_alter_event: The number of
ALTER EVENTstatements executed since the last server restart.
Com_drop_event: The number of
DROP EVENTstatements executed since the last server restart.
Com_show_create_event: The number of
SHOW CREATE EVENTstatements executed since the last server restart.
Com_show_events: The number of
SHOW EVENTSstatements 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