MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:dlenev Date:August 18 2005 3:07pm
Subject:bk commit into 5.0 tree (dlenev:1.1991) BUG#11896
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of dlenev. When dlenev 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
  1.1991 05/08/18 19:07:23 dlenev@stripped +6 -0
  Fix for bug #11896 "Partial locking in case of recursive trigger definitions".
  
  If we are in stored function or trigger we should ensure that we won't change
  table that is already used by calling statement (this can damage table or
  easily cause infinite loops). Particularly this means that recursive triggers
  should be disallowed.

  sql/sql_base.cc
    1.282 05/08/18 19:07:17 dlenev@stripped +50 -24
    open_table():
      If we are in stored function or trigger we should ensure that
      we won't change table that is already used by calling statement
      (this can damage table or easily cause infinite loops).
      So if we are opening table for writing, we should check that it
      is not already open by some calling stamement.

  sql/share/errmsg.txt
    1.42 05/08/18 19:07:17 dlenev@stripped +2 -0
    Added error messages for complaining about situations when in function or
    trigger we try to change table which is used in statement invoking this
    function or trigger.

  mysql-test/t/trigger.test
    1.23 05/08/18 19:07:17 dlenev@stripped +36 -0
    Added test for bug #11896 "Partial locking in case of recursive trigger
    definitions".

  mysql-test/t/sp-error.test
    1.81 05/08/18 19:07:16 dlenev@stripped +29 -0
    Added tests checking that in functions we don't allow to update tables which
    are used by statements which invoke these functions.

  mysql-test/r/trigger.result
    1.18 05/08/18 19:07:16 dlenev@stripped +41 -0
    Added test for bug #11896 "Partial locking in case of recursive trigger
    definitions".

  mysql-test/r/sp-error.result
    1.78 05/08/18 19:07:16 dlenev@stripped +30 -0
    Added tests checking that in functions we don't allow to update tables which
    are used by statements which invoke these functions.

# 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:	dlenev
# Host:	brandersnatch.localdomain
# Root:	/home/dlenev/src/mysql-5.0-bg11896

--- 1.281/sql/sql_base.cc	2005-08-13 00:56:13 +04:00
+++ 1.282/sql/sql_base.cc	2005-08-18 19:07:17 +04:00
@@ -1046,35 +1046,61 @@
   {						// Using table locks
     TABLE *best_table= 0;
     int best_distance= INT_MIN;
+    bool check_if_used= thd->prelocked_mode &&
+                        ((int) table_list->lock_type >=
+                         (int) TL_WRITE_ALLOW_WRITE);
     for (table=thd->open_tables; table ; table=table->next)
     {
       if (table->s->key_length == key_length &&
-          !memcmp(table->s->table_cache_key, key, key_length) &&
-          !my_strcasecmp(system_charset_info, table->alias, alias) &&
-          table->query_id != thd->query_id && /* skip tables already used */
-          !(thd->prelocked_mode && table->query_id))
+          !memcmp(table->s->table_cache_key, key, key_length))
       {
-        int distance= ((int) table->reginfo.lock_type -
-                       (int) table_list->lock_type);
-        /*
-          Find a table that either has the exact lock type requested,
-          or has the best suitable lock. In case there is no locked
-          table that has an equal or higher lock than requested,
-          we us the closest matching lock to be able to produce an error
-          message about wrong lock mode on the table. The best_table is changed
-          if bd < 0 <= d or bd < d < 0 or 0 <= d < bd.
-
-          distance <  0 - No suitable lock found
-          distance >  0 - we have lock mode higher then we require
-          distance == 0 - we have lock mode exactly which we need
-        */
-        if (best_distance < 0 && distance > best_distance ||
-            distance >= 0 && distance < best_distance)
+        if (check_if_used && table->query_id &&
+            table->query_id != thd->query_id)
+        {
+          /*
+            If we are in stored function or trigger we should ensure that
+            we won't change table that is already used by calling statement.
+            So if we are opening table for writing, we should check that it
+            is not already open by some calling stamement.
+          */
+          my_error(ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG, MYF(0),
+                   table->s->table_name);
+          DBUG_RETURN(0);
+        }
+        if (!my_strcasecmp(system_charset_info, table->alias, alias) &&
+            table->query_id != thd->query_id && /* skip tables already used */
+            !(thd->prelocked_mode && table->query_id))
         {
-          best_distance= distance;
-          best_table= table;
-          if (best_distance == 0)               // Found perfect lock
-            break;
+          int distance= ((int) table->reginfo.lock_type -
+                         (int) table_list->lock_type);
+          /*
+            Find a table that either has the exact lock type requested,
+            or has the best suitable lock. In case there is no locked
+            table that has an equal or higher lock than requested,
+            we us the closest matching lock to be able to produce an error
+            message about wrong lock mode on the table. The best_table
+            is changed if bd < 0 <= d or bd < d < 0 or 0 <= d < bd.
+
+            distance <  0 - No suitable lock found
+            distance >  0 - we have lock mode higher then we require
+            distance == 0 - we have lock mode exactly which we need
+          */
+          if (best_distance < 0 && distance > best_distance ||
+              distance >= 0 && distance < best_distance)
+          {
+            best_distance= distance;
+            best_table= table;
+            if (best_distance == 0 && !check_if_used)
+            {
+              /*
+                If we have found perfect match and we don't need to check that
+                table is not used by one of calling statements (assuming that
+                we are inside of function or trigger) we can finish iterating
+                through open tables list.
+              */
+              break;
+            }
+          }
         }
       }
     }

