MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:kroki Date:August 28 2006 4:34pm
Subject:bk commit into 5.0 tree (kroki:1.2241) BUG#18914
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of tomash. When tomash 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, 2006-08-28 20:34:17+04:00, kroki@stripped +3 -0
  BUG#18914: Calling certain SPs from triggers fail.
  
  CREATE TABLE from a function or a trigger should not work because of
  implicit commit, however wrong error was reported:
  "Table doesn't exist".
  
  The problem was that when a procedure that creates a table was used in a
  function or a trigger the table that wasn't yet created was added to the
  list of tables for prelocking, thus giving the error "Table doesn't
  exist".
  
  The solution is to not try to prelock such non-existent tables.  This
  does not break prelocking because later we'll report the error anyway.
  
  This patch also fixes DROP TABLE IF EXISTS in the case there are no
  such table: we'll correctly give "implicit commit" error instead of
  "table doesn't exists".
  
  TO THE REVIEWER: I'm worried a little bit about the fact that we do not
  lock tables to be deleted any more, as later we call functions named
  abort_locked_tables() and drop_locked_tables().  Also, I did not add
  the asserts, I'm not sure if asserts for other forbidden statements
  are there.

  mysql-test/r/sp-error.result@stripped, 2006-08-28 20:34:13+04:00, kroki@stripped +33 -0
    Add result for bug#18914: Calling certain SPs from triggers fail.

  mysql-test/t/sp-error.test@stripped, 2006-08-28 20:34:13+04:00, kroki@stripped +58 -0
    Add test case for bug#18914: Calling certain SPs from triggers fail.

  sql/sp_head.cc@stripped, 2006-08-28 20:34:13+04:00, kroki@stripped +30 -8
    Rename SP_TABLE::temp to SP_TABLE::create, and use it to mark tables
    that are yet to be created.  Skip over such tables in
    sp_head::add_used_tables_to_table_list().
    
    Do not add tables to be deleted to the prelocking list.

# 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:	kroki
# Host:	moonlight.intranet
# Root:	/home/tomash/src/mysql_ab/mysql-5.0-bug18914

--- 1.107/mysql-test/r/sp-error.result	2006-08-28 20:34:25 +04:00
+++ 1.108/mysql-test/r/sp-error.result	2006-08-28 20:34:25 +04:00
@@ -1187,3 +1187,36 @@ END;
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS bug14702()
 BEGIN
 END' at line 1
+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;

--- 1.107/mysql-test/t/sp-error.test	2006-08-28 20:34:25 +04:00
+++ 1.108/mysql-test/t/sp-error.test	2006-08-28 20:34:25 +04:00
@@ -1729,6 +1729,64 @@ END;
 
 
 #
+# 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#NNNN: New bug synopsis
 #
 #--disable_warnings

--- 1.219/sql/sp_head.cc	2006-08-28 20:34:25 +04:00
+++ 1.220/sql/sp_head.cc	2006-08-28 20:34:25 +04:00
@@ -3337,7 +3337,22 @@ typedef struct st_sp_table
   */
   LEX_STRING qname;
   uint db_length, table_name_length;
-  bool temp;               /* true if corresponds to a temporary table */
+
+  /*
+    'create' is true if this element corresponds to the table to be
+    created in routine so this table have to be excluded from the list
+    of tables used by this routine during creation of table list for
+    prelocking.
+
+    Note that this does not mean that we allow creation of
+    non-temporary tables in functions and triggers (such statements
+    emit error about attempt to do implicit commit).  So we are not
+    introducing problems with prelocking by doing such exclusion and
+    temporary tables are the only case for which this member is really
+    important/exploited.
+  */
+  bool create;
+
   thr_lock_type lock_type; /* lock type used for prelocking */
   uint lock_count;
   uint query_lock_count;
@@ -3377,8 +3392,14 @@ sp_head::merge_table_list(THD *thd, TABL
 {
   SP_TABLE *tab;
 
-  if (lex_for_tmp_check->sql_command == SQLCOM_DROP_TABLE &&
-      lex_for_tmp_check->drop_temporary)
+  /*
+    Do not add tables to be deleted to the prelocking list, because it
+    is either temporary table, or we will give a error anyway because
+    DROP TABLE does implicit commit, and thus not allowed from
+    function or trigger.  And we should not try to prelock it because
+    at this point we do not know if it exists.
+  */
+  if (lex_for_tmp_check->sql_command == SQLCOM_DROP_TABLE)
     return TRUE;
 
   for (uint i= 0 ; i < m_sptabs.records ; i++)
@@ -3412,7 +3433,7 @@ sp_head::merge_table_list(THD *thd, TABL
       if ((tab= (SP_TABLE *)hash_search(&m_sptabs, (byte *)tname, tlen)) ||
           ((tab= (SP_TABLE *)hash_search(&m_sptabs, (byte *)tname,
                                         tlen - alen - 1)) &&
-           tab->temp))
+           tab->create))
       {
         if (tab->lock_type < table->lock_type)
           tab->lock_type= table->lock_type; // Use the table with the highest lock type
@@ -3425,10 +3446,9 @@ sp_head::merge_table_list(THD *thd, TABL
 	if (!(tab= (SP_TABLE *)thd->calloc(sizeof(SP_TABLE))))
 	  return FALSE;
 	if (lex_for_tmp_check->sql_command == SQLCOM_CREATE_TABLE &&
-	    lex_for_tmp_check->query_tables == table &&
-	    lex_for_tmp_check->create_info.options & HA_LEX_CREATE_TMP_TABLE)
+	    lex_for_tmp_check->query_tables == table)
         {
-	  tab->temp= TRUE;
+          tab->create= TRUE;
           tab->qname.length= tlen - alen - 1;
         }
         else
@@ -3495,7 +3515,9 @@ sp_head::add_used_tables_to_table_list(T
     char *tab_buff, *key_buff;
     TABLE_LIST *table;
     SP_TABLE *stab= (SP_TABLE *)hash_element(&m_sptabs, i);
-    if (stab->temp)
+
+    /* Skip over tables that are yet to be created. */
+    if (stab->create)
       continue;
 
     if (!(tab_buff= (char *)thd->calloc(ALIGN_SIZE(sizeof(TABLE_LIST)) *
Thread
bk commit into 5.0 tree (kroki:1.2241) BUG#18914kroki28 Aug