MySQL Bugs: #8407: Stored functions/triggers ignore exception handler

Bug #8407	Stored functions/triggers ignore exception handler
Submitted:	9 Feb 2005 22:11	Modified:	9 Mar 2007 2:11
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug	OS:	Linux (SUSE 9.2)
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	stored function, trigger

Description:
If a function contains a statement which fails at runtime,
due for instance to a reference to a nonexistent table, then
the exception should be caught by a continue handler. It isn't.
(This depends on a new 'feature' to allow table references in functions.)

How to repeat:
mysql> create function f20 () returns int begin declare continue handler for sqlexception begin end; drop view v20; return 5; end;//
Query OK, 0 rows affected (0.00 sec)

mysql> select f20()//
ERROR 1146 (42S02): Table 'db18.v20' doesn't exist

Thank you for the bug report.

This might not be possible to solve. It's a side effect of the new
table locking scheme that makes table accesses from stored function
work.

This is a logical consequence of the locking scheme (tables must exist before
the call), so it cannot be fixed.

It can be fixed as following:

  in the prelocking step do not abort with an error if some table does not exist.
  (this can be done conditionally, if there's exception handler, or always)

  during the execution if we got "table is not locked" error AND there were
  no explicit LOCK TABLES then change the error to "table does not exist".

  Then let the exception handler catch it as usual.

The same problem exists for triggers that declare handlers directly. Look at the Bug #14739 that is marked as a duplicate of this one.

The original use case can not be reproduced anymore with
a DROP VIEW statement :
1422: Explicit or implicit commit is not allowed in stored function or trigger.

The bug can be reproduced with a select ... from no_such_table.

See for example Bug#20713, which is a duplicate.

See related bugs: Bug#18914, Bug#20713, Bug#21825, Bug#22580.

The underlying issue is the pre-locking code and the computation of the
'transitive closure' of the table list for stored procedures.

Bug#25345 "Cursors from Functions" was marked as a duplicate of this bug.
Please add the test case from this bug to the test suite along with the patch for this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19058

ChangeSet@1.2390, 2007-01-30 20:22:12-07:00, malff@weblab.(none) +15 -0
  Bug#8407 (Stored functions/triggers ignore exception handler)
  Bug 18914 (Calling certain SPs from triggers fail)
  Bug 20713 (Functions will not not continue for SQLSTATE VALUE '42S02')
  Bug 21825 (Incorrect message error deleting records in a table with a
    trigger for inserting)
  Bug 22580 (DROP TABLE in nested stored procedure causes strange dependency
    error)
  Bug 25345 (Cursors from Functions)
  
  
  This fix resolves a long standing issue originally reported with bug 8407,
  which affect the behavior of Stored Procedures, Stored Functions and Trigger
  in many different ways, causing symptoms reported by all the bugs listed.
  In all cases, the root cause of the problem traces back to 8407 and how the
  server locks tables involved with sub statements.
  
  Prior to this fix, the implementation of stored routines would:
  - compute the transitive closure of all the tables referenced by a top level
  statement
  - open and lock all the tables involved
  - execute the top level statement
  "transitive closure of tables" means collecting:
  - all the tables,
  - all the stored functions,
  - all the views,
  - all the table triggers
  - all the stored procedures
  involved, and recursively inspect these objects definition to find more
  references to more objects, until the list of every object referenced does
  not grow any more.
  This mechanism is known as "pre-locking" tables before execution.
  The motivation for locking all the tables (possibly) used at once is to
  prevent dead locks.
  
  One problem with this approach is that, if the execution path the code
  really takes during runtime does not use a given table, and if the table is
  missing, the server would not execute the statement.
  This in particular has a major impact on triggers, since a missing table
  referenced by an update/delete trigger would prevent an insert trigger to run.
  
  Another problem is that stored routines might define SQL exception handlers
  to deal with missing tables, but the server implementation would never give
  user code a chance to execute this logic, since the routine is never
  executed when a missing table cause the pre-locking code to fail.
  
  With this fix, the internal implementation of the pre-locking code has been
  relaxed of some constraints, so that failure to open a table does not
  necessarily prevent execution of a stored routine.
  
  In particular, the pre-locking mechanism is now behaving as follows:
  
  1) the first step, to compute the transitive closure of all the tables
  possibly referenced by a statement, is unchanged.
  
  2) the next step, which is to open all the tables involved, only attempts
  to open the tables added by the pre-locking code, but silently fails without
  reporting any error or invoking any exception handler is the table is not
  present. This is achieved by trapping internal errors with
  Prelock_error_handler
  
  3) the locking step only locks tables that were successfully opened.
  
  4) when executing sub statements, the list of tables used by each statements
  is evaluated as before. The tables needed by the sub statement are expected
  to be already opened and locked. Statement referencing tables that were not
  opened in step 2) will fail to find the table in the open list, and only at
  this point will execution of the user code fail.
  
  5) when a runtime exception is raised at 4), the instruction continuation
  destination (the next instruction to execute in case of SQL continue
  handlers) is evaluated.
  This is achieved with sp_instr::exec_open_and_lock_tables()
  
  6) if a user exception handler is present in the stored routine, that
  handler is invoked as usual, so that ER_NO_SUCH_TABLE exceptions can be
  trapped by stored routines. If no handler exists, then the runtime execution
  will fail as expected.
  
  With all these changes, a side effect is that view security is impacted, in
  two different ways.
  
  First, a view defined as "select stored_function()", where the stored
  function references a table that may not exist, is considered valid.
  The rationale is that, because the stored function might trap exceptions
  during execution and still return a valid result, there is no way to decide
  when the view is created if a missing table really cause the view to be invalid.
  
  Secondly, testing for existence of tables is now done later during
  execution. View security, which consist of trapping errors and return a
  generic ER_VIEW_INVALID (to prevent disclosing information) was only
  implemented at very specific phases covering *opening* tables, but not
  covering the runtime execution. Because of this existing limitation,
  errors that were previously trapped and converted into ER_VIEW_INVALID are
  not trapped, causing table names to be reported to the user.
  This change is exposing an existing problem, which is independent and will
  be resolved separately.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19219

