Skip navigation links

Additional languages
MySQL 5.1 Reference Manual :: 24 Event Scheduler :: 24.6 Event Scheduler Limitations and Restrictions
25 Views »
Section Navigation      [Toggle]

24.6. Event Scheduler Limitations and Restrictions

This section lists restrictions and limitations applying to event scheduling in MySQL 5.1.

Qualification of identifiers.  In MySQL 5.1.6 only, any table referenced in an event's action statement must be fully qualified with the name of the schema in which it occurs (that is, as schema_name.table_name).

Case sensitivity of event identifiers.  Beginning with MySQL 5.1.8, event names are handled in case-insensitive fashion. For example, this means that you cannot have two events in the same database (and — prior to MySQL 5.1.12 — with the same definer) with the names anEvent and AnEvent.

Important

If you have events created in MySQL 5.1.7 or earlier which are assigned to the same database and have the same definer, and whose names differ only with respect to lettercase, then you must rename these events to respect case-sensitive handling before upgrading to MySQL 5.1.8 or later.

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)

Visibility of events belonging to other users.  Prior to MySQL 5.1.12, you could not view another user's events in the INFORMATION_SCHEMA.EVENTS table. In other words, any query made against this table was treated as though it contained the condition DEFINER = CURRENT_USER() in the WHERE clause.

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 beginning of the year 2038. Prior to MySQL 5.1.8, handling in scheduled events of dates later than this was buggy; starting with MySQL 5.1.8, such dates are specifically disallowed by the Event Scheduler. (Bug#16396)

Server SQL mode.  In MySQL 5.1.6, INFORMATION_SCHEMA.EVENTS shows NULL in the SQL_MODE column. Beginning with MySQL 5.1.7, the SQL_MODE displayed is that in effect when the event was created.

Dropping or altering events.  In MySQL 5.1.6, the only way to drop or alter an event created by a user who was not the definer of that event was by manipulation of the mysql.event system table by the MySQL root user or by another user with privileges on this table. Beginning with MySQL 5.1.7, DROP USER drops all events for which that user was the definer; also beginning with MySQL 5.1.7 DROP SCHEMA drops all events associated with the dropped schema.

Database object references in ON SCHEDULE clauses.  References to stored functions, user-defined functions, and tables in the ON SCHEDULE clauses of CREATE EVENT and ALTER EVENT statements are not supported. Beginning with MySQL 5.1.13, 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, Triggers, and Events”, and Section 12.7, “SQL Syntax for Prepared Statements”, for more information.

Upgrading to MySQL 5.1.18 or later.  When upgrading to MySQL 5.1.18 or later from a previous MySQL version where scheduled events were in use, the upgrade utilities mysql_upgrade and mysql_fix_privilege_tables do not accomodate changes in system tables relating to the Event Scheduler. As a workaround, you can dump events before the upgrade, then restore them from the dump afterwards. This issue was fixed in MySQL 5.1.20 (see Bug#28521).