List:Commits« Previous MessageNext Message »
From:marc.alff Date:March 6 2007 2:42am
Subject:bk commit into 5.0 tree (malff:1.2390) BUG#8407
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of marcsql. When marcsql does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 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.

  mysql-test/r/information_schema_db.result@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +3 -3
    Revised the pre-locking code implementation, aligned the tests.

  mysql-test/r/sp-error.result@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +137 -6
    Revised the pre-locking code implementation, aligned the tests.

  mysql-test/r/sp.result@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +40 -2
    Revised the pre-locking code implementation, aligned the tests.

  mysql-test/r/trigger.result@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +37 -0
    Revised the pre-locking code implementation, aligned the tests.

  mysql-test/r/view.result@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +3 -3
    Revised the pre-locking code implementation, aligned the tests.

  mysql-test/t/sp-error.test@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +187 -6
    Revised the pre-locking code implementation, aligned the tests.

  mysql-test/t/sp.test@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +49 -4
    Revised the pre-locking code implementation, aligned the tests.

  mysql-test/t/trigger.test@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +74 -0
    Revised the pre-locking code implementation, aligned the tests.

  sql/lock.cc@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +1 -1
    table->placeholder now checks for schema_table

  sql/mysqld.cc@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +8 -0
    my_message_sql(): invoke internal exception handlers

  sql/sp_head.cc@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +40 -9
    exec_open_and_lock_tables(): open and lock tables, or return the
    continuation destination of this instruction

  sql/sp_head.h@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +24 -0
    exec_open_and_lock_tables(): open and lock tables, or return the
    continuation destination of this instruction

  sql/sql_base.cc@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +94 -8
    Prelock_error_handler: delay open table errors until execution

  sql/sql_class.cc@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +32 -0
    THD: add internal error handler, as an exception mechanism.

  sql/sql_class.h@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +67 -0
    THD: add internal error handler, as an exception mechanism.

  sql/sql_update.cc@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +1 -1
    table->placeholder now checks for schema_table

  sql/table.cc@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +3 -1
    st_table_list::hide_view_error(): masked more errors for view security

  sql/table.h@stripped, 2007-03-05 19:42:04-07:00, malff@weblab.(none) +1 -1
    table->placeholder now checks for schema_table, and unopened tables

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	malff
# Host:	weblab.(none)
# Root:	/home/marcsql/TREE/mysql-5.0-8407_b

