D.1 Restrictions on Stored Programs

Some of the restrictions noted here apply to all stored routines;
that is, both to stored procedures and stored functions. There are
also some
restrictions
specific to stored functions but not to stored procedures.

SQL Statements Not Permitted in Stored Routines

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).

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”.

Inserts cannot be delayed. INSERT
DELAYED syntax is accepted, but the statement is
handled as a normal INSERT.

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.

Restrictions for Stored Functions

The following additional statements or operations are not
permitted within stored functions. They are permitted within
stored procedures, except stored procedures that are invoked from
within a stored function or trigger. For example, if you use
FLUSH in a stored procedure, that
stored procedure cannot be called from a stored function or
trigger.

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.

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.

Restrictions for Triggers

For triggers, the following additional restrictions apply:

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.32, “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.

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.

Name Conflicts within Stored Routines

The same identifier might be used for a routine parameter, a local
variable, and a table column. Also, the same local variable name
can be used in nested blocks. For example:

Replication Considerations

The
--replicate-wild-do-table=db_name.tbl_name
option applies to tables, views, and triggers. It does not apply
to stored procedures and functions, or events. To filter
statements operating on the latter objects, use one or more of the
--replicate-*-db options.

Debugging Considerations

There are no stored routine debugging facilities.

Unsupported Syntax from the SQL:2003 Standard

The MySQL stored routine syntax is based on the SQL:2003 standard.
The following items from that standard are not currently
supported:

UNDO handlers

FOR loops

Concurrency Considerations

To prevent problems of interaction between sessions, when a client
issues a statement, the server uses a snapshot of routines and
triggers available for execution of the statement. That is, the
server calculates a list of procedures, functions, and triggers
that may be used during execution of the statement, loads them,
and then proceeds to execute the statement. While the statement
executes, it does not see changes to routines performed by other
sessions.

For maximum concurrency, stored functions should minimize their
side-effects; in particular, updating a table within a stored
function can reduce concurrent operations on that table. A stored
function acquires table locks before executing, to avoid
inconsistency in the binary log due to mismatch of the order in
which statements execute and when they appear in the log. When
statement-based binary logging is used, statements that invoke a
function are recorded rather than the statements executed within
the function. Consequently, stored functions that update the same
underlying tables do not execute in parallel. In contrast, stored
procedures do not acquire table-level locks. All statements
executed within stored procedures are written to the binary log,
even for statement-based binary logging. See
Section 20.7, “Binary Logging of Stored Programs”.

Event Scheduler Restrictions

The following limitations are specific to the Event Scheduler:

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 by a stored
routine, trigger, or another event. An event also may not
create, alter, or drop stored routines or triggers. (Bug
#16409, Bug #18896)

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.)