List:Commits« Previous MessageNext Message »
From:konstantin Date:May 11 2007 10:29am
Subject:bk commit into 5.0 tree (kostja:1.2477) BUG#21483
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of kostja. When kostja 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-05-11 14:28:53+04:00, kostja@vajra.(none) +6 -0
  A fix and a test case for 
  Bug#21483 "Server abort or deadlock on INSERT DELAYED with another
  implicit insert"
  Also fixes and adds test cases for bugs:
  20497 "Trigger with INSERT DELAYED causes Error 1165"
  21714 "Wrong NEW.value and server abort on INSERT DELAYED to a
  table with a trigger"
  
  Problem:
  In MySQL INSERT DELAYED is a way to pipe all inserts into a
  given table through a dedicated thread. This is necessary for
  simplistic storage engines like MyISAM, which do not have internal
  concurrency control or threading and thus can not
  achieve efficient INSERT throughput without support from SQL layer.
  DELAYED INSERT works as follows:
  For every distinct table, which can accept DELAYED inserts and has
  pending data to insert, a dedicated thread is created to write data
  to disk. All user connection threads that attempt to
  delayed-insert into this table interact with the dedicated thread in
  producer/consumer fashion: all records to-be inserted are pushed
  into a queue of the dedicated thread, which fetches the records and 
  writes them.
  In this design, client connection threads never open or lock
  the delayed insert table.
  This functionality was introduced in version 3.23 and does not take 
  into account existence of triggers, views, or pre-locking.
  E.g. if INSERT DELAYED is called from a stored function, which,
  in turn, is called from another stored function that uses the delayed
  table, a deadlock can occur, because delayed locking by-passes
  pre-locking. Besides:
   * the delayed thread works directly with the subject table through
     the storage engine API and does not invoke triggers
   * even if it was patched to invoke triggers, if triggers,
     in turn, used other tables, the delayed thread would
     have to open and lock involved tables (use pre-locking).
   * even if it was patched to use pre-locking, without deadlock
     detection the delayed thread could easily lock out user 
     connection threads in case when the same table is used both
     in a trigger and on the right side of the insert query: 
     the delayed thread would not release locks until all inserts 
     are complete, and user connection can not complete inserts 
     without having locks on the tables used on the right side of the
     query.
  
  Solution:
  
  These considerations suggest two general alternatives for the
  future of INSERT DELAYED:
   * it is considered a full-fledged alternative to normal INSERT
   * it is regarded as an optimisation that is only relevant 
     for simplistic engines.
  Since we missed our chance to provide complete support of new
  features when 5.0 was in development, the first alternative
  currently renders infeasible.
  However, even the second alternative, which is to detect
  new features and convert DELAYED insert into a normal insert, 
  is not easy to implement.
  The catch-22 is that we don't know if the subject table has triggers
  or is a view before we open it, and we only open it in the
  delayed thread. We don't know if the query involves pre-locking
  until we have opened all tables, and we always first create
  the delayed thread, and only then open the remaining tables.
  This patch detects the problematic scenarios and converts
  DELAYED INSERT to a normal INSERT:
   * if the statement is executed under pre-locking (e.g. from
     within a stored function or trigger) or the right
     side may require prelocking, we detect the situation
     before creating a delayed insert thread and convert the statement
     to a conventional INSERT.
    * if the subject table is a view or has triggers, we shutdown
     the delayed thread and convert the statement to a conventional
     INSERT.

  mysql-test/r/insert.result@stripped, 2007-05-11 14:28:51+04:00, kostja@vajra.(none) +116 -0
    Update test results.

  mysql-test/t/insert.test@stripped, 2007-05-11 14:28:51+04:00, kostja@vajra.(none) +139 -0
    Add a test case for Bug#21483, Bug#20497, Bug#21714 (INSERT DELAYED
    and stored routines, triggers).

  sql/sp_head.cc@stripped, 2007-05-11 14:28:51+04:00, kostja@vajra.(none) +8 -0
    Upgrade lock type to TL_WRITE when computing the pre-locking set.

  sql/sql_base.cc@stripped, 2007-05-11 14:28:51+04:00, kostja@vajra.(none) +2 -2
    Use a new method.

  sql/sql_insert.cc@stripped, 2007-05-11 14:28:51+04:00, kostja@vajra.(none) +151 -81
    INSERT DELAYED and pre-locking:
    - if  under pre-locking, upgrade the lock type to TL_WRITE
    and proceed as a normal write
    - if DELAYED table has triggers, also request a lock upgrade.
    - make sure errors in the delayed thread are propagated
    correctly

  sql/sql_lex.h@stripped, 2007-05-11 14:28:51+04:00, kostja@vajra.(none) +6 -0
    Add a method to check if a parsed tree refers to stored
    routines.

