bk commit into 5.1 tree (istruewing:1.2338) BUG#20627

[ingo]

Below is the list of changes that have just been committed into a local
5.1 repository of istruewing. When istruewing 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-09-26 17:43:06+02:00, istruewing@stripped +3 -0
  Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
  Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
  Merge from 5.0.
  Changed auto_increment handling to the 5.1 pattern.
  Added start-of-new-statement detection for releasing the
  auto_increment values. This aligns the behaviour of delayed
  inserts to non-delayed inserts.

  mysql-test/r/delayed.result@stripped, 2006-09-26 17:43:02+02:00, istruewing@stripped +57
-9
    Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
    Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
    Merge from 5.0.
    Additional test results.

  mysql-test/t/delayed.test@stripped, 2006-09-26 17:43:02+02:00, istruewing@stripped +58
-4
    Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
    Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
    Merge from 5.0.
    Additional tests.

  sql/sql_insert.cc@stripped, 2006-09-26 17:43:02+02:00, istruewing@stripped +66 -33
    Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
    Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
    Merge from 5.0.
    Changed auto_increment handling to the 5.1 pattern.
    Added start-of-new-statement detection for releasing the
    auto_increment values. This aligns the behaviour of delayed
    inserts to non-delayed inserts.

# 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:	istruewing
# Host:	chilla.local
# Root:	/home/mydev/mysql-5.1-bug20627

--- 1.225/sql/sql_insert.cc	2006-09-26 17:43:13 +02:00
+++ 1.226/sql/sql_insert.cc	2006-09-26 17:43:13 +02:00
@@ -1330,7 +1330,7 @@ public:
   bool query_start_used, ignore, log_query;
   bool stmt_depends_on_first_successful_insert_id_in_prev_stmt;
   ulonglong first_successful_insert_id_in_prev_stmt;
-  ulonglong next_insert_id;
+  ulonglong forced_insert_id;
   ulong auto_increment_increment;
   ulong auto_increment_offset;
   timestamp_auto_set_type timestamp_field_type;
@@ -1339,7 +1339,7 @@ public:
   delayed_row(LEX_STRING const query_arg, enum_duplicates dup_arg,
               bool ignore_arg, bool log_query_arg)
     : record(0), dup(dup_arg), ignore(ignore_arg), log_query(log_query_arg),
