Skip navigation links

Additional languages
MySQL 5.1 Reference Manual :: D Restrictions and Limits :: D.1 Restrictions on Stored Routines, Triggers, and Events
D.2 Restrictions on Server-Side Cursors »
Section Navigation      [Toggle]

D.1. Restrictions on Stored Routines, Triggers, and Events

Some of the restrictions noted here apply to all stored routines; that is, both to stored procedures and stored functions. Some of these restrictions apply only to stored functions, and not to stored procedures.

All of the restrictions for stored functions also apply to triggers.

All of the restrictions for stored procedures also apply to the DO clause of event definitions. For additional event-related restrictions, see Section 25.6, “Event Scheduler Limitations and Restrictions”.

Stored routines cannot contain arbitrary SQL statements. The following statements are disallowed:

  • The locking statements LOCK TABLES, UNLOCK TABLES.

  • ALTER VIEW. (Before MySQL 5.1.21, this restriction is enforced only for stored functions.)

  • LOAD DATA and LOAD TABLE.

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

    In addition, SQL statements that are not permitted within prepared statements are also not permitted in stored routines. See Section 12.7, “SQL Syntax for Prepared Statements”, for a list of statements supported in prepared statements. Statements not listed there are not supported for SQL prepared statements and thus are also not supported for stored routines unless noted otherwise in Chapter 23, Stored Procedures and Functions.

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

For stored functions (but not stored procedures), the following additional statements or operations are disallowed:

  • Statements that do explicit or implicit commit or rollback.

  • Statements that return a result set. This includes SELECT statements that do not have an INTO var_list clause and SHOW statements. A function can process a result set either with SELECT ... INTO var_list or by using a cursor and FETCH statements. See Section 23.2.7.3, “SELECT ... INTO Statement”.

  • FLUSH statements.

  • Recursive statements. That is, stored functions cannot be used recursively.

  • Within a stored function or trigger, it is not permitted to 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.

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

Note that although some restrictions normally apply to stored functions and triggers but not to stored procedures, those restrictions do apply to stored procedures if they are invoked from within a stored function or trigger. For example, although you can use FLUSH in a stored procedure, such a stored procedure cannot be called from a stored function or trigger.

It is possible for the same identifier to 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 non-standard.

Use of stored routines can cause replication problems. This issue is discussed further in Section 23.4, “Binary Logging of Stored Routines and Triggers”.

INFORMATION_SCHEMA does not have a PARAMETERS table until MySQL 6.0, so applications that need to acquire routine parameter information at runtime must use workarounds such as parsing the output of SHOW CREATE statements or the param_list column of the mysql.proc table. param_list contents can be processed from within a stored routine, unlike the output from SHOW.

There are no stored routine debugging facilities.

Before MySQL 5.1.4, CALL statements cannot be prepared. This true both for server-side prepared statements and for SQL prepared statements.

UNDO handlers are not supported.

FOR loops are not supported.

To prevent problems of interaction between server threads, 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. This means that while the statement executes, it will not see changes to routines performed by other threads.

For triggers, the following additional statements or operations are disallowed:

  • Triggers currently are not activated by foreign key actions.

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

  • Triggers are not allowed on tables in the mysql database.

Stored routines and triggers in MySQL Cluster.  Stored functions, stored procedures, and triggers are all supported by tables using the NDB 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 the following:

  • Stored routine definitions are kept in tables in the mysql system database using the MyISAM storage engine, and so do not participate in clustering.

  • The .TRN and .TRG 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 PROCEDURE, 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 ALTER or DROP statements on each MySQL Server accessing the cluster.

Warning

Do not attempt to work around the issue described in the first item mentioned previously by converting any mysql database tables to use the NDB storage engine. Altering the system tables in the mysql database is very likely to produce undesirable results, and is not supported by MySQL AB.


User Comments

Posted by Roland Bouman on February 19 2006 8:05pm[Delete] [Edit]

While waiting for a information_schema.PARAMETERS system view, there is an alternative to parsing the output of a show command to get info on the parameterlist: you can use the param_list column of the proc table in the mysql database. The advantage is that you can parse that from inside a sp, which you cannot do with the output of a show command.

Of course, you will need to be able to select from the mysql.proc table.

Posted by Morgan Tocker on July 19 2006 10:18pm[Delete] [Edit]

You can't union the result of two procedures together, so this won't work:

DELIMITER ;;

CREATE PROCEDURE a ()
BEGIN
SELECT 2 FROM DUAL;
END;
;;

CREATE PROCEDURE b ()
BEGIN

SELECT 1 FROM DUAL
UNION
CALL a();
END;
;;

DELIMITER ;


The workaround is to create a temporary table. Note that the syntax isn't quite as you
would predict, as the temporary table must be created inside b() to prevent what appears to be a scope problem.

DELIMITER ;;

DROP PROCEDURE a;;
CREATE PROCEDURE a ()
BEGIN
INSERT into temp_a SELECT 2 FROM DUAL;
END;
;;

DROP PROCEDURE b;;

CREATE PROCEDURE b ()
BEGIN

DROP TABLE IF EXISTS temp_a;
CREATE TEMPORARY TABLE `temp_a` (
`2` bigint(1) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CALL a();

SELECT 1 FROM DUAL
UNION
SELECT * FROM temp_a;
END;
;;

Add your own comment.