1. limitations on Stored Programs
The following additional limitations apply for stored programs:
Some of the limitations mentioned here apply to stored procedures and stored functions.
SQL Statements Not allowed in Stored Routines
Stored routines can’t contain arbitrary SQL statements. The following statements are not allowed:
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 into stored functions or triggers. So the stored functions and triggers can’t use dynamic SQL.
Inserts can’t be delayed. INSERT DELAYED is accepted but the statement is taken care of as a normal INSERT.
Limitations for Stored Functions
The following additional statements or operations are not allowed within stored functions. They are allowed within stored procedures, except stored procedures that are invoked from within a stored function or trigger. For example, if you use FLUSH a stored procedure, that stored procedure can’t be called from a stored function or trigger.
Statements that perform explicitly 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.
Stored functions can’t be used recursively.
A stored function or trigger can’t modify a table that is already being used by the statement that invoked the function or trigger.
Limitations for Triggers
The following additional limitations apply for triggers:
Triggers are not activated by a 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.
The RETURN statement is not allowed in triggers, which can’t return a value. To exit a trigger immediately, use the LEAVE statement.
Triggers are not allowed on tables in the mysql database. Nor are they allowed on INFORMATION_SCHEMA or performance_schema tables. Those tables are actually views and triggers are not allowed 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.
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. In these 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.
Debugging Considerations
There are no stored routine debugging facilities.
Event Scheduler limitations
The following limitations are specific to the Event Scheduler:
Event names are taken care of in case-insensitive fashion. For example, you can’t 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.
2. Limitations on Views
The maximum number of tables that can be referenced in the definition of a view is 61.
View processing is not optimized:
Not possible to create an index on a view.
Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables.
3. Limitations on Condition Handling
SIGNAL, RESIGNAL, and GET DIAGNOSTICS are not allow as prepared statements.
4. Limitations on Cursors
The following limitations are specific to the Event Scheduler:
Cursors are read-only; you cannot use a cursor to update rows.
UPDATE WHERE CURRENT OF and DELETE WHERE CURRENT OF are not implemented, because updatable cursors are not supported.
Cursors are non-holdable.
Cursors are sensitive.
Cursors are non-scrollable.
Cursors are not named. The statement handler acts as the cursor ID.