ChangeSet@1.2390, 2007-02-01 17:54:46-07:00, malff@weblab.(none) +15 -0
  Bug#8407 (Stored functions/triggers ignore exception handler)
  Bug 18914 (Calling certain SPs from triggers fail)
  Bug 20713 (Functions will not not continue for SQLSTATE VALUE '42S02')
  Bug 21825 (Incorrect message error deleting records in a table with a
    trigger for inserting)
  Bug 22580 (DROP TABLE in nested stored procedure causes strange dependency
    error)
  Bug 25345 (Cursors from Functions)
  
  
  This fix resolves a long standing issue originally reported with bug 8407,
  which affect the behavior of Stored Procedures, Stored Functions and Trigger
  in many different ways, causing symptoms reported by all the bugs listed.
  In all cases, the root cause of the problem traces back to 8407 and how the
  server locks tables involved with sub statements.
  
  Prior to this fix, the implementation of stored routines would:
  - compute the transitive closure of all the tables referenced by a top level
  statement
  - open and lock all the tables involved
  - execute the top level statement
  "transitive closure of tables" means collecting:
  - all the tables,
  - all the stored functions,
  - all the views,
  - all the table triggers
  - all the stored procedures
  involved, and recursively inspect these objects definition to find more
  references to more objects, until the list of every object referenced does
  not grow any more.
  This mechanism is known as "pre-locking" tables before execution.
  The motivation for locking all the tables (possibly) used at once is to
  prevent dead locks.
  
  One problem with this approach is that, if the execution path the code
  really takes during runtime does not use a given table, and if the table is
  missing, the server would not execute the statement.
  This in particular has a major impact on triggers, since a missing table
  referenced by an update/delete trigger would prevent an insert trigger to run.
  
  Another problem is that stored routines might define SQL exception handlers
  to deal with missing tables, but the server implementation would never give
  user code a chance to execute this logic, since the routine is never
  executed when a missing table cause the pre-locking code to fail.
  
  With this fix, the internal implementation of the pre-locking code has been
  relaxed of some constraints, so that failure to open a table does not
  necessarily prevent execution of a stored routine.
  
  In particular, the pre-locking mechanism is now behaving as follows:
  
  1) the first step, to compute the transitive closure of all the tables
  possibly referenced by a statement, is unchanged.
  
  2) the next step, which is to open all the tables involved, only attempts
  to open the tables added by the pre-locking code, but silently fails without
  reporting any error or invoking any exception handler is the table is not
  present. This is achieved by trapping internal errors with
  Prelock_error_handler
  
  3) the locking step only locks tables that were successfully opened.
  
  4) when executing sub statements, the list of tables used by each statements
  is evaluated as before. The tables needed by the sub statement are expected
  to be already opened and locked. Statement referencing tables that were not
  opened in step 2) will fail to find the table in the open list, and only at
  this point will execution of the user code fail.
  
  5) when a runtime exception is raised at 4), the instruction continuation
  destination (the next instruction to execute in case of SQL continue
  handlers) is evaluated.
  This is achieved with sp_instr::exec_open_and_lock_tables()
  
  6) if a user exception handler is present in the stored routine, that
  handler is invoked as usual, so that ER_NO_SUCH_TABLE exceptions can be
  trapped by stored routines. If no handler exists, then the runtime execution
  will fail as expected.
  
  With all these changes, a side effect is that view security is impacted, in
  two different ways.
  
  First, a view defined as "select stored_function()", where the stored
  function references a table that may not exist, is considered valid.
  The rationale is that, because the stored function might trap exceptions
  during execution and still return a valid result, there is no way to decide
  when the view is created if a missing table really cause the view to be invalid.
  
  Secondly, testing for existence of tables is now done later during
  execution. View security, which consist of trapping errors and return a
  generic ER_VIEW_INVALID (to prevent disclosing information) was only
  implemented at very specific phases covering *opening* tables, but not
  covering the runtime execution. Because of this existing limitation,
  errors that were previously trapped and converted into ER_VIEW_INVALID are
  not trapped, causing table names to be reported to the user.
  This change is exposing an existing problem, which is independent and will
  be resolved separately.