-      query(query_arg)
+      forced_insert_id(0), query(query_arg)
     {}
   ~delayed_row()
   {
@@ -1358,6 +1358,7 @@ public:
   pthread_cond_t cond,cond_client;
   volatile uint tables_in_use,stacked_inserts;
   volatile bool status,dead;
+  bool error_in_statement;      // be able to ignore values after an error
   COPY_INFO info;
   I_List<delayed_row> rows;
   ulong group_count;
@@ -1366,7 +1367,7 @@ public:
   delayed_insert()
     :locks_in_memory(0),
      table(0),tables_in_use(0),stacked_inserts(0), status(0), dead(0),
-     group_count(0)
+     error_in_statement(0), group_count(0)
   {
     thd.security_ctx->user=thd.security_ctx->priv_user=(char*) delayed_user;
     thd.security_ctx->host=(char*) my_localhost;
@@ -1697,6 +1698,7 @@ write_delayed(THD *thd,TABLE *table, enu
 {
   delayed_row *row;
   delayed_insert *di=thd->di;
+  const Discrete_interval *forced_auto_inc;
   DBUG_ENTER("write_delayed");
   DBUG_PRINT("enter", ("query = '%s' length %u", query.str, query.length));
 
@@ -1746,21 +1748,16 @@ write_delayed(THD *thd,TABLE *table, enu
     thd->first_successful_insert_id_in_prev_stmt;
   row->timestamp_field_type=    table->timestamp_field_type;
 
-  /* The session variable settings can always be copied. */
+  /* Copy session variables. */
   row->auto_increment_increment= thd->variables.auto_increment_increment;
   row->auto_increment_offset=    thd->variables.auto_increment_offset;
-  /*
-    Next insert id must be set for the first value in a multi-row insert
-    only. So clear it after the first use. Assume a multi-row insert.
-    Since the user thread doesn't really execute the insert,
-    thd->next_insert_id is left untouched between the rows. If we copy
-    the same insert id to every row of the multi-row insert, the delayed
-    insert thread would copy this before inserting every row. Thus it
-    tries to insert all rows with the same insert id. This fails on the
-    unique constraint. So just the first row would be really inserted.
-  */
-  row->next_insert_id= thd->next_insert_id;
-  thd->next_insert_id= 0;
+  /* Copy the next forced auto increment value, if any. */
+  if ((forced_auto_inc= thd->auto_inc_intervals_forced.get_next()))
+  {
+    row->forced_insert_id= forced_auto_inc->minimum();
+    DBUG_PRINT("delayed", ("transmitting auto_inc: %lu",
+                           (ulong) row->forced_insert_id));
+  }
 
   di->rows.push_back(row);
   di->stacked_inserts++;
@@ -2013,6 +2010,10 @@ pthread_handler_t handle_delayed_insert(
       MYSQL_LOCK *lock=thd->lock;
       thd->lock=0;
       pthread_mutex_unlock(&di->mutex);
+      /*
+        We need to release next_insert_id before unlocking. This is
+        enforced by handler::ha_external_lock().
+      */
       di->table->file->ha_release_auto_increment();
       mysql_unlock_tables(thd, lock);
       di->group_count=0;
@@ -2141,13 +2142,52 @@ bool delayed_insert::handle_inserts(void
       row->stmt_depends_on_first_successful_insert_id_in_prev_stmt;
     table->timestamp_field_type= row->timestamp_field_type;
 
-    /* The session variable settings can always be copied. */
+    DBUG_PRINT("delayed", ("query: '%s'  length: %u", row->query.str ?
+                           row->query.str : "[NULL]", row->query.length));
+    if ( row->query.str )
+    {
+      /*
+        This is the first value of an INSERT statement.
+        It is the right place to clear a forced insert_id.
+        This is usually done after the last value of an INSERT statement,
+        but we won't know this in the insert delayed thread. But before
+        the first value is sufficiently equivalent to after the last
+        value of the previous statement.
+      */
+      table->file->ha_release_auto_increment();
+      /*
+        Reinitialize the ignore-values-flag for a new statement. See
+        below for a detailed description.
+      */
+      error_in_statement= FALSE;
+    }
+    if (error_in_statement)
+    {
+      /*
+        There was an insert error for a former value of the same
+        statement. Behave like non-delayed insert does. Abort the
+        statement. Or ignore all following values, which comes to the
+        same effect. But we still need to take all values from the
+        queue. And since the values from one statement can appear in the
+        delayed insert thread in several chunks, error_in_statement is a
+        variable in the delayed_insert object and as such survives
+        several calls of delayed_insert::handle_inserts().
+      */
+      pthread_mutex_lock(&mutex);
+      delete row;
+      continue;
+    }
+
+    /* Copy the session variables. */
     thd.variables.auto_increment_increment= row->auto_increment_increment;
     thd.variables.auto_increment_offset=    row->auto_increment_offset;
-    /* Next insert id must be used only if non-zero. */
-    if (row->next_insert_id)
-      thd.next_insert_id= row->next_insert_id;
-    DBUG_PRINT("loop", ("next_insert_id: %lu", (ulong) thd.next_insert_id));
+    /* Copy a forced insert_id, if any. */
+    if (row->forced_insert_id)
+    {
+      DBUG_PRINT("delayed", ("received auto_inc: %lu",
+                             (ulong) row->forced_insert_id));
+      thd.force_one_auto_inc_interval(row->forced_insert_id);
+    }
 
     info.ignore= row->ignore;
     info.handle_duplicates= row->dup;
@@ -2171,19 +2211,12 @@ bool delayed_insert::handle_inserts(void
       thread_safe_increment(delayed_insert_errors,&LOCK_delayed_status);
       row->log_query = 0;
       /*
-        We must reset next_insert_id. Otherwise all following rows may
-        become duplicates. If write_record() failed on a duplicate and
-        next_insert_id would be left unchanged, the next rows would also
-        be tried with the same insert id and would fail. Since the end
-        of a multi-row statement is unknown here, all following rows in
-        the queue would be dropped, regardless which thread added them.
-        After the queue is used up, next_insert_id is cleared and the
-        next run will succeed. This could even happen if these come from
-        the same multi-row statement as the current queue contents. That
-        way it would look somewhat random which rows are rejected after
-        a duplicate.
+        Non-delayed (and non-ignored) insert aborts a statement after an
+        error. Simulate this here by setting the error_in_statement
+        flag. See above for a detailed description. The distinction
+        between ignored and non-ignored is done within write_record().
       */
-      thd.next_insert_id= 0;
+      error_in_statement= TRUE;
     }
 
     if (using_ignore)

--- 1.12/mysql-test/r/delayed.result	2006-09-26 17:43:13 +02:00
+++ 1.13/mysql-test/r/delayed.result	2006-09-26 17:43:13 +02:00
@@ -130,7 +130,8 @@ SET @@session.auto_increment_increment= 
 CREATE TABLE t1 (
 c1 INT(11) NOT NULL AUTO_INCREMENT,
 c2 INT(11) DEFAULT NULL,
-PRIMARY KEY (c1)
+PRIMARY KEY (c1),
+UNIQUE INDEX (c2)
 );
 SET insert_id= 14;
 INSERT INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13);
@@ -144,8 +145,24 @@ INSERT INTO t1 VALUES(  49, 71), (NULL, 
 INSERT INTO t1 VALUES(NULL, 81), (NULL, 82), (NULL, 83);
 SET insert_id= 114;
 INSERT INTO t1 VALUES(NULL, 91);
-ERROR 23000: Duplicate entry '114' for key 1
-INSERT INTO t1 VALUES           (NULL, 92), (NULL, 93);
+ERROR 23000: Duplicate entry '114' for key 'PRIMARY'
+INSERT INTO t1 VALUES            (NULL, 92), (NULL, 93);
+SET insert_id= 174;
+INSERT INTO t1 VALUES(NULL, 111), (NULL, 112), (NULL, 113);
+ERROR 23000: Duplicate entry '174' for key 'PRIMARY'
+INSERT INTO t1 VALUES(NULL, 121), (174,  122), (NULL, 123);
+ERROR 23000: Duplicate entry '174' for key 'PRIMARY'
+INSERT INTO t1 VALUES(NULL,  11), (NULL, 132), (NULL, 133);
+ERROR 23000: Duplicate entry '11' for key 'c2'
+INSERT INTO t1 VALUES(NULL, 141), (NULL,  11), (NULL, 143);
+ERROR 23000: Duplicate entry '11' for key 'c2'
+SET insert_id= 184;
+INSERT IGNORE INTO t1 VALUES(NULL, 151), (NULL, 152), (NULL, 153);
+INSERT IGNORE INTO t1 VALUES(NULL, 161), (189,  162), (NULL, 163);
+INSERT IGNORE INTO t1 VALUES(NULL,  11), (NULL, 172), (NULL, 173);
+INSERT IGNORE INTO t1 VALUES(NULL, 181), (NULL,  11), (NULL, 183);
+SET insert_id= 44;
+INSERT IGNORE INTO t1 VALUES(NULL, 191), (NULL, 192), (NULL, 193);
 SELECT * FROM t1;
 c1	c2
 14	11
@@ -174,17 +191,27 @@ c1	c2
 164	83
 169	92
 174	93
+179	121
+184	141
+189	161
+194	163
+199	172
+204	173
+209	181
+214	183
+44	191
 SELECT COUNT(*) FROM t1;
 COUNT(*)
-26
+35
 SELECT SUM(c1) FROM t1;
 SUM(c1)
-2569
+4185
 DROP TABLE t1;
 CREATE TABLE t1 (
 c1 INT(11) NOT NULL AUTO_INCREMENT,
 c2 INT(11) DEFAULT NULL,
-PRIMARY KEY (c1)
+PRIMARY KEY (c1),
+UNIQUE INDEX (c2)
 );
 SET insert_id= 14;
 INSERT DELAYED INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13);
@@ -198,7 +225,19 @@ INSERT DELAYED INTO t1 VALUES(  49, 71),
 INSERT DELAYED INTO t1 VALUES(NULL, 81), (NULL, 82), (NULL, 83);
 SET insert_id= 114;
 INSERT DELAYED INTO t1 VALUES(NULL, 91);
-INSERT DELAYED INTO t1 VALUES           (NULL, 92), (NULL, 93);
+INSERT DELAYED INTO t1 VALUES            (NULL, 92), (NULL, 93);
+SET insert_id= 174;
+INSERT DELAYED INTO t1 VALUES(NULL, 111), (NULL, 112), (NULL, 113);
+INSERT DELAYED INTO t1 VALUES(NULL, 121), (174,  122), (NULL, 123);
+INSERT DELAYED INTO t1 VALUES(NULL,  11), (NULL, 132), (NULL, 133);
+INSERT DELAYED INTO t1 VALUES(NULL, 141), (NULL,  11), (NULL, 143);
+SET insert_id= 184;
+INSERT DELAYED IGNORE INTO t1 VALUES(NULL, 151), (NULL, 152), (NULL, 153);
+INSERT DELAYED IGNORE INTO t1 VALUES(NULL, 161), (189,  162), (NULL, 163);
+INSERT DELAYED IGNORE INTO t1 VALUES(NULL,  11), (NULL, 172), (NULL, 173);
+INSERT DELAYED IGNORE INTO t1 VALUES(NULL, 181), (NULL,  11), (NULL, 183);
+SET insert_id= 44;
+INSERT DELAYED IGNORE INTO t1 VALUES(NULL, 191), (NULL, 192), (NULL, 193);
 FLUSH TABLE t1;
 SELECT * FROM t1;
 c1	c2
@@ -228,12 +267,21 @@ c1	c2
 164	83
 169	92
 174	93
+179	121
+184	141
+189	161
+194	163
+199	172
+204	173
+209	181
+214	183
+44	191
 SELECT COUNT(*) FROM t1;
 COUNT(*)
-26
+35
 SELECT SUM(c1) FROM t1;
 SUM(c1)
-2569
+4185
 DROP TABLE t1;
 SET             @@auto_increment_offset=
 @bug20830_old_auto_increment_offset;

--- 1.15/mysql-test/t/delayed.test	2006-09-26 17:43:13 +02:00
+++ 1.16/mysql-test/t/delayed.test	2006-09-26 17:43:13 +02:00
@@ -168,7 +168,8 @@ SET @@session.auto_increment_increment= 
 CREATE TABLE t1 (
   c1 INT(11) NOT NULL AUTO_INCREMENT,
   c2 INT(11) DEFAULT NULL,
-  PRIMARY KEY (c1)
+  PRIMARY KEY (c1),
+  UNIQUE INDEX (c2)
   );
 SET insert_id= 14;
 INSERT INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13);
@@ -187,7 +188,35 @@ INSERT INTO t1 VALUES(NULL, 81), (NULL, 
 SET insert_id= 114;
 --error 1062
 INSERT INTO t1 VALUES(NULL, 91);
-INSERT INTO t1 VALUES           (NULL, 92), (NULL, 93);
+INSERT INTO t1 VALUES            (NULL, 92), (NULL, 93);
+#
+# Create a duplicate value at begin of statement.
+SET insert_id= 174;
+--error 1062
+INSERT INTO t1 VALUES(NULL, 111), (NULL, 112), (NULL, 113);
+# Create a duplicate value in middle of statement.
+--error 1062
+INSERT INTO t1 VALUES(NULL, 121), (174,  122), (NULL, 123);
+# Create a duplicate value in non-auto index at begin of statement.
+--error 1062
+INSERT INTO t1 VALUES(NULL,  11), (NULL, 132), (NULL, 133);
+# Create a duplicate value in non-auto index in middle of statement.
+--error 1062
+INSERT INTO t1 VALUES(NULL, 141), (NULL,  11), (NULL, 143);
+#
+# Ignore a duplicate value at begin of statement.
+SET insert_id= 184;
+INSERT IGNORE INTO t1 VALUES(NULL, 151), (NULL, 152), (NULL, 153);
+# Ignore a duplicate value in middle of statement.
+INSERT IGNORE INTO t1 VALUES(NULL, 161), (189,  162), (NULL, 163);
+# Ignore a duplicate value in non-auto index at begin of statement.
+INSERT IGNORE INTO t1 VALUES(NULL,  11), (NULL, 172), (NULL, 173);
+# Ignore a duplicate value in non-auto index in middle of statement.
+INSERT IGNORE INTO t1 VALUES(NULL, 181), (NULL,  11), (NULL, 183);
+# Ignore a duplicate value in automatic sequence in middle of statement.
+SET insert_id= 44;
+INSERT IGNORE INTO t1 VALUES(NULL, 191), (NULL, 192), (NULL, 193);
+#
 # Check what we have now
 SELECT * FROM t1;
 SELECT COUNT(*) FROM t1;
@@ -198,7 +227,8 @@ DROP TABLE t1;
 CREATE TABLE t1 (
   c1 INT(11) NOT NULL AUTO_INCREMENT,
   c2 INT(11) DEFAULT NULL,
-  PRIMARY KEY (c1)
+  PRIMARY KEY (c1),
+  UNIQUE INDEX (c2)
   );
 SET insert_id= 14;
 INSERT DELAYED INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13);
@@ -216,7 +246,31 @@ INSERT DELAYED INTO t1 VALUES(NULL, 81),
 # Create a duplicate value.
 SET insert_id= 114;
 INSERT DELAYED INTO t1 VALUES(NULL, 91);
-INSERT DELAYED INTO t1 VALUES           (NULL, 92), (NULL, 93);
+INSERT DELAYED INTO t1 VALUES            (NULL, 92), (NULL, 93);
+#
+# Create a duplicate value at begin of statement.
+SET insert_id= 174;
+INSERT DELAYED INTO t1 VALUES(NULL, 111), (NULL, 112), (NULL, 113);
+# Create a duplicate value in middle of statement.
+INSERT DELAYED INTO t1 VALUES(NULL, 121), (174,  122), (NULL, 123);
+# Create a duplicate value in non-auto index at begin of statement.
+INSERT DELAYED INTO t1 VALUES(NULL,  11), (NULL, 132), (NULL, 133);
+# Create a duplicate value in non-auto index in middle of statement.
+INSERT DELAYED INTO t1 VALUES(NULL, 141), (NULL,  11), (NULL, 143);
+#
+# Ignore a duplicate value at begin of statement.
+SET insert_id= 184;
+INSERT DELAYED IGNORE INTO t1 VALUES(NULL, 151), (NULL, 152), (NULL, 153);
+# Ignore a duplicate value in middle of statement.
+INSERT DELAYED IGNORE INTO t1 VALUES(NULL, 161), (189,  162), (NULL, 163);
+# Ignore a duplicate value in non-auto index at begin of statement.
+INSERT DELAYED IGNORE INTO t1 VALUES(NULL,  11), (NULL, 172), (NULL, 173);
+# Ignore a duplicate value in non-auto index in middle of statement.
+INSERT DELAYED IGNORE INTO t1 VALUES(NULL, 181), (NULL,  11), (NULL, 183);
+# Ignore a duplicate value in automatic sequence in middle of statement.
+SET insert_id= 44;
+INSERT DELAYED IGNORE INTO t1 VALUES(NULL, 191), (NULL, 192), (NULL, 193);
+#
 # Wait until the rows are flushed to the table files.
 FLUSH TABLE t1;
 # Check what we have now