--- 1.41/sql/share/errmsg.txt	2005-08-13 00:58:05 +04:00
+++ 1.42/sql/share/errmsg.txt	2005-08-18 19:07:17 +04:00
@@ -5395,3 +5395,5 @@
 	eng "Display width out of range for column '%-.64s' (max = %d)"
 ER_XAER_DUPID XAE08
         eng "XAER_DUPID: The XID already exists"
+ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+        eng "Can't update table '%-.64s' in stored function/trigger because it is already used by statement which invoked this stored function/trigger."

--- 1.17/mysql-test/r/trigger.result	2005-08-11 10:39:28 +04:00
+++ 1.18/mysql-test/r/trigger.result	2005-08-18 19:07:16 +04:00
@@ -664,3 +664,44 @@
 update t1 set data = 1;
 update t1 set data = 2;
 drop table t1;
+create table t1 (f1 integer);
+create table t2 (f2 integer);
+create trigger t1_ai after insert on t1
+for each row insert into t2 values (new.f1+1);
+create trigger t2_ai after insert on t2
+for each row insert into t1 values (new.f2+1);
+insert into t1 values (1);
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+select * from t1;
+f1
+1
+select * from t2;
+f2
+2
+drop trigger t1_ai;
+drop trigger t2_ai;
+create trigger t1_bu before update on t1
+for each row insert into t1 values (2);
+update t1 set f1= 10;
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+select * from t1;
+f1
+1
+drop trigger t1_bu;
+create trigger t1_bu before update on t1
+for each row delete from t1 where f1=new.f1;
+update t1 set f1= 10;
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+select * from t1;
+f1
+1
+drop trigger t1_bu;
+create trigger t1_bi before insert on t1
+for each row set new.f1=(select sum(f1) from t1);
+insert into t1 values (3);
+select * from t1;
+f1
+1
+1
+drop trigger t1_bi;
+drop tables t1, t2;

--- 1.22/mysql-test/t/trigger.test	2005-08-11 10:39:28 +04:00
+++ 1.23/mysql-test/t/trigger.test	2005-08-18 19:07:17 +04:00
@@ -685,3 +685,39 @@
 
 connection default;
 drop table t1;
+
+# Test for bug #11896 "Partial locking in case of recursive trigger
+# definitions". Recursion in triggers should not be allowed.
+# We also should not allow to change tables which are used in
+# statements invoking this trigger.
+create table t1 (f1 integer);
+create table t2 (f2 integer);
+create trigger t1_ai after insert on t1
+  for each row insert into t2 values (new.f1+1);
+create trigger t2_ai after insert on t2
+  for each row insert into t1 values (new.f2+1);
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+insert into t1 values (1);
+select * from t1;
+select * from t2;
+drop trigger t1_ai;
+drop trigger t2_ai;
+create trigger t1_bu before update on t1
+  for each row insert into t1 values (2);
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+update t1 set f1= 10;
+select * from t1;
+drop trigger t1_bu;
+create trigger t1_bu before update on t1
+  for each row delete from t1 where f1=new.f1;
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+update t1 set f1= 10;
+select * from t1;
+drop trigger t1_bu;
+# This should work tough
+create trigger t1_bi before insert on t1
+  for each row set new.f1=(select sum(f1) from t1);
+insert into t1 values (3);
+select * from t1;
+drop trigger t1_bi;
+drop tables t1, t2;

--- 1.77/mysql-test/r/sp-error.result	2005-08-10 10:31:26 +04:00
+++ 1.78/mysql-test/r/sp-error.result	2005-08-18 19:07:16 +04:00
@@ -299,6 +299,36 @@
 unlock tables|
 lock tables mysql.proc write|
 unlock tables|
+drop function if exists f1|
+create function f1(i int) returns int
+begin
+insert into t1 (val) values (i);
+return 0;
+end|
+select val, f1(val) from t1|
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+select val, f1(val) from t1 as tab|
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+select * from t1|
+val	x
+42	3.1
+19	1.2
+update t1 set val= f1(val)|
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+select * from t1|
+val	x
+42	3.1
+19	1.2
+select f1(17)|
+f1(17)
+0
+select * from t1|
+val	x
+42	3.1
+19	1.2
+17	NULL
+delete from t1 where val= 17|
+drop function f1|
 create procedure bug1965()
 begin
 declare c cursor for select val from t1 order by valname;

--- 1.80/mysql-test/t/sp-error.test	2005-08-10 10:31:26 +04:00
+++ 1.81/mysql-test/t/sp-error.test	2005-08-18 19:07:16 +04:00
@@ -410,6 +410,35 @@
 
 
 #
+# Check that in functions we don't allow to update tables which
+# are used by statements which invoke these functions.
+#
+--disable_warnings
+drop function if exists f1|
+--enable_warnings
+create function f1(i int) returns int
+begin
+  insert into t1 (val) values (i);
+  return 0;
+end|
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+select val, f1(val) from t1|
+# Table alias should not matter
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+select val, f1(val) from t1 as tab|
+select * from t1|
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+update t1 set val= f1(val)|
+select * from t1|
+# But this should be OK
+select f1(17)|
+select * from t1|
+# Cleanup
+delete from t1 where val= 17|
+drop function f1|
+
+
+#
 # BUG#1965
 #
 create procedure bug1965()
Thread
bk commit into 5.0 tree (dlenev:1.1991) BUG#11896dlenev18 Aug