Bug #26336 "IF statement with nested SQL in stored functions" was marked a duplicate of this bug.

Approved by email with a few comments.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21077

ChangeSet@1.2390, 2007-03-02 19:08:42-07:00, malff@weblab.(none) +18 -0
  Bug#8407 (Stored functions/triggers ignore exception handler)
  Bug 18914 (Calling certain SPs from triggers fail)
  Bug 20713 (Functions will not not continue for SQLSTATE VALUE '42S02')
  Bug 21825 (Incorrect message error deleting records in a table with a
    trigger for inserting)
  Bug 22580 (DROP TABLE in nested stored procedure causes strange dependency
    error)
  Bug 25345 (Cursors from Functions)
  
  
  This fix resolves a long standing issue originally reported with bug 8407,
  which affect the behavior of Stored Procedures, Stored Functions and Trigger
  in many different ways, causing symptoms reported by all the bugs listed.
  In all cases, the root cause of the problem traces back to 8407 and how the
  server locks tables involved with sub statements.
  
  Prior to this fix, the implementation of stored routines would:
  - compute the transitive closure of all the tables referenced by a top level
  statement
  - open and lock all the tables involved
  - execute the top level statement
  "transitive closure of tables" means collecting:
  - all the tables,
  - all the stored functions,
  - all the views,
  - all the table triggers
  - all the stored procedures
  involved, and recursively inspect these objects definition to find more
  references to more objects, until the list of every object referenced does
  not grow any more.
  This mechanism is known as "pre-locking" tables before execution.
  The motivation for locking all the tables (possibly) used at once is to
  prevent dead locks.
  
  One problem with this approach is that, if the execution path the code
  really takes during runtime does not use a given table, and if the table is
  missing, the server would not execute the statement.
  This in particular has a major impact on triggers, since a missing table
  referenced by an update/delete trigger would prevent an insert trigger to run.
  
  Another problem is that stored routines might define SQL exception handlers
  to deal with missing tables, but the server implementation would never give
  user code a chance to execute this logic, since the routine is never
  executed when a missing table cause the pre-locking code to fail.
  
  With this fix, the internal implementation of the pre-locking code has been
  relaxed of some constraints, so that failure to open a table does not
  necessarily prevent execution of a stored routine.
  
  In particular, the pre-locking mechanism is now behaving as follows:
  
  1) the first step, to compute the transitive closure of all the tables
  possibly referenced by a statement, is unchanged.
  
  2) the next step, which is to open all the tables involved, only attempts
  to open the tables added by the pre-locking code, but silently fails without
  reporting any error or invoking any exception handler is the table is not
  present. This is achieved by trapping internal errors with
  Prelock_error_handler
  
  3) the locking step only locks tables that were successfully opened.
  
  4) when executing sub statements, the list of tables used by each statements
  is evaluated as before. The tables needed by the sub statement are expected
  to be already opened and locked. Statement referencing tables that were not
  opened in step 2) will fail to find the table in the open list, and only at
  this point will execution of the user code fail.
  
  5) when a runtime exception is raised at 4), the instruction continuation
  destination (the next instruction to execute in case of SQL continue
  handlers) is evaluated.
  This is achieved with sp_instr::exec_open_and_lock_tables()
  
  6) if a user exception handler is present in the stored routine, that
  handler is invoked as usual, so that ER_NO_SUCH_TABLE exceptions can be
  trapped by stored routines. If no handler exists, then the runtime execution
  will fail as expected.
  
  With all these changes, a side effect is that view security is impacted, in
  two different ways.
  
  First, a view defined as "select stored_function()", where the stored
  function references a table that may not exist, is considered valid.
  The rationale is that, because the stored function might trap exceptions
  during execution and still return a valid result, there is no way to decide
  when the view is created if a missing table really cause the view to be invalid.
  
  Secondly, testing for existence of tables is now done later during
  execution. View security, which consist of trapping errors and return a
  generic ER_VIEW_INVALID (to prevent disclosing information) was only
  implemented at very specific phases covering *opening* tables, but not
  covering the runtime execution. Because of this existing limitation,
  errors that were previously trapped and converted into ER_VIEW_INVALID are
  not trapped, causing table names to be reported to the user.
  This change is exposing an existing problem, which is independent and will
  be resolved separately.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/21203

