List:Commits« Previous MessageNext Message »
From:kroki Date:August 23 2006 5:12pm
Subject:bk commit into 5.0 tree (kroki:1.2235) BUG#21714
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-23 21:12:02+04:00, kroki@stripped +4 -0
  BUG#21483: Server abort or deadlock on INSERT DELAYED with another
             implicit insert
  
  If the statement operates on several tables via functions or triggers,
  it is vital to execute all operations at one time, none of the actions
  can be delayed, because all tables should be locked simultaneously.
  
  The solution is to downgrade INSERT DELAYED to normal INSERT if the
  statement uses functions that access tables or triggers, or is called
  from a function or a trigger.  This also fixes bug#20497 (Trigger with
  INSERT DELAYED causes Error 1165) and bug#21714 (Wrong NEW.value and
  server abort on INSERT DELAYED to a table with a trigger).
  
  REPLACE DELAYED is handled by the same code.

  mysql-test/r/insert.result@stripped, 2006-08-23 21:11:56+04:00, kroki@stripped +116 -0
    Add result for bug#21483: Server abort or deadlock on INSERT DELAYED
    with another implicit insert.

  mysql-test/t/insert.test@stripped, 2006-08-23 21:11:57+04:00, kroki@stripped +146 -0
    Add test case for bug#21483: Server abort or deadlock on INSERT DELAYED
    with another implicit insert.

  sql/sp_head.cc@stripped, 2006-08-23 21:11:57+04:00, kroki@stripped +7 -0
    Table list from sp_head::merge_table_list() is used only in prelocked
    mode, so we downgrade any TL_WRITE_DELAYED to TL_WRITE.

  sql/sql_insert.cc@stripped, 2006-08-23 21:11:57+04:00, kroki@stripped +75 -18
    If the statement is (or will be) executed in prelocked mode, we
    downgrade TL_WRITE_DELAYED lock type to TL_WRITE.

# 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-bug21483

--- 1.197/sql/sql_insert.cc	2006-08-23 21:12:08 +04:00
+++ 1.198/sql/sql_insert.cc	2006-08-23 21:12:08 +04:00
@@ -351,7 +351,8 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
 #else
   if ((lock_type == TL_WRITE_DELAYED &&
        ((specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE)) ||
-	thd->slave_thread || !thd->variables.max_insert_delayed_threads)) ||
+        thd->slave_thread || !thd->variables.max_insert_delayed_threads ||
+        thd->prelocked_mode || thd->lex->requires_prelocking())) ||
       (lock_type == TL_WRITE_CONCURRENT_INSERT && duplic == DUP_REPLACE) ||
       (duplic == DUP_UPDATE))
     lock_type=TL_WRITE;
@@ -371,32 +372,88 @@ bool mysql_insert(THD *thd,TABLE_LIST *t
 	DBUG_RETURN(TRUE);
       }
     }
-    if ((table= delayed_get_table(thd,table_list)) && !thd->is_fatal_error)
+
+    /*
+      We have to open tables first to know if prelocked mode will be
+      used.
+    */
+    uint counter;
+    if (open_tables(thd, &table_list, &counter, 0))
+      DBUG_RETURN(TRUE);
+
+    DBUG_ASSERT(table == NULL);
+    DBUG_ASSERT(thd->net.last_errno != ER_WRONG_OBJECT);
+
+    if (!thd->lex->requires_prelocking())
     {
       /*
-        Open tables used for sub-selects or in stored functions, will also
-        cache these functions.
+        If not in prelocked mode, try to start or reinitialize INSERT
+        DELAYED thread.
       */
-      res= open_and_lock_tables(thd, table_list->next_global);
-      /*
-	First is not processed by open_and_lock_tables() => we need set
-	updateability flags "by hands".
-      */
-      if (!table_list->derived && !table_list->view)
-        table_list->updatable= 1;  // usual table
+      table= delayed_get_table(thd, table_list);
+
+      if (table == NULL || thd->is_fatal_error)
+      {
+        if (thd->net.last_errno != ER_WRONG_OBJECT)
+          table= NULL;                          /* Force normal insert */
+        else
+          DBUG_RETURN(TRUE);
+      }
     }