# 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:	kostja
# Host:	vajra.(none)
# Root:	/opt/local/work/mysql-5.0-21483

--- 1.377/sql/sql_base.cc	2007-04-24 22:34:26 +04:00
+++ 1.378/sql/sql_base.cc	2007-05-11 14:28:51 +04:00
@@ -2277,7 +2277,7 @@
   */
 
   if (!thd->prelocked_mode && !thd->lex->requires_prelocking() &&
-      thd->lex->sroutines_list.elements)
+      thd->lex->uses_stored_routines())
   {
     bool first_no_prelocking, need_prelocking, tabs_changed;
     TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last;
@@ -2465,7 +2465,7 @@
     */
     if (tables->view && !thd->prelocked_mode &&
         !thd->lex->requires_prelocking() &&
-        tables->view->sroutines_list.elements)
+        tables->view->uses_stored_routines())
     {
       /* We have at least one table in TL here. */
       if (!query_tables_last_own)

--- 1.232/sql/sql_insert.cc	2007-05-10 18:27:32 +04:00
+++ 1.233/sql/sql_insert.cc	2007-05-11 14:28:51 +04:00
@@ -61,7 +61,7 @@
 #include "slave.h"
 
 #ifndef EMBEDDED_LIBRARY
-static TABLE *delayed_get_table(THD *thd,TABLE_LIST *table_list);
+static bool delayed_get_table(THD *thd, TABLE_LIST *table_list);
 static int write_delayed(THD *thd,TABLE *table, enum_duplicates dup, bool ignore,
 			 char *query, uint query_length, bool log_on);
 static void end_delayed_insert(THD *thd);
@@ -409,6 +409,7 @@
   downgrade the lock in handler::store_lock() method.
 */
 
+static
 void upgrade_lock_type(THD *thd, thr_lock_type *lock_type,
                        enum_duplicates duplic,
                        bool is_multi_insert)
@@ -422,29 +423,37 @@
 
   if (*lock_type == TL_WRITE_DELAYED)
   {
-#ifdef EMBEDDED_LIBRARY
-    /* No auxiliary threads in the embedded server. */
-    *lock_type= TL_WRITE;
-    return;
-#else
     /*
       We do not use delayed threads if:
-      - we're running in the safe mode or skip-new - the feature
-         is disabled in these modes
-      - we're running this query in statement level replication,
-        on a replication slave - because we must ensure serial
-        execution of queries on the slave
+      - we're running in the safe mode or skip-new mode -- the
+        feature is disabled in these modes
+      - we're executing this statement on a replication slave --
+        we need to ensure serial execution of queries on the
+        slave
       - it is INSERT .. ON DUPLICATE KEY UPDATE - in this case the
         insert cannot be concurrent
+      - this statement is directly or indirectly invoked from
+        a stored function or trigger (under pre-locking) - to
+        avoid deadlocks, since INSERT DELAYED involves a lock
+        upgrade (TL_WRITE_DELAYED -> TL_WRITE) which we should not
+        attempt while keeping other table level locks.
+      - this statement itself may require pre-locking.
+        We should upgrade the lock even though in most cases
+        delayed functionality may work. Unfortunately, we can't
+        easily identify whether the subject table is not used in
+        the statement indirectly via a stored function or trigger:
+        if it is used, that will lead to a deadlock between the
+        client connection and the delayed thread.
     */
     if (specialflag & (SPECIAL_NO_NEW_FUNC | SPECIAL_SAFE_MODE) ||
         thd->slave_thread ||
-        thd->variables.max_insert_delayed_threads == 0)
+        thd->variables.max_insert_delayed_threads == 0 ||
+        thd->prelocked_mode ||
+        thd->lex->uses_stored_routines())
     {
       *lock_type= TL_WRITE;
       return;
     }
-#endif
     bool log_on= (thd->options & OPTION_BIN_LOG ||
                   ! (thd->security_ctx->master_access & SUPER_ACL));
     if (log_on && mysql_bin_log.is_open() && is_multi_insert)
@@ -471,6 +480,56 @@
 
 
 /**
+  Find or create a delayed insert thread for the first table in
+  the table list, then open and lock the remaining tables.
+  If a table can not be used with insert delayed, upgrade the lock
+  and open and lock all tables.
+*/
+
+static
+bool open_and_lock_for_insert_delayed(THD *thd, TABLE_LIST *table_list)
+{
+  DBUG_ENTER("open_and_lock_for_insert_delayed");
+
+#ifndef EMBEDDED_LIBRARY
+  if (delayed_get_table(thd, table_list))
+    DBUG_RETURN(TRUE);
+
+  if (table_list->table)
+  {
+    /*
+      Open tables used for sub-selects or in stored functions, will also
+      cache these functions.
+    */
+    if (open_and_lock_tables(thd, table_list->next_global))
+    {
+      end_delayed_insert(thd);
+      DBUG_RETURN(TRUE);
+    }
+    /*
+      First table was not processed by open_and_lock_tables(),
+      we need to set updatability flag "by hand".
+    */
+    if (!table_list->derived && !table_list->view)
+      table_list->updatable= 1;  // usual table
+    DBUG_RETURN(FALSE);
+  }
+#endif
+  /*
+    * This is embedded library and we don't have auxiliary
+    threads OR
+    * a lock upgrade was requested inside delayed_get_table
+      because
+      - there are too many delayed insert threads OR
+      - the table has triggers.
+    Use a normal insert.
+  */
+  table_list->lock_type= TL_WRITE;
+  DBUG_RETURN(open_and_lock_tables(thd, table_list));
+}
+
+
+/**
   INSERT statement implementation
 */
 
@@ -483,11 +542,6 @@
 		  bool ignore)
 {
   int error, res;
-  /*
-    log_on is about delayed inserts only.
-    By default, both logs are enabled (this won't cause problems if the server
-    runs without --log-update or --log-bin).
-  */
   bool transactional_table, joins_freed= FALSE;
   bool changed;
   uint value_count;
@@ -501,9 +555,14 @@
   Name_resolution_context_state ctx_state;
 #ifndef EMBEDDED_LIBRARY
   char *query= thd->query;
-#endif
+  /*
+    log_on is about delayed inserts only.
+    By default, both logs are enabled (this won't cause problems if the server
+    runs without --log-update or --log-bin).
+  */
   bool log_on= (thd->options & OPTION_BIN_LOG) ||
     (!(thd->security_ctx->master_access & SUPER_ACL));
+#endif
   thr_lock_type lock_type = table_list->lock_type;
   Item *unused_conds= 0;
   DBUG_ENTER("mysql_insert");
@@ -514,7 +573,6 @@
   */
   upgrade_lock_type(thd, &table_list->lock_type, duplic,
                     values_list.elements > 1);
-  lock_type= table_list->lock_type;
 
   /*
     We can't write-delayed into a table locked with LOCK TABLES:
@@ -522,7 +580,7 @@
     never be able to get a lock on the table. QQQ: why not
     upgrade the lock here instead?
   */
-  if (lock_type == TL_WRITE_DELAYED && thd->locked_tables &&
+  if (table_list->lock_type == TL_WRITE_DELAYED && thd->locked_tables &&
       find_locked_table(thd, table_list->db, table_list->table_name))
   {
     my_error(ER_DELAYED_INSERT_TABLE_LOCKED, MYF(0),
@@ -530,36 +588,16 @@
     DBUG_RETURN(TRUE);
   }
 
-#ifndef EMBEDDED_LIBRARY
-  if (lock_type == TL_WRITE_DELAYED)
+  if (table_list->lock_type == TL_WRITE_DELAYED)
   {
-    res= 1;
-    if ((table= delayed_get_table(thd,table_list)) && !thd->is_fatal_error)
-    {
-      /*
-        Open tables used for sub-selects or in stored functions, will also
-        cache these functions.
-      */
-      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
-    }
-    else if (thd->net.last_errno != ER_WRONG_OBJECT)
-    {
-      /* 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);
-    }
+    if (open_and_lock_for_insert_delayed(thd, table_list))
+      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;
@@ -577,6 +615,7 @@
 
   /* mysql_prepare_insert set table_list->table if it was not set */
   table= table_list->table;
+  lock_type= table_list->lock_type;
 
   context= &thd->lex->select_lex.context;
   /*
@@ -1633,19 +1672,32 @@
   Attempt to find or create a delayed insert thread to handle inserts
   into this table.
 
-  @return Return a local copy of the table in the delayed thread
-  @retval  NULL  too many delayed threads OR
-                 this thread ran out of resources OR
-                 a newly created delayed insert thread ran out of resources OR
-                 the delayed insert thread failed to open the table.
-                 In the last three cases an error is set in THD.
+  @return In case of success, table_list->table points to a local copy
+          of the delayed table or is set to NULL, which indicates a
+          request for lock upgrade. In case of failure, value of
+          table_list->table is undefined.
+  @retval TRUE  - this thread ran out of resources OR
+                - a newly created delayed insert thread ran out of
+                  resources OR
+                - the created thread failed to open and lock the table
+                  (e.g. because it does not exist) OR
+                - the table opened in the created thread turned out to
+                  be a view
+  @retval FALSE - table successfully opened OR
+                - too many delayed insert threads OR
+                - the table has triggers and we have to fall back to
+                  a normal INSERT
+                Two latter cases indicate a request for lock upgrade.
+
+  XXX: why do we regard INSERT DELAYED into a view as an error and
+  do not simply a lock upgrade?
 */
 
-static TABLE *delayed_get_table(THD *thd,TABLE_LIST *table_list)
+static
+bool delayed_get_table(THD *thd, TABLE_LIST *table_list)
 {
   int error;
   Delayed_insert *tmp;
-  TABLE *table;
   DBUG_ENTER("delayed_get_table");
 
   /* Must be set in the parser */
@@ -1671,7 +1723,8 @@
       if (!(tmp=new Delayed_insert()))
       {
 	my_error(ER_OUTOFMEMORY,MYF(0),sizeof(Delayed_insert));
-	goto err1;
+        thd->fatal_error();
+        goto end_create;
       }
       pthread_mutex_lock(&LOCK_thread_count);
       thread_count++;
@@ -1680,9 +1733,10 @@
       tmp->thd.query= my_strdup(table_list->table_name,MYF(MY_WME));
       if (tmp->thd.db == NULL || tmp->thd.query == NULL)
       {
+        /* The error is reported */
 	delete tmp;
-	my_message(ER_OUT_OF_RESOURCES, ER(ER_OUT_OF_RESOURCES), MYF(0));
-	goto err1;
+        thd->fatal_error();
+        goto end_create;
       }
       tmp->table_list= *table_list;			// Needed to open table
       tmp->table_list.alias= tmp->table_list.table_name= tmp->thd.query;
@@ -1698,7 +1752,8 @@
 	tmp->unlock();
 	delete tmp;
 	my_error(ER_CANT_CREATE_THREAD, MYF(0), error);
-	goto err1;
+        thd->fatal_error();
+        goto end_create;
       }
 
       /* Wait until table is open */
@@ -1711,41 +1766,44 @@
       thd->proc_info="got old table";
       if (tmp->thd.killed)
       {
-	if (tmp->thd.is_fatal_error)
+	if (tmp->thd.net.report_error)
 	{
-	  /* Copy error message and abort */
-	  thd->fatal_error();
-	  strmov(thd->net.last_error,tmp->thd.net.last_error);
-	  thd->net.last_errno=tmp->thd.net.last_errno;
+          /*
+            Copy the error message. Note that we don't treat fatal
+            errors in the delayed thread as fatal errors in the
+            main thread. Use of my_message will enable stored
+            procedures continue handlers.
+          */
+          my_message(tmp->thd.net.last_errno, tmp->thd.net.last_error,
+                     MYF(0));
 	}
 	tmp->unlock();
-	goto err;
+        goto end_create;
       }
       if (thd->killed)
       {
 	tmp->unlock();
-	goto err;
+        goto end_create;
       }
     }
     pthread_mutex_unlock(&LOCK_delayed_create);
   }
 
   pthread_mutex_lock(&tmp->mutex);
-  table= tmp->get_local_table(thd);
+  table_list->table= tmp->get_local_table(thd);
   pthread_mutex_unlock(&tmp->mutex);
-  if (table)
+  if (table_list->table)
+  {
+    DBUG_ASSERT(tmp->thd.net.report_error == 0 && thd->net.report_error == 0);
     thd->di=tmp;
-  else if (tmp->thd.is_fatal_error)
-    thd->fatal_error();
+  }
   /* Unlock the delayed insert object after its last access. */
   tmp->unlock();
-  DBUG_RETURN((table_list->table=table));
+  DBUG_RETURN(table_list->table == NULL);
 
- err1:
-  thd->fatal_error();
- err:
+end_create:
   pthread_mutex_unlock(&LOCK_delayed_create);
-  DBUG_RETURN(0); // Continue with normal insert
+  DBUG_RETURN(thd->net.report_error);
 }
 
 
@@ -1760,6 +1818,9 @@
   @pre This function is called from the client thread.  Delayed
        insert thread mutex must be acquired before invoking this
        function.
+
+  @return Not-NULL table object on success. NULL in case of an error,
+                    which is set in client_thd.
 */
 
 TABLE *Delayed_insert::get_local_table(THD* client_thd)
@@ -1785,8 +1846,7 @@
       goto error;
     if (dead)
     {
-      strmov(client_thd->net.last_error,thd.net.last_error);
-      client_thd->net.last_errno=thd.net.last_errno;
+      my_message(thd.net.last_errno, thd.net.last_error, MYF(0));
       goto error;
     }
   }
@@ -1831,7 +1891,7 @@
   for (org_field= table->field; *org_field; org_field++, field++)
   {
     if (!(*field= (*org_field)->new_field(client_thd->mem_root, copy, 1)))
-      DBUG_RETURN(0);
+      goto error;
     (*field)->orig_table= copy;			// Remove connection
     (*field)->move_field(adjust_ptrs);		// Point at copy->record[0]
     if (*org_field == found_next_number_field)
@@ -1871,8 +1931,9 @@
 
 /* Put a question in queue */
 
-static int write_delayed(THD *thd,TABLE *table,enum_duplicates duplic, bool ignore,
-			 char *query, uint query_length, bool log_on)
+static
+int write_delayed(THD *thd,TABLE *table,enum_duplicates duplic, bool ignore,
+                  char *query, uint query_length, bool log_on)
 {
   delayed_row *row=0;
   Delayed_insert *di=thd->di;
@@ -2049,6 +2110,15 @@
   {
     thd->fatal_error();
     my_error(ER_ILLEGAL_HA, MYF(0), di->table_list.table_name);
+    goto end;
+  }
+  if (di->table->triggers)
+  {
+    /*
+      Table has triggers. This is not an error, but we do
+      not support triggers with delayed insert. Terminate the delayed
+      thread without an error and thus request lock upgrade.
+    */
     goto end;
   }
   di->table->copy_blobs=1;

--- 1.244/sql/sql_lex.h	2007-04-25 21:38:08 +04:00
+++ 1.245/sql/sql_lex.h	2007-05-11 14:28:51 +04:00
@@ -886,6 +886,12 @@
       query_tables_own_last= 0;
     }
   }
+  /**
+    true if the parsed tree contains references to stored procedures
+    or functions, false otherwise
+  */
+  bool uses_stored_routines() const
+  { return sroutines_list.elements != 0; }
 };
 
 

--- 1.27/mysql-test/r/insert.result	2007-01-22 15:13:57 +03:00
+++ 1.28/mysql-test/r/insert.result	2007-05-11 14:28:51 +04:00
@@ -346,3 +346,119 @@
 12	NULL
 drop view v1;
 drop table t1,t2;
+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.25/mysql-test/t/insert.test	2007-01-22 15:14:18 +03:00
+++ 1.26/mysql-test/t/insert.test	2007-05-11 14:28:51 +04:00
@@ -216,3 +216,142 @@
 drop view v1;
 drop table t1,t2;
 
+
+#
+# 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
+#
+# Fixed by the patch for Bug#21483
+#
+--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 
+#
+# Fixed by the patch for Bug#21483
+#
+--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.244/sql/sp_head.cc	2007-05-07 12:23:09 +04:00
+++ 1.245/sql/sp_head.cc	2007-05-11 14:28:51 +04:00
@@ -3488,6 +3488,14 @@
       tname[tlen]= '\0';
 
       /*
+        Upgrade the lock type because this table list will be used
+        only in pre-locked mode, in which DELAYED inserts are always
+        converted to normal inserts.
+      */
+      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 (kostja:1.2477) BUG#21483konstantin11 May