--- 1.93/sql/lock.cc	2007-03-05 19:42:14 -07:00
+++ 1.94/sql/lock.cc	2007-03-05 19:42:14 -07:00
@@ -566,7 +566,7 @@ TABLE_LIST *mysql_lock_have_duplicate(TH
 
   for (; haystack; haystack= haystack->next_global)
   {
-    if (haystack->placeholder() || haystack->schema_table)
+    if (haystack->placeholder())
       continue;
     table2= haystack->table;
     if (table2->s->tmp_table == TMP_TABLE)

--- 1.590/sql/mysqld.cc	2007-03-05 19:42:14 -07:00
+++ 1.591/sql/mysqld.cc	2007-03-05 19:42:14 -07:00
@@ -2411,6 +2411,14 @@ static int my_message_sql(uint error, co
   */
   if ((thd= current_thd))
   {
+    /*
+      TODO: There are two exceptions mechanism (THD and sp_rcontext),
+      this could be improved by having a common stack of handlers.
+    */
+    if (thd->handle_error(error,
+                          MYSQL_ERROR::WARN_LEVEL_ERROR))
+      DBUG_RETURN(0);
+
     if (thd->spcont &&
         thd->spcont->handle_error(error, MYSQL_ERROR::WARN_LEVEL_ERROR, thd))
     {

--- 1.363/sql/sql_base.cc	2007-03-05 19:42:14 -07:00
+++ 1.364/sql/sql_base.cc	2007-03-05 19:42:14 -07:00
@@ -28,6 +28,59 @@
 #include <io.h>
 #endif
 
+/**
+  This internal handler is used to trap internally
+  errors that can occur when executing open table
+  during the prelocking phase.
+*/
+class Prelock_error_handler : public Internal_error_handler
+{
+public:
+  Prelock_error_handler()
+    : m_handled_errors(0), m_unhandled_errors(0)
+  {}
+
+  virtual ~Prelock_error_handler() {}
+
+  virtual bool handle_error(uint sql_errno,
+                            MYSQL_ERROR::enum_warning_level level,
+                            THD *thd);
+
+  bool safely_trapped_errors();
+
+private:
+  int m_handled_errors;
+  int m_unhandled_errors;
+};
+
+
+bool
+Prelock_error_handler::handle_error(uint sql_errno,
+                                    MYSQL_ERROR::enum_warning_level /* level */,
+                                    THD * /* thd */)
+{
+  if (sql_errno == ER_NO_SUCH_TABLE)
+  {
+    m_handled_errors++;
+    return TRUE;                                // 'TRUE', as per coding style
+  }
+
+  m_unhandled_errors++;
+  return FALSE;                                 // 'FALSE', as per coding style
+}
+
+
+bool Prelock_error_handler::safely_trapped_errors()
+{
+  /*
+    If m_unhandled_errors != 0, something else, unanticipated, happened,
+    so the error is not trapped but returned to the caller.
+    Multiple ER_NO_SUCH_TABLE can be raised in case of views.
+  */
+  return ((m_handled_errors > 0) && (m_unhandled_errors == 0));
+}
+
+
 TABLE *unused_tables;				/* Used by mysql_test */
 HASH open_cache;				/* Used by mysql_test */
 
@@ -1334,7 +1387,10 @@ TABLE *open_table(THD *thd, TABLE_LIST *
         VOID(pthread_mutex_unlock(&LOCK_open));
       }
     }
-    my_error(ER_TABLE_NOT_LOCKED, MYF(0), alias);
+    if ((thd->locked_tables) && (thd->locked_tables->lock_count > 0))
+      my_error(ER_TABLE_NOT_LOCKED, MYF(0), alias);
+    else
+      my_error(ER_NO_SUCH_TABLE, MYF(0), table_list->db, table_list->alias);
     DBUG_RETURN(0);
   }
 
@@ -2092,6 +2148,8 @@ int open_tables(THD *thd, TABLE_LIST **s
   MEM_ROOT new_frm_mem;
   /* Also used for indicating that prelocking is need */
   TABLE_LIST **query_tables_last_own;
+  bool safe_to_ignore_table;
+
   DBUG_ENTER("open_tables");
   /*
     temporary mem_root for new .frm parsing.
@@ -2147,6 +2205,7 @@ int open_tables(THD *thd, TABLE_LIST **s
 
   for (tables= *start; tables ;tables= tables->next_global)
   {
+    safe_to_ignore_table= FALSE;                // 'FALSE', as per coding style
     /*
       Ignore placeholders for derived tables. After derived tables
       processing, link to created temporary table will be put here.
@@ -2166,9 +2225,28 @@ int open_tables(THD *thd, TABLE_LIST **s
       DBUG_RETURN(-1);
     }
     (*counter)++;
-    
-    if (!tables->table &&
-	!(tables->table= open_table(thd, tables, &new_frm_mem, &refresh, flags)))
+
+    if (!tables->table)
+    {
+      if (tables->prelocking_placeholder)
+      {
+        /*
+          For the tables added by the pre-locking code, attempt to open
+          the table but fail silently if the table does not exist.
+          The real failure will occur when/if a statement attempts to use
+          that table.
+        */
+        Prelock_error_handler prelock_handler;
+        thd->push_internal_handler(& prelock_handler);
+        tables->table= open_table(thd, tables, &new_frm_mem, &refresh, flags);
+        thd->pop_internal_handler();
+        safe_to_ignore_table= prelock_handler.safely_trapped_errors();
+      }
+      else
+        tables->table= open_table(thd, tables, &new_frm_mem, &refresh, flags);
+    }
+
+    if (!tables->table)
     {
       free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
 
@@ -2219,6 +2297,14 @@ int open_tables(THD *thd, TABLE_LIST **s
         close_tables_for_reopen(thd, start);
 	goto restart;
       }
+
+      if (safe_to_ignore_table)
+      {
+        DBUG_PRINT("info", ("open_table: ignoring table '%s'.'%s'",
+                            tables->db, tables->alias));
+        continue;
+      }
+
       result= -1;				// Fatal error
       break;
     }
@@ -2522,7 +2608,7 @@ bool open_normal_and_derived_tables(THD 
 static void mark_real_tables_as_free_for_reuse(TABLE_LIST *table)
 {
   for (; table; table= table->next_global)
-    if (!table->placeholder() && !table->schema_table)
+    if (!table->placeholder())
       table->table->query_id= 0;
 }
 
@@ -2594,7 +2680,7 @@ int lock_tables(THD *thd, TABLE_LIST *ta
       DBUG_RETURN(-1);
     for (table= tables; table; table= table->next_global)
     {
-      if (!table->placeholder() && !table->schema_table)
+      if (!table->placeholder())
 	*(ptr++)= table->table;
     }
 
@@ -2636,7 +2722,7 @@ int lock_tables(THD *thd, TABLE_LIST *ta
 
       for (table= tables; table != first_not_own; table= table->next_global)
       {
-        if (!table->placeholder() && !table->schema_table)
+        if (!table->placeholder())
         {
           table->table->query_id= thd->query_id;
           if (check_lock_and_start_stmt(thd, table->table, table->lock_type))
@@ -2663,7 +2749,7 @@ int lock_tables(THD *thd, TABLE_LIST *ta
     TABLE_LIST *first_not_own= thd->lex->first_not_own_table();
     for (table= tables; table != first_not_own; table= table->next_global)
     {
-      if (!table->placeholder() && !table->schema_table &&
+      if (!table->placeholder() &&
 	  check_lock_and_start_stmt(thd, table->table, table->lock_type))
       {
 	ha_rollback_stmt(thd);

--- 1.257/sql/sql_class.cc	2007-03-05 19:42:14 -07:00
+++ 1.258/sql/sql_class.cc	2007-03-05 19:42:14 -07:00
@@ -273,6 +273,38 @@ THD::THD()
   substitute_null_with_insert_id = FALSE;
   thr_lock_info_init(&lock_info); /* safety: will be reset after start */
   thr_lock_owner_init(&main_lock_id, &lock_info);
+
+  m_internal_handler= NULL;
+}
+
+
+void THD::push_internal_handler(Internal_error_handler *handler)
+{
+  /*
+    TODO: The current implementation is limited to 1 handler at a time only.
+    THD and sp_rcontext need to be modified to use a common handler stack.
+  */
+  DBUG_ASSERT(m_internal_handler == NULL);
+  m_internal_handler= handler;
+}
+
+
+bool THD::handle_error(uint sql_errno,
+                       MYSQL_ERROR::enum_warning_level level)
+{
+  if (m_internal_handler)
+  {
+    return m_internal_handler->handle_error(sql_errno, level, this);
+  }
+
+  return FALSE;                                 // 'FALSE', as per coding style
+}
+
+
+void THD::pop_internal_handler()
+{
+  DBUG_ASSERT(m_internal_handler != NULL);
+  m_internal_handler= NULL;
 }
 
 

--- 1.317/sql/sql_class.h	2007-03-05 19:42:14 -07:00
+++ 1.318/sql/sql_class.h	2007-03-05 19:42:14 -07:00
@@ -1071,6 +1071,48 @@ public:
   SAVEPOINT *savepoints;
 };
 
+/**
+  This class represents the interface for internal error handlers.
+  Internal error handlers are exception handlers used by the server
+  implementation.
+*/
+class Internal_error_handler
+{
+protected:
+  Internal_error_handler() {}
+  virtual ~Internal_error_handler() {}
+
+public:
+  /**
+    Handle an error condition.
+    This method can be implemented by a subclass to achieve any of the
+    following:
+    - mask an error internally, prevent exposing it to the user,
+    - mask an error and throw another one instead.
+    When this method returns true, the error condition is considered
+    'handled', and will not be propagated to upper layers.
+    It is the responsability of the code installing an internal handler
+    to then check for trapped conditions, and implement logic to recover
+    from the anticipated conditions trapped during runtime.
+
+    This mechanism is similar to C++ try/throw/catch:
+    - 'try' correspond to <code>THD::push_internal_handler()</code>,
+    - 'throw' correspond to <code>my_error()</code>,
+    which invokes <code>my_message_sql()</code>,
+    - 'catch' correspond to checking how/if an internal handler was invoked,
+    before removing it from the exception stack with
+    <code>THD::pop_internal_handler()</code>.
+
+    @param sql_errno the error number
+    @param level the error level
+    @param thd the calling thread
+    @return true if the error is handled
+  */
+  virtual bool handle_error(uint sql_errno,
+                            MYSQL_ERROR::enum_warning_level level,
+                            THD *thd) = 0;
+};
+
 
 /*
   For each client connection we create a separate thread with THD serving as
@@ -1659,6 +1701,31 @@ public:
       *p_db_length= db_length;
     return FALSE;
   }
+
+public:
+  /**
+    Add an internal error handler to the thread execution context.
+    @param handler the exception handler to add
+  */
+  void push_internal_handler(Internal_error_handler *handler);
+
+  /**
+    Handle an error condition.
+    @param sql_errno the error number
+    @param level the error level
+    @return true if the error is handled
+  */
+  virtual bool handle_error(uint sql_errno,
+                            MYSQL_ERROR::enum_warning_level level);
+
+  /**
+    Remove the error handler last pushed.
+  */
+  void pop_internal_handler();
+
+private:
+  /** The current internal error handler for this thread, or NULL. */
+  Internal_error_handler *m_internal_handler;
 };
 
 

--- 1.209/sql/sql_update.cc	2007-03-05 19:42:14 -07:00
+++ 1.210/sql/sql_update.cc	2007-03-05 19:42:14 -07:00
@@ -783,7 +783,7 @@ reopen_tables:
       tl->lock_type= using_update_log ? TL_READ_NO_INSERT : TL_READ;
       tl->updating= 0;
       /* Update TABLE::lock_type accordingly. */
-      if (!tl->placeholder() && !tl->schema_table && !using_lock_tables)
+      if (!tl->placeholder() && !using_lock_tables)
         tl->table->reginfo.lock_type= tl->lock_type;
     }
   }

--- 1.241/sql/table.cc	2007-03-05 19:42:14 -07:00
+++ 1.242/sql/table.cc	2007-03-05 19:42:14 -07:00
@@ -2091,7 +2091,9 @@ void st_table_list::hide_view_error(THD 
       thd->net.last_errno == ER_SP_DOES_NOT_EXIST ||
       thd->net.last_errno == ER_PROCACCESS_DENIED_ERROR ||
       thd->net.last_errno == ER_COLUMNACCESS_DENIED_ERROR ||
-      thd->net.last_errno == ER_TABLEACCESS_DENIED_ERROR)
+      thd->net.last_errno == ER_TABLEACCESS_DENIED_ERROR ||
+      thd->net.last_errno == ER_TABLE_NOT_LOCKED ||
+      thd->net.last_errno == ER_NO_SUCH_TABLE)
   {
     TABLE_LIST *top= top_table();
     thd->clear_error();

--- 1.137/sql/table.h	2007-03-05 19:42:14 -07:00
+++ 1.138/sql/table.h	2007-03-05 19:42:14 -07:00
@@ -643,7 +643,7 @@ typedef struct st_table_list
   int view_check_option(THD *thd, bool ignore_failure);
   bool setup_underlying(THD *thd);
   void cleanup_items();
-  bool placeholder() {return derived || view; }
+  bool placeholder() {return derived || view || schema_table || !table; }
   void print(THD *thd, String *str);
   bool check_single_table(st_table_list **table, table_map map,
                           st_table_list *view);

--- 1.186/mysql-test/r/view.result	2007-03-05 19:42:14 -07:00
+++ 1.187/mysql-test/r/view.result	2007-03-05 19:42:14 -07:00
@@ -1933,11 +1933,11 @@ create function f1 () returns int return
 DROP TABLE t1;
 CHECK TABLE v1, v2, v3, v4, v5, v6;
 Table	Op	Msg_type	Msg_text
-test.v1	check	error	View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+test.v1	check	status	OK
 test.v2	check	status	OK
-test.v3	check	error	View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+test.v3	check	status	OK
 test.v4	check	status	OK
-test.v5	check	error	View 'test.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+test.v5	check	status	OK
 test.v6	check	status	OK
 drop function f1;
 drop function f2;

--- 1.50/mysql-test/r/trigger.result	2007-03-05 19:42:14 -07:00
+++ 1.51/mysql-test/r/trigger.result	2007-03-05 19:42:14 -07:00
@@ -1278,4 +1278,41 @@ a	b
 2	b
 3	c
 drop table t1;
+DROP TABLE IF EXISTS bug21825_A;
+DROP TABLE IF EXISTS bug21825_B;
+CREATE TABLE bug21825_A (id int(10));
+CREATE TABLE bug21825_B (id int(10));
+CREATE TRIGGER trgA AFTER INSERT ON bug21825_A
+FOR EACH ROW
+BEGIN
+INSERT INTO bug21825_B (id) values (1);
+END//
+INSERT INTO bug21825_A (id) VALUES (10);
+INSERT INTO bug21825_A (id) VALUES (20);
+DROP TABLE bug21825_B;
+DELETE FROM bug21825_A WHERE id = 20;
+DROP TABLE bug21825_A;
+DROP TABLE IF EXISTS bug22580_t1;
+DROP PROCEDURE IF EXISTS bug22580_proc_1;
+DROP PROCEDURE IF EXISTS bug22580_proc_2;
+CREATE TABLE bug22580_t1 (a INT, b INT);
+CREATE PROCEDURE bug22580_proc_2()
+BEGIN
+DROP TABLE IF EXISTS bug22580_tmp;
+CREATE TEMPORARY TABLE bug22580_tmp (a INT);
+DROP TABLE bug22580_tmp;
+END||
+CREATE PROCEDURE bug22580_proc_1()
+BEGIN
+CALL bug22580_proc_2();
+END||
+CREATE TRIGGER t1bu BEFORE UPDATE ON bug22580_t1
+FOR EACH ROW 
+BEGIN
+CALL bug22580_proc_1();
+END||
+INSERT INTO bug22580_t1 VALUES (1,1);
+DROP TABLE bug22580_t1;
+DROP PROCEDURE bug22580_proc_1;
+DROP PROCEDURE bug22580_proc_2;
 End of 5.0 tests

--- 1.58/mysql-test/t/trigger.test	2007-03-05 19:42:14 -07:00
+++ 1.59/mysql-test/t/trigger.test	2007-03-05 19:42:14 -07:00
@@ -1554,4 +1554,78 @@ select * from t1;
 
 drop table t1;
 
+#
+# Bug#21285 (Incorrect message error deleting records in a table with a
+#           trigger for inserting)
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS bug21825_A;
+DROP TABLE IF EXISTS bug21825_B;
+--enable_warnings
+
+CREATE TABLE bug21825_A (id int(10));
+CREATE TABLE bug21825_B (id int(10));
+
+delimiter //;
+
+CREATE TRIGGER trgA AFTER INSERT ON bug21825_A
+FOR EACH ROW
+BEGIN
+  INSERT INTO bug21825_B (id) values (1);
+END//
+delimiter ;//
+
+INSERT INTO bug21825_A (id) VALUES (10);
+INSERT INTO bug21825_A (id) VALUES (20);
+
+DROP TABLE bug21825_B;
+
+# Must pass, the missing table in the insert trigger should not matter.
+DELETE FROM bug21825_A WHERE id = 20;
+
+DROP TABLE bug21825_A;
+
+#
+# Bug#22580 (DROP TABLE in nested stored procedure causes strange dependancy
+# error)
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS bug22580_t1;
+DROP PROCEDURE IF EXISTS bug22580_proc_1;
+DROP PROCEDURE IF EXISTS bug22580_proc_2;
+--enable_warnings
+
+CREATE TABLE bug22580_t1 (a INT, b INT);
+
+DELIMITER ||;
+
+CREATE PROCEDURE bug22580_proc_2()
+BEGIN
+  DROP TABLE IF EXISTS bug22580_tmp;
+  CREATE TEMPORARY TABLE bug22580_tmp (a INT);
+  DROP TABLE bug22580_tmp;
+END||
+
+CREATE PROCEDURE bug22580_proc_1()
+BEGIN
+  CALL bug22580_proc_2();
+END||
+
+CREATE TRIGGER t1bu BEFORE UPDATE ON bug22580_t1
+FOR EACH ROW 
+BEGIN
+  CALL bug22580_proc_1();
+END||
+
+DELIMITER ;||
+
+# Must pass, the actions of the update trigger should not matter
+INSERT INTO bug22580_t1 VALUES (1,1);
+
+DROP TABLE bug22580_t1;
+DROP PROCEDURE bug22580_proc_1;
+DROP PROCEDURE bug22580_proc_2;
+
 --echo End of 5.0 tests

--- 1.9/mysql-test/r/information_schema_db.result	2007-03-05 19:42:14 -07:00
+++ 1.10/mysql-test/r/information_schema_db.result	2007-03-05 19:42:14 -07:00
@@ -87,13 +87,13 @@ where table_schema='test';
 table_name	table_type	table_comment
 t1	BASE TABLE	
 v1	VIEW	VIEW
-v2	VIEW	View 'test.v2' references invalid table(s) or column(s) or function(s) or define
+v2	VIEW	VIEW
 drop table t1;
 select table_name, table_type, table_comment from information_schema.tables
 where table_schema='test';
 table_name	table_type	table_comment
-v1	VIEW	View 'test.v1' references invalid table(s) or column(s) or function(s) or define
-v2	VIEW	View 'test.v2' references invalid table(s) or column(s) or function(s) or define
+v1	VIEW	VIEW
+v2	VIEW	VIEW
 drop function f1;
 drop function f2;
 drop view v1, v2;

--- 1.110/mysql-test/r/sp-error.result	2007-03-05 19:42:14 -07:00
+++ 1.111/mysql-test/r/sp-error.result	2007-03-05 19:42:14 -07:00
@@ -1128,9 +1128,9 @@ drop view if exists v1, v2, v3, v4;
 create function bug11555_1() returns int return (select max(i) from t1);
 create function bug11555_2() returns int return bug11555_1();
 create view v1 as select bug11555_1();
-ERROR 42S02: Table 'test.t1' doesn't exist
+drop view v1;
 create view v2 as select bug11555_2();
-ERROR 42S02: Table 'test.t1' doesn't exist
+drop view v2;
 create table t1 (i int);
 create view v1 as select bug11555_1();
 create view v2 as select bug11555_2();
@@ -1143,8 +1143,7 @@ ERROR HY000: View 'test.v2' references i
 select * from v3;
 ERROR HY000: View 'test.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
 create view v4 as select * from v1;
-ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
-drop view v1, v2, v3;
+drop view v1, v2, v3, v4;
 drop function bug11555_1;
 drop function bug11555_2;
 create table t1 (i int);
@@ -1153,12 +1152,12 @@ create trigger t1_ai after insert on t1 
 create view v1 as select * from t1;
 drop table t2;
 insert into v1 values (1);
-ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
 drop trigger t1_ai;
 create function bug11555_1() returns int return (select max(i) from t2);
 create trigger t1_ai after insert on t1 for each row set @a:=bug11555_1();
 insert into v1 values (2);
-ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
+ERROR HY000: Table 't2' was not locked with LOCK TABLES
 drop function bug11555_1;
 drop table t1;
 drop view v1;
@@ -1269,3 +1268,135 @@ call bug24491();
 ERROR 42S22: Unknown column 'y.value' in 'field list'
 drop procedure bug24491;
 drop tables t1;
+DROP FUNCTION IF EXISTS bug18914_f1;
+DROP FUNCTION IF EXISTS bug18914_f2;
+DROP PROCEDURE IF EXISTS bug18914_p1;
+DROP PROCEDURE IF EXISTS bug18914_p2;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (i INT);
+CREATE PROCEDURE bug18914_p1() CREATE TABLE t2 (i INT);
+CREATE PROCEDURE bug18914_p2() DROP TABLE IF EXISTS no_such_table;
+CREATE FUNCTION bug18914_f1() RETURNS INT
+BEGIN
+CALL bug18914_p1();
+RETURN 1;
+END |
+CREATE FUNCTION bug18914_f2() RETURNS INT
+BEGIN
+CALL bug18914_p2();
+RETURN 1;
+END |
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL bug18914_p1();
+INSERT INTO t1 VALUES (1);
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+SELECT bug18914_f1();
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+SELECT bug18914_f2();
+ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
+SELECT * FROM t2;
+ERROR 42S02: Table 'test.t2' doesn't exist
+DROP FUNCTION bug18914_f1;
+DROP FUNCTION bug18914_f2;
+DROP PROCEDURE bug18914_p1;
+DROP PROCEDURE bug18914_p2;
+DROP TABLE t1;
+drop table if exists bogus_table_20713;
+drop function if exists func_20713_a;
+drop function if exists func_20713_b;
+create table bogus_table_20713( id int(10) not null primary key);
+insert into bogus_table_20713 values (1), (2), (3);
+create function func_20713_a() returns int(11)
+begin
+declare id int;
+declare continue handler for sqlexception set id=null;
+set @in_func := 1;
+set id = (select id from bogus_table_20713 where id = 3);
+set @in_func := 2;
+return id;
+end//
+create function func_20713_b() returns int(11)
+begin
+declare id int;
+declare continue handler for sqlstate value '42S02' set id=null;
+set @in_func := 1;
+set id = (select id from bogus_table_20713 where id = 3);
+set @in_func := 2;
+return id;
+end//
+set @in_func := 0;
+select func_20713_a();
+func_20713_a()
+NULL
+select @in_func;
+@in_func
+2
+set @in_func := 0;
+select func_20713_b();
+func_20713_b()
+NULL
+select @in_func;
+@in_func
+2
+drop table bogus_table_20713;
+set @in_func := 0;
+select func_20713_a();
+func_20713_a()
+NULL
+select @in_func;
+@in_func
+2
+set @in_func := 0;
+select func_20713_b();
+func_20713_b()
+NULL
+select @in_func;
+@in_func
+2
+drop function if exists func_20713_a;
+drop function if exists func_20713_b;
+drop table if exists table_25345_a;
+drop table if exists table_25345_b;
+drop procedure if exists proc_25345;
+drop function if exists func_25345;
+drop function if exists func_25345_b;
+create table table_25345_a (a int);
+create table table_25345_b (b int);
+create procedure proc_25345()
+begin
+declare c1 cursor for select a from table_25345_a;
+declare c2 cursor for select b from table_25345_b;
+select 1 as result;
+end ||
+create function func_25345() returns int(11)
+begin
+call proc_25345();
+return 1;
+end ||
+create function func_25345_b() returns int(11)
+begin
+declare c1 cursor for select a from table_25345_a;
+declare c2 cursor for select b from table_25345_b;
+return 1;
+end ||
+call proc_25345();
+result
+1
+select func_25345();
+ERROR 0A000: Not allowed to return a result set from a function
+select func_25345_b();
+func_25345_b()
+1
+drop table table_25345_a;
+call proc_25345();
+result
+1
+select func_25345();
+ERROR 0A000: Not allowed to return a result set from a function
+select func_25345_b();
+func_25345_b()
+1
+drop table table_25345_b;
+drop procedure proc_25345;
+drop function func_25345;
+drop function func_25345_b;

--- 1.218/mysql-test/r/sp.result	2007-03-05 19:42:14 -07:00
+++ 1.219/mysql-test/r/sp.result	2007-03-05 19:42:14 -07:00
@@ -1155,9 +1155,13 @@ create function f12_2() returns int
 return (select count(*) from t3)|
 drop temporary table t3|
 select f12_1()|
-ERROR 42S02: Table 'test.t3' doesn't exist
+f12_1()
+3
+Warnings:
+Note	1051	Unknown table 't3'
 select f12_1() from t1 limit 1|
-ERROR 42S02: Table 'test.t3' doesn't exist
+f12_1()
+3
 drop function f0|
 drop function f1|
 drop function f2|
@@ -5741,4 +5745,38 @@ END|
 CALL bug24117()|
 DROP PROCEDURE bug24117|
 DROP TABLE t3|
+drop function if exists func_8407_a|
+drop function if exists func_8407_b|
+create function func_8407_a() returns int
+begin
+declare x int;
+declare continue handler for sqlexception
+begin
+end;
+select 1 from no_such_view limit 1 into x;
+return x;
+end|
+create function func_8407_b() returns int
+begin
+declare x int default 0;
+declare continue handler for sqlstate '42S02'
+  begin
+set x:= x+1000;
+end;
+case (select 1 from no_such_view limit 1)
+when 1 then set x:= x+1;
+when 2 then set x:= x+2;
+else set x:= x+100;
+end case;
+set x:=x + 500;
+return x;
+end|
+select func_8407_a()|
+func_8407_a()
+NULL
+select func_8407_b()|
+func_8407_b()
+1500
+drop function func_8407_a|
+drop function func_8407_b|
 drop table t1,t2;

--- 1.111/mysql-test/t/sp-error.test	2007-03-05 19:42:14 -07:00
+++ 1.112/mysql-test/t/sp-error.test	2007-03-05 19:42:14 -07:00
@@ -1607,10 +1607,12 @@ create function bug11555_1() returns int
 create function bug11555_2() returns int return bug11555_1();
 # It is OK to report name of implicitly used table which is missing
 # when we create view.
---error ER_NO_SUCH_TABLE
+# For stored functions however, because of exceptions handlers, there is
+# no easy way to find out if a missing table makes the view invalid.
 create view v1 as select bug11555_1();
---error ER_NO_SUCH_TABLE
+drop view v1;
 create view v2 as select bug11555_2();
+drop view v2;
 # But we should hide name of missing implicitly used table when we use view
 create table t1 (i int);
 create view v1 as select bug11555_1();
@@ -1625,9 +1627,8 @@ select * from v2;
 select * from v3;
 # Note that creation of view which depends on broken view is yet 
 # another form of view usage.
---error ER_VIEW_INVALID
 create view v4 as select * from v1;
-drop view v1, v2, v3;
+drop view v1, v2, v3, v4;
 # We also should hide details about broken triggers which are
 # invoked for view.
 drop function bug11555_1;
@@ -1637,12 +1638,14 @@ create table t2 (i int);
 create trigger t1_ai after insert on t1 for each row insert into t2 values (new.i);
 create view v1 as select * from t1;
 drop table t2;
---error ER_VIEW_INVALID
+# Limitation, the desired error is ER_VIEW_INVALID
+--error ER_TABLE_NOT_LOCKED
 insert into v1 values (1);
 drop trigger t1_ai;
 create function bug11555_1() returns int return (select max(i) from t2);
 create trigger t1_ai after insert on t1 for each row set @a:=bug11555_1();
---error ER_VIEW_INVALID
+# Limitation, the desired error is ER_VIEW_INVALID
+--error ER_TABLE_NOT_LOCKED
 insert into v1 values (2);
 drop function bug11555_1;
 drop table t1;
@@ -1839,6 +1842,184 @@ call bug24491();
 drop procedure bug24491;
 drop tables t1;
 
+#
+# BUG#18914: Calling certain SPs from triggers fail
+#
+# Failing to call a procedure that does implicit commit from a trigger
+# is a correct behaviour, however the error message was misleading.
+#
+# DROP TABLE IF EXISTS is also fixed to give correct error instead of
+# "Table doesn't exist". 
+#
+--disable_warnings
+DROP FUNCTION IF EXISTS bug18914_f1;
+DROP FUNCTION IF EXISTS bug18914_f2;
+DROP PROCEDURE IF EXISTS bug18914_p1;
+DROP PROCEDURE IF EXISTS bug18914_p2;
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+
+CREATE PROCEDURE bug18914_p1() CREATE TABLE t2 (i INT);
+CREATE PROCEDURE bug18914_p2() DROP TABLE IF EXISTS no_such_table;
+
+delimiter |;
+CREATE FUNCTION bug18914_f1() RETURNS INT
+BEGIN
+  CALL bug18914_p1();
+  RETURN 1;
+END |
+
+CREATE FUNCTION bug18914_f2() RETURNS INT
+BEGIN
+  CALL bug18914_p2();
+  RETURN 1;
+END |
+delimiter ;|
+
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL bug18914_p1();
+
+--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+INSERT INTO t1 VALUES (1);
+
+--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+SELECT bug18914_f1();
+
+--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
+SELECT bug18914_f2();
+
+--error ER_NO_SUCH_TABLE
+SELECT * FROM t2;
+
+DROP FUNCTION bug18914_f1;
+DROP FUNCTION bug18914_f2;
+DROP PROCEDURE bug18914_p1;
+DROP PROCEDURE bug18914_p2;
+DROP TABLE t1;
+
+#
+# Bug#20713 (Functions will not not continue for SQLSTATE VALUE '42S02')
+#
+
+--disable_warnings
+drop table if exists bogus_table_20713;
+drop function if exists func_20713_a;
+drop function if exists func_20713_b;
+--enable_warnings
+
+create table bogus_table_20713( id int(10) not null primary key);
+insert into bogus_table_20713 values (1), (2), (3);
+
+delimiter //;
+
+create function func_20713_a() returns int(11)
+begin
+  declare id int;
+
+  declare continue handler for sqlexception set id=null;
+
+  set @in_func := 1;
+  set id = (select id from bogus_table_20713 where id = 3);
+  set @in_func := 2;
+
+  return id;
+end//
+
+create function func_20713_b() returns int(11)
+begin
+  declare id int;
+
+  declare continue handler for sqlstate value '42S02' set id=null;
+
+  set @in_func := 1;
+  set id = (select id from bogus_table_20713 where id = 3);
+  set @in_func := 2;
+
+  return id;
+end//
+
+delimiter ;//
+
+set @in_func := 0;
+select func_20713_a();
+select @in_func;
+
+set @in_func := 0;
+select func_20713_b();
+select @in_func;
+
+drop table bogus_table_20713;
+
+set @in_func := 0;
+select func_20713_a();
+select @in_func;
+
+set @in_func := 0;
+select func_20713_b();
+select @in_func;
+
+drop function if exists func_20713_a;
+drop function if exists func_20713_b;
+
+#
+# Bug#25345 (Cursors from Functions)
+#
+
+--disable_warnings
+drop table if exists table_25345_a;
+drop table if exists table_25345_b;
+drop procedure if exists proc_25345;
+drop function if exists func_25345;
+drop function if exists func_25345_b;
+--enable_warnings
+
+create table table_25345_a (a int);
+create table table_25345_b (b int);
+
+delimiter ||;
+
+create procedure proc_25345()
+begin
+  declare c1 cursor for select a from table_25345_a;
+  declare c2 cursor for select b from table_25345_b;
+
+  select 1 as result;
+end ||
+
+create function func_25345() returns int(11)
+begin
+  call proc_25345();
+  return 1;
+end ||
+
+create function func_25345_b() returns int(11)
+begin
+  declare c1 cursor for select a from table_25345_a;
+  declare c2 cursor for select b from table_25345_b;
+
+  return 1;
+end ||
+
+delimiter ;||
+
+call proc_25345();
+--error ER_SP_NO_RETSET
+select func_25345();
+select func_25345_b();
+
+drop table table_25345_a;
+
+call proc_25345();
+--error ER_SP_NO_RETSET
+select func_25345();
+select func_25345_b();
+
+drop table table_25345_b;
+drop procedure proc_25345;
+drop function func_25345;
+drop function func_25345_b;
 
 #
 # BUG#NNNN: New bug synopsis

--- 1.208/mysql-test/t/sp.test	2007-03-05 19:42:14 -07:00
+++ 1.209/mysql-test/t/sp.test	2007-03-05 19:42:14 -07:00
@@ -1368,7 +1368,7 @@ end|
 select f11()|
 --error ER_CANT_REOPEN_TABLE
 select f11() from t1|
-# We don't handle temporary tables used by nested functions well
+# Test that using a single table instance at a time works
 create function f12_1() returns int
 begin
   drop temporary table if exists t3;
@@ -1378,11 +1378,9 @@ begin
 end|
 create function f12_2() returns int
   return (select count(*) from t3)|
-# We need clean start to get error
+
 drop temporary table t3|
---error ER_NO_SUCH_TABLE
 select f12_1()|
---error ER_NO_SUCH_TABLE
 select f12_1() from t1 limit 1|
 
 # Cleanup
@@ -6713,6 +6711,53 @@ END|
 CALL bug24117()|
 DROP PROCEDURE bug24117|
 DROP TABLE t3|
+
+#
+# Bug#8407(Stored functions/triggers ignore exception handler)
+#
+
+--disable_warnings
+drop function if exists func_8407_a|
+drop function if exists func_8407_b|
+--enable_warnings
+
+create function func_8407_a() returns int
+begin
+  declare x int;
+
+  declare continue handler for sqlexception
+  begin
+  end;
+
+  select 1 from no_such_view limit 1 into x;
+
+  return x;
+end|
+
+create function func_8407_b() returns int
+begin
+  declare x int default 0;
+
+  declare continue handler for sqlstate '42S02'
+  begin
+    set x:= x+1000;
+  end;
+
+  case (select 1 from no_such_view limit 1)
+    when 1 then set x:= x+1;
+    when 2 then set x:= x+2;
+    else set x:= x+100;
+  end case;
+  set x:=x + 500;
+  
+  return x;
+end|
+
+select func_8407_a()|
+select func_8407_b()|
+
+drop function func_8407_a|
+drop function func_8407_b|
 
 #
 # NOTE: The delimiter is `|`, and not `;`. It is changed to `;`

--- 1.230/sql/sp_head.cc	2007-03-05 19:42:14 -07:00
+++ 1.231/sql/sp_head.cc	2007-03-05 19:42:14 -07:00
@@ -2374,16 +2374,11 @@ sp_lex_keeper::reset_lex_and_exec_core(T
       m_lex->mark_as_requiring_prelocking(lex_query_tables_own_last);
     }
   }
-    
+
   reinit_stmt_before_use(thd, m_lex);
-  /*
-    If requested check whenever we have access to tables in LEX's table list
-    and open and lock them before executing instructtions core function.
-  */
-  if (open_tables &&
-      (check_table_access(thd, SELECT_ACL, m_lex->query_tables, 0) ||
-       open_and_lock_tables(thd, m_lex->query_tables)))
-      res= -1;
+
+  if (open_tables)
+    res= instr->exec_open_and_lock_tables(thd, m_lex->query_tables, nextp);
 
   if (!res)
     res= instr->exec_core(thd, nextp);
@@ -2432,6 +2427,33 @@ sp_lex_keeper::reset_lex_and_exec_core(T
   sp_instr class functions
 */
 
+int sp_instr::exec_open_and_lock_tables(THD *thd, TABLE_LIST *tables,
+                                        uint *nextp)
+{
+  int result;
+
+  /*
+    Check whenever we have access to tables for this statement
+    and open and lock them before executing instructions core function.
+  */
+  if (check_table_access(thd, SELECT_ACL, tables, 0)
+      || open_and_lock_tables(thd, tables))
+  {
+    get_cont_dest(nextp);
+    result= -1;
+  }
+  else
+    result= 0;
+
+  return result;
+}
+
+void sp_instr::get_cont_dest(uint *nextp)
+{
+  *nextp= m_ip+1;
+}
+
+
 int sp_instr::exec_core(THD *thd, uint *nextp)
 {
   DBUG_ASSERT(0);
@@ -2610,6 +2632,15 @@ sp_instr_set_trigger_field::print(String
   trigger_field->print(str);
   str->append(STRING_WITH_LEN(":="));
   value->print(str);
+}
+
+/*
+  sp_instr_opt_meta
+*/
+
+void sp_instr_opt_meta::get_cont_dest(uint *nextp)
+{
+  *nextp= m_cont_dest;
 }
 
 

--- 1.90/sql/sp_head.h	2007-03-05 19:42:14 -07:00
+++ 1.91/sql/sp_head.h	2007-03-05 19:42:14 -07:00
@@ -458,6 +458,28 @@ public:
   
   virtual int execute(THD *thd, uint *nextp) = 0;
 
+  /**
+    Execute <code>open_and_lock_tables()</code> for this statement.
+    Open and lock the tables used by this statement, as a pre-requisite
+    to execute the core logic of this instruction with
+    <code>exec_core()</code>.
+    If this statement fails, the next instruction to execute is also returned.
+    This is useful when a user defined SQL continue handler needs to be
+    executed.
+    @param thd the current thread
+    @param tables the list of tables to open and lock
+    @param nextp the continuation instruction, returned to the caller if this
+    method fails.
+    @return zero on success, non zero on failure.
+  */
+  int exec_open_and_lock_tables(THD *thd, TABLE_LIST *tables, uint *nextp);
+
+  /**
+    Get the continuation destination of this instruction.
+    @param nextp the continuation destination (output)
+  */
+  virtual void get_cont_dest(uint *nextp);
+
   /*
     Execute core function of instruction after all preparations (e.g.
     setting of proper LEX, saving part of the thread context have been
@@ -721,6 +743,8 @@ public:
 
   virtual void set_destination(uint old_dest, uint new_dest)
     = 0;
+
+  virtual void get_cont_dest(uint *nextp);
 
 protected:
 
Thread
bk commit into 5.0 tree (malff:1.2390) BUG#8407marc.alff6 Mar