-    else if (thd->net.last_errno != ER_WRONG_OBJECT)
+
+    /*
+      This loop does the same thing as open_and_lock_tables(), but we
+      start with the tables already open, and on every iteration we
+      check if prelocked mode will be used.  If so, we downgrade
+      TL_WRITE_DELAYED to TL_WRITE and try to lock all tables.  If
+      prelocked mode won't be used, we try to lock second and
+      subsequent tables, because the first table is locked in INSERT
+      DELAYED thread already.
+    */
+    while (1)
     {
-      /* Too many delayed insert threads;  Use a normal insert */
-      table_list->lock_type= lock_type= TL_WRITE;
-      res= open_and_lock_tables(thd, table_list);
+      TABLE_LIST *tables;
+      if (table && !thd->lex->requires_prelocking())
+      {
+        DBUG_ASSERT(counter > 0);
+
+        table_list->lock_type= lock_type= TL_WRITE_DELAYED;
+        tables= table_list->next_global;
+        counter--;
+      }
+      else
+      {
+        table_list->lock_type= lock_type= TL_WRITE;
+        tables= table_list;
+      }
+
+      bool need_reopen;
+      if (!lock_tables(thd, tables, counter, &need_reopen))
+      {
+        if (mysql_handle_derived(thd->lex, &mysql_derived_prepare) ||
+            (thd->fill_derived_tables() &&
+             mysql_handle_derived(thd->lex, &mysql_derived_filling)))
+          DBUG_RETURN(TRUE);
+
+        break;
+      }
+      else
+      {
+        if (!need_reopen)
+          DBUG_RETURN(TRUE);
+
+        close_tables_for_reopen(thd, &table_list);
+        if (open_tables(thd, &table_list, &counter, 0))
+          DBUG_RETURN(TRUE);
+      }
     }
   }
   else
 #endif /* EMBEDDED_LIBRARY */
-    res= open_and_lock_tables(thd, table_list);
-  if (res || thd->is_fatal_error)
-    DBUG_RETURN(TRUE);
+  {
+    if (open_and_lock_tables(thd, table_list))
+      DBUG_RETURN(TRUE);
+  }
 
   thd->proc_info="init";
   thd->used_tables=0;

--- 1.24/mysql-test/r/insert.result	2006-08-23 21:12:08 +04:00
+++ 1.25/mysql-test/r/insert.result	2006-08-23 21:12:08 +04:00
@@ -331,3 +331,119 @@ select row_count();
 row_count()
 1
 drop table t1;
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+CREATE TABLE t1 (i INT);
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+INSERT INTO t1 VALUES (1);
+RETURN 1;
+END |
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+INSERT DELAYED INTO t1 VALUES (2);
+RETURN 1;
+END |
+SELECT f1();
+f1()
+1
+SELECT f2();
+f2()
+1
+INSERT INTO t1 VALUES (3);
+INSERT DELAYED INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (f1());
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+INSERT DELAYED INTO t1 VALUES (f1());
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+INSERT INTO t1 VALUES (f2());
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+INSERT DELAYED INTO t1 VALUES (f2());
+ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+INSERT INTO t1 VALUES (NEW.i);
+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.
+INSERT DELAYED 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;
+i
+1
+2
+3
+4
+DROP FUNCTION f2;
+DROP FUNCTION f1;
+DROP TABLE t1;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (i INT);
+CREATE TABLE t2 (i INT);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (NEW.i);
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (NEW.i);
+CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (OLD.i);
+INSERT INTO t1 VALUES (1);
+INSERT DELAYED INTO t1 VALUES (2);
+SELECT * FROM t1;
+i
+1
+2
+UPDATE t1 SET i = 3 WHERE i = 1;
+SELECT * FROM t1;
+i
+3
+2
+DELETE FROM t1 WHERE i = 3;
+SELECT * FROM t1;
+i
+2
+SELECT * FROM t2;
+i
+1
+2
+3
+3
+DROP TABLE t1, t2;
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (i INT);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+SET @a= NEW.i;
+SET @a= 0;
+INSERT DELAYED INTO t1 VALUES (1);
+SELECT @a;
+@a
+1
+INSERT DELAYED INTO t1 VALUES (2);
+SELECT @a;
+@a
+2
+DROP TABLE t1;
+CREATE TABLE t1 (i INT);
+CREATE TABLE t2 (i INT);
+CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
+INSERT INTO t2 VALUES (NEW.i);
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (NEW.i);
+CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW
+INSERT DELAYED INTO t2 VALUES (OLD.i);
+INSERT DELAYED INTO t1 VALUES (1);
+SELECT * FROM t1;
+i
+1
+UPDATE t1 SET i = 2 WHERE i = 1;
+SELECT * FROM t1;
+i
+2
+DELETE FROM t1 WHERE i = 2;
+SELECT * FROM t1;
+i
+SELECT * FROM t2;
+i
+1
+2
+2
+DROP TABLE t1, t2;
+End of 5.0 tests.

