These restrictions apply to the features described in Chapter 20, Stored Programs and Views.
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.
The restrictions for stored functions also apply to triggers. There are also some restrictions specific to triggers.
Stored routines cannot contain arbitrary SQL statements. The following statements are not permitted:
LOAD DATA and
SQL prepared statements
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
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, “SQL Syntax for Prepared Statements”. Exceptions
RESIGNAL, which are not
permissible as prepared statements but are permitted in stored
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
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
as the beginning of a
END block. To begin a transaction in this context,
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
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
clause and other
statements such as
CHECK TABLE. A function can
process a result set either with
SELECT ... INTO
or by using a
See Section 188.8.131.52, “SELECT ... INTO Syntax”, and
Section 13.6.6, “Cursors”.
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
error occurs, even if the references occur in different
statements within the function.
READ statements that invoke stored functions can
cause replication errors. As of MySQL 5.5.7, such statements
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 184.108.40.206, “Replication and Triggers”.
Triggers are not permitted on tables in the
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.
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:
CREATE PROCEDURE p (i INT) BEGIN DECLARE i INT DEFAULT 0; SELECT i FROM t; BEGIN DECLARE i INT DEFAULT 1; SELECT i FROM t; END; END;
In such cases, the identifier is ambiguous and the following precedence rules apply:
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.
The behavior that variables take precedence over table columns is nonstandard.
Use of stored routines can cause replication problems. This issue is discussed further in Section 20.7, “Binary Logging of Stored Programs”.
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
There are no stored routine debugging facilities.
The MySQL stored routine syntax is based on the SQL:2003 standard. The following items from that standard are not currently supported:
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”.
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
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)
As of MySQL 5.5.8, DDL statements on events are prohibited
LOCK TABLES statement
is in effect.
Event timings using the intervals
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
LAST_EXECUTED column or the
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_insert that are displayed by
SHOW STATUS. However, such
counts are updated in the global scope.
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.)
Stored routines and triggers in MySQL Cluster.
Stored procedures, stored functions, and triggers are all
supported by tables using the
storage engine; however, it is important to keep in mind that
they do not propagate automatically between
MySQL Servers acting as Cluster SQL nodes. This is because of
Stored routine definitions are kept in tables in the
mysql system database using the
MyISAM storage engine, and so do not
participate in clustering.
files containing trigger definitions are not read by the
NDB storage engine, and are not
copied between Cluster nodes.
Any stored routine or trigger that interacts with MySQL Cluster
tables must be re-created by running the appropriate
CREATE FUNCTION, or
CREATE TRIGGER statements on each
MySQL Server that participates in the cluster where you wish to
use the stored routine or trigger. Similarly, any changes to
existing stored routines or triggers must be carried out
explicitly on all Cluster SQL nodes, using the appropriate
DROP statements on
each MySQL Server accessing the cluster.
Do not attempt to work around the issue
described in the first item mentioned previously by converting
mysql database tables to use the
NDB storage engine.
Altering the system tables in the
mysql database is not supported
and is very likely to produce undesirable results.