C.1 Restrictions on Stored Programs

The locking statements LOCK TABLES and UNLOCK TABLES.

ALTER VIEW.

LOAD DATA and LOAD TABLE.

SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them).

Generally, statements not permitted in SQL prepared statements are also not permitted in stored programs. For a list of statements supported as prepared statements, see Section 13.5, “Prepared SQL Statement Syntax”. Exceptions are SIGNAL, RESIGNAL, and GET DIAGNOSTICS, which are not permissible as prepared statements but are permitted in stored programs.

Because local variables are in scope only during stored program execution, references to them are not permitted in prepared statements created within a stored program. Prepared statement scope is the current session, not the stored program, so the statement could be executed after the program ends, at which point the variables would no longer be in scope. For example, SELECT ... INTO local_var cannot be used as a prepared statement. This restriction also applies to stored procedure and function parameters. See Section 13.5.1, “PREPARE Syntax”.

Within all stored programs (stored procedures and functions, triggers, and events), the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block. To begin a transaction in this context, use START TRANSACTION instead.

Statements that perform explicit or implicit commit or rollback. Support for these statements is not required by the SQL standard, which states that each DBMS vendor may decide whether to permit them.

Statements that return a result set. This includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE. A function can process a result set either with SELECT ... INTO var_list or by using a cursor and FETCH statements. See Section 13.2.10.1, “SELECT ... INTO Syntax”, and Section 13.6.6, “Cursors”.

FLUSH statements.

Stored functions cannot be used recursively.

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

If you refer to a temporary table multiple times in a stored function under different aliases, a Can't reopen table: 'tbl_name' error occurs, even if the references occur in different statements within the function.

HANDLER ... READ statements that invoke stored functions can cause replication errors and are disallowed.

Triggers are not activated by foreign key actions.

When using row-based replication, triggers on the slave are not activated by statements originating on the master. The triggers on the slave are activated when using statement-based replication. For more information, see Section 17.4.1.35, “Replication and Triggers”.

The RETURN statement is not permitted in triggers, which cannot return a value. To exit a trigger immediately, use the LEAVE statement.

Triggers are not permitted on tables in the mysql database. Nor are they permitted on INFORMATION_SCHEMA or performance_schema tables. Those tables are actually views and triggers are not permitted on views.

The trigger cache does not detect when metadata of the underlying objects has changed. If a trigger uses a table and the table has changed since the trigger was loaded into the cache, the trigger operates using the outdated metadata.

A local variable takes precedence over a routine parameter or table column.

A routine parameter takes precedence over a table column.

A local variable in an inner block takes precedence over a local variable in an outer block.

UNDO handlers

FOR loops

Event names are handled in case-insensitive fashion. For example, you cannot have two events in the same database with the names anEvent and AnEvent.

An event may not be created, altered, or dropped from within a stored program, if the event name is specified by means of a variable. An event also may not create, alter, or drop stored routines or triggers.

DDL statements on events are prohibited while a LOCK TABLES statement is in effect.

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 nonzero 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 actual event execution time. (See also Bug #16522.)

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)

Events do not support times later than the end of the Unix Epoch; this is approximately the beginning of the year 2038. Such dates are specifically not permitted by the Event Scheduler. (Bug #16396)

References to stored functions, 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 not permitted. (See Bug #22830 for more information.)