--- 1.23/mysql-test/t/insert.test	2006-08-23 21:12:08 +04:00
+++ 1.24/mysql-test/t/insert.test	2006-08-23 21:12:08 +04:00
@@ -210,3 +210,149 @@ select row_count();
 insert into t1 values (5, 5) on duplicate key update data= data + 10;
 select row_count();
 drop table t1;
+
+
+#
+# BUG#21483: Server abort or deadlock on INSERT DELAYED with another
+# implicit insert 
+#
+# The solution is to downgrade INSERT DELAYED to normal INSERT if the
+# statement uses functions and access tables or triggers, or is called
+# from a function or a trigger.
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+delimiter |;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+  INSERT INTO t1 VALUES (1);
+  RETURN 1;
+END |
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+  INSERT DELAYED INTO t1 VALUES (2);
+  RETURN 1;
+END |
+delimiter ;|
+
+SELECT f1();
+SELECT f2();
+INSERT INTO t1 VALUES (3);
+INSERT DELAYED INTO t1 VALUES (4);
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT INTO t1 VALUES (f1());
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT DELAYED INTO t1 VALUES (f1());
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT INTO t1 VALUES (f2());
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT DELAYED INTO t1 VALUES (f2());
+
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  INSERT INTO t1 VALUES (NEW.i);
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT INTO t1 VALUES (1);
+
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT DELAYED INTO t1 VALUES (1);
+
+SELECT * FROM t1;
+
+DROP FUNCTION f2;
+DROP FUNCTION f1;
+DROP TABLE t1;
+
+
+#
+# BUG#20497: Trigger with INSERT DELAYED causes Error 1165
+#
+# The solution is to downgrade INSERT DELAYED to normal INSERT if the
+# statement uses functions and access tables or triggers, or is called
+# from a function or a trigger.
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+CREATE TABLE t2 (i INT);
+
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  INSERT DELAYED INTO t2 VALUES (NEW.i);
+
+CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
+  INSERT DELAYED INTO t2 VALUES (NEW.i);
+
+CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW
+  INSERT DELAYED INTO t2 VALUES (OLD.i);
+
+INSERT INTO t1 VALUES (1);
+INSERT DELAYED INTO t1 VALUES (2);
+SELECT * FROM t1;
+UPDATE t1 SET i = 3 WHERE i = 1;
+SELECT * FROM t1;
+DELETE FROM t1 WHERE i = 3;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+DROP TABLE t1, t2;
+
+
+#
+# BUG#21714: Wrong NEW.value and server abort on INSERT DELAYED to a
+# table with a trigger 
+#
+# The solution is to downgrade INSERT DELAYED to normal INSERT if the
+# statement uses functions and access tables or triggers, or is called
+# from a function or a trigger.
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  SET @a= NEW.i;
+
+SET @a= 0;
+INSERT DELAYED INTO t1 VALUES (1);
+SELECT @a;
+INSERT DELAYED INTO t1 VALUES (2);
+SELECT @a;
+
+DROP TABLE t1;
+
+CREATE TABLE t1 (i INT);
+CREATE TABLE t2 (i INT);
+
+CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
+  INSERT INTO t2 VALUES (NEW.i);
+
+CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
+  INSERT DELAYED INTO t2 VALUES (NEW.i);
+
+CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW
+  INSERT DELAYED INTO t2 VALUES (OLD.i);
+
+INSERT DELAYED INTO t1 VALUES (1);
+SELECT * FROM t1;
+UPDATE t1 SET i = 2 WHERE i = 1;
+SELECT * FROM t1;
+DELETE FROM t1 WHERE i = 2;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+DROP TABLE t1, t2;
+
+
+--echo End of 5.0 tests.

--- 1.221/sql/sp_head.cc	2006-08-23 21:12:08 +04:00
+++ 1.222/sql/sp_head.cc	2006-08-23 21:12:08 +04:00
@@ -3419,6 +3419,13 @@ sp_head::merge_table_list(THD *thd, TABL
       tname[tlen]= '\0';
 
       /*
+        Downgrade the lock type because this table list will be used
+        only in prelocked mode.
+      */
+      if (table->lock_type == TL_WRITE_DELAYED)
+        table->lock_type= TL_WRITE;
+
+      /*
         We ignore alias when we check if table was already marked as temporary
         (and therefore should not be prelocked). Otherwise we will erroneously
         treat table with same name but with different alias as non-temporary.
Thread
bk commit into 5.0 tree (kroki:1.2235) BUG#21714kroki23 Aug