ChangeSet@1.2390, 2007-03-05 19:42:07-07:00, malff@weblab.(none) +18 -0
  Bug#8407 (Stored functions/triggers ignore exception handler)
  Bug 18914 (Calling certain SPs from triggers fail)
  Bug 20713 (Functions will not not continue for SQLSTATE VALUE '42S02')
  Bug 21825 (Incorrect message error deleting records in a table with a
    trigger for inserting)
  Bug 22580 (DROP TABLE in nested stored procedure causes strange dependency
    error)
  Bug 25345 (Cursors from Functions)
  
  
  This fix resolves a long standing issue originally reported with bug 8407,
  which affect the behavior of Stored Procedures, Stored Functions and Trigger
  in many different ways, causing symptoms reported by all the bugs listed.
  In all cases, the root cause of the problem traces back to 8407 and how the
  server locks tables involved with sub statements.
  
  Prior to this fix, the implementation of stored routines would:
  - compute the transitive closure of all the tables referenced by a top level
  statement
  - open and lock all the tables involved
  - execute the top level statement
  "transitive closure of tables" means collecting:
  - all the tables,
  - all the stored functions,
  - all the views,
  - all the table triggers
  - all the stored procedures
  involved, and recursively inspect these objects definition to find more
  references to more objects, until the list of every object referenced does
  not grow any more.
  This mechanism is known as "pre-locking" tables before execution.
  The motivation for locking all the tables (possibly) used at once is to
  prevent dead locks.
  
  One problem with this approach is that, if the execution path the code
  really takes during runtime does not use a given table, and if the table is
  missing, the server would not execute the statement.
  This in particular has a major impact on triggers, since a missing table
  referenced by an update/delete trigger would prevent an insert trigger to run.
  
  Another problem is that stored routines might define SQL exception handlers
  to deal with missing tables, but the server implementation would never give
  user code a chance to execute this logic, since the routine is never
  executed when a missing table cause the pre-locking code to fail.
  
  With this fix, the internal implementation of the pre-locking code has been
  relaxed of some constraints, so that failure to open a table does not
  necessarily prevent execution of a stored routine.
  
  In particular, the pre-locking mechanism is now behaving as follows:
  
  1) the first step, to compute the transitive closure of all the tables
  possibly referenced by a statement, is unchanged.
  
  2) the next step, which is to open all the tables involved, only attempts
  to open the tables added by the pre-locking code, but silently fails without
  reporting any error or invoking any exception handler is the table is not
  present. This is achieved by trapping internal errors with
  Prelock_error_handler
  
  3) the locking step only locks tables that were successfully opened.
  
  4) when executing sub statements, the list of tables used by each statements
  is evaluated as before. The tables needed by the sub statement are expected
  to be already opened and locked. Statement referencing tables that were not
  opened in step 2) will fail to find the table in the open list, and only at
  this point will execution of the user code fail.
  
  5) when a runtime exception is raised at 4), the instruction continuation
  destination (the next instruction to execute in case of SQL continue
  handlers) is evaluated.
  This is achieved with sp_instr::exec_open_and_lock_tables()
  
  6) if a user exception handler is present in the stored routine, that
  handler is invoked as usual, so that ER_NO_SUCH_TABLE exceptions can be
  trapped by stored routines. If no handler exists, then the runtime execution
  will fail as expected.
  
  With all these changes, a side effect is that view security is impacted, in
  two different ways.
  
  First, a view defined as "select stored_function()", where the stored
  function references a table that may not exist, is considered valid.
  The rationale is that, because the stored function might trap exceptions
  during execution and still return a valid result, there is no way to decide
  when the view is created if a missing table really cause the view to be invalid.
  
  Secondly, testing for existence of tables is now done later during
  execution. View security, which consist of trapping errors and return a
  generic ER_VIEW_INVALID (to prevent disclosing information) was only
  implemented at very specific phases covering *opening* tables, but not
  covering the runtime execution. Because of this existing limitation,
  errors that were previously trapped and converted into ER_VIEW_INVALID are
  not trapped, causing table names to be reported to the user.
  This change is exposing an existing problem, which is independent and will
  be resolved separately.

Fixed in 5.0.38 and 5.1.17

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.38 and 5.1.17 changelogs.