bk commit into 5.0 tree (istruewing:1.2246) BUG#20627

[ingo]

Below is the list of changes that have just been committed into a local
5.0 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-15 19:43:42+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
  
  INSERT DELAYED ignored an explicitly set INSERT_ID and session
  specific auto_increment_* variables.
  
  The problem was that the inserts are done by a system thread,
  which does not have access to the session variables of the user
  thread.
  
  On a proposal of Guilhem I fixed it so that the variables are
  copied to the data structure for every delayed row. The system
  thread sets its session variables from these values.

  mysql-test/r/delayed.result@stripped, 2006-09-15 19:43:40+02:00, istruewing@stripped
+142 -0
    Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
    Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
    Added test cases.

  mysql-test/t/delayed.test@stripped, 2006-09-15 19:43:40+02:00, istruewing@stripped +108
-6
    Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
    Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
    Added test cases.

  sql/sql_insert.cc@stripped, 2006-09-15 19:43:40+02:00, istruewing@stripped +31 -0
    Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
    Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
    Added auto_increment/insert_id related variables to 'delayed_row'.
    The session values are copied to 'delayed_row' by the user thread.
    The delayed insert thread copies them to his session variables.

# 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.0-bug20627

--- 1.198/sql/sql_insert.cc	2006-09-15 19:43:47 +02:00
+++ 1.199/sql/sql_insert.cc	2006-09-15 19:43:47 +02:00
@@ -1274,6 +1274,9 @@ public:
   time_t start_time;
   bool query_start_used,last_insert_id_used,insert_id_used, ignore, log_query;
   ulonglong last_insert_id;
+  ulonglong next_insert_id;
+  ulong auto_increment_increment;
+  ulong auto_increment_offset;
   timestamp_auto_set_type timestamp_field_type;
   uint query_length;
 
@@ -1655,6 +1658,16 @@ static int write_delayed(THD *thd,TABLE 
   row->last_insert_id=		thd->last_insert_id;
   row->timestamp_field_type=    table->timestamp_field_type;
 
+  /* The session variable settings can always be copied. */
+  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.
+  */
+  row->next_insert_id= thd->next_insert_id;
+  thd->next_insert_id= 0;
+
   di->rows.push_back(row);
   di->stacked_inserts++;
   di->status=1;
@@ -2026,6 +2039,14 @@ bool delayed_insert::handle_inserts(void
     thd.insert_id_used=row->insert_id_used;
     table->timestamp_field_type= row->timestamp_field_type;
 
+    /* The session variable settings can always be copied. */
+    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));
+
     info.ignore= row->ignore;
     info.handle_duplicates= row->dup;
     if (info.ignore ||
@@ -2047,6 +2068,11 @@ bool delayed_insert::handle_inserts(void
       info.error_count++;				// Ignore errors
       thread_safe_increment(delayed_insert_errors,&LOCK_delayed_status);
       row->log_query = 0;
+      /*
+        We must reset this. Otherwise all following rows may become
+        duplicates.
+      */
+      thd.next_insert_id= 0;
     }
     if (using_ignore)
     {
@@ -2092,6 +2118,7 @@ bool delayed_insert::handle_inserts(void
 	  /* This should never happen */
 	  table->file->print_error(error,MYF(0));
 	  sql_print_error("%s",thd.net.last_error);
+          DBUG_PRINT("error", ("HA_EXTRA_NO_CACHE failed in loop"));
 	  goto err;
 	}
 	query_cache_invalidate3(&thd, table, 1);
@@ -2117,6 +2144,7 @@ bool delayed_insert::handle_inserts(void
   {						// This shouldn't happen
     table->file->print_error(error,MYF(0));
     sql_print_error("%s",thd.net.last_error);
+    DBUG_PRINT("error", ("HA_EXTRA_NO_CACHE failed after loop"));
     goto err;
   }
   query_cache_invalidate3(&thd, table, 1);
@@ -2124,13 +2152,16 @@ bool delayed_insert::handle_inserts(void
   DBUG_RETURN(0);
 
  err:
+  DBUG_EXECUTE("error", max_rows= 0;);
   /* Remove all not used rows */
   while ((row=rows.get()))
   {
     delete row;
     thread_safe_increment(delayed_insert_errors,&LOCK_delayed_status);
     stacked_inserts--;
+    DBUG_EXECUTE("error", max_rows++;);
   }
+  DBUG_PRINT("error", ("dropped %lu rows after an error", max_rows));
   thread_safe_increment(delayed_insert_errors, &LOCK_delayed_status);
   pthread_mutex_lock(&mutex);
   DBUG_RETURN(1);

--- 1.11/mysql-test/r/delayed.result	2006-09-15 19:43:47 +02:00
+++ 1.12/mysql-test/r/delayed.result	2006-09-15 19:43:47 +02:00
@@ -7,6 +7,7 @@ insert delayed into t1 set a = 4;
 insert delayed into t1 set a = 5, tmsp = 19711006010203;
 insert delayed into t1 (a, tmsp) values (6, 19711006010203);
 insert delayed into t1 (a, tmsp) values (7, NULL);
+FLUSH TABLE t1;
 insert into t1 set a = 8,tmsp=19711006010203;
 select * from t1 where tmsp=0;
 a	tmsp
@@ -22,6 +23,7 @@ insert delayed into t1 values (null,"c")
 insert delayed into t1 values (3,"d"),(null,"e");
 insert delayed into t1 values (3,"this will give an","error");
 ERROR 21S01: Column count doesn't match value count at row 1
+FLUSH TABLE t1;
 show status like 'not_flushed_delayed_rows';
 Variable_name	Value
 Not_flushed_delayed_rows	0
@@ -54,6 +56,7 @@ insert delayed into t1 values(null);
 insert delayed into t1 values(null);
 insert delayed into t1 values(null);
 insert delayed into t1 values(null);
+FLUSH TABLE t1;
 select * from t1 order by a;
 a
 1
@@ -69,3 +72,142 @@ a
 12
 13
 DROP TABLE t1;
+SET @@auto_increment_offset= 2;
+SET @@auto_increment_increment= 3;
+SET @@session.auto_increment_offset= 4;
+SET @@session.auto_increment_increment= 5;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1)
+);
+INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
+SELECT * FROM t1;
+c1
+4
+9
+14
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1)
+);
+INSERT DELAYED INTO t1 VALUES (NULL),(NULL),(NULL);
+FLUSH TABLE t1;
+SELECT * FROM t1;
+c1
+4
+9
+14
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT(11) NOT NULL AUTO_INCREMENT,
+c2 INT(11) DEFAULT NULL,
+PRIMARY KEY (c1)
+);
+SET insert_id= 14;
+INSERT INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13);
+INSERT INTO t1 VALUES(NULL, 21), (NULL, 22), (NULL, 23);
+INSERT INTO t1 VALUES(  69, 31), (NULL, 32), (NULL, 33);
+INSERT INTO t1 VALUES(NULL, 41), (NULL, 42), (NULL, 43);
+SET insert_id= 114;
+INSERT INTO t1 VALUES(NULL, 51), (NULL, 52), (NULL, 53);
+INSERT INTO t1 VALUES(NULL, 61), (NULL, 62), (NULL, 63);
+INSERT INTO t1 VALUES(  49, 71), (NULL, 72), (NULL, 73);
+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);
+SELECT * FROM t1;
+c1	c2
+14	11
+19	12
+24	13
+29	21
+34	22
+39	23
+69	31
+74	32
+79	33
+84	41
+89	42
+94	43
+114	51
+119	52
+124	53
+129	61
+134	62
+139	63
+49	71
+144	72
+149	73
+154	81
+159	82
+164	83
+169	92
+174	93
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+26
+SELECT SUM(c1) FROM t1;
+SUM(c1)
+2569
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT(11) NOT NULL AUTO_INCREMENT,
+c2 INT(11) DEFAULT NULL,
+PRIMARY KEY (c1)
+);
+SET insert_id= 14;
+INSERT DELAYED INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13);
+INSERT DELAYED INTO t1 VALUES(NULL, 21), (NULL, 22), (NULL, 23);
+INSERT DELAYED INTO t1 VALUES(  69, 31), (NULL, 32), (NULL, 33);
+INSERT DELAYED INTO t1 VALUES(NULL, 41), (NULL, 42), (NULL, 43);
+SET insert_id= 114;
+INSERT DELAYED INTO t1 VALUES(NULL, 51), (NULL, 52), (NULL, 53);
+INSERT DELAYED INTO t1 VALUES(NULL, 61), (NULL, 62), (NULL, 63);
+INSERT DELAYED INTO t1 VALUES(  49, 71), (NULL, 72), (NULL, 73);
+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);
+FLUSH TABLE t1;
+SELECT * FROM t1;
+c1	c2
+14	11
+19	12
+24	13
+29	21
+34	22
+39	23
+69	31
+74	32
+79	33
+84	41
+89	42
+94	43
+114	51
+119	52
+124	53
+129	61
+134	62
+139	63
+49	71
+144	72
+149	73
+154	81
+159	82
+164	83
+169	92
+174	93
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+26
+SELECT SUM(c1) FROM t1;
+SUM(c1)
+2569
+DROP TABLE t1;
+SET @@auto_increment_offset= 1;
+SET @@auto_increment_increment= 1;
+SET @@session.auto_increment_offset= 1;
+SET @@session.auto_increment_increment= 1;

--- 1.13/mysql-test/t/delayed.test	2006-09-15 19:43:47 +02:00
+++ 1.14/mysql-test/t/delayed.test	2006-09-15 19:43:47 +02:00
@@ -17,7 +17,8 @@ insert delayed into t1 set a = 4;
 insert delayed into t1 set a = 5, tmsp = 19711006010203;
 insert delayed into t1 (a, tmsp) values (6, 19711006010203);
 insert delayed into t1 (a, tmsp) values (7, NULL);
---sleep 2
+# Wait until the rows are flushed to the table files.
+FLUSH TABLE t1;
 insert into t1 set a = 8,tmsp=19711006010203;
 select * from t1 where tmsp=0;
 select * from t1 where tmsp=19711006010203;
@@ -34,8 +35,8 @@ insert delayed into t1 values (null,"c")
 insert delayed into t1 values (3,"d"),(null,"e");
 --error 1136
 insert delayed into t1 values (3,"this will give an","error");
-# 2 was not enough for --ps-protocol
---sleep 4
+# Wait until the rows are flushed to the table files.
+FLUSH TABLE t1;
 show status like 'not_flushed_delayed_rows';
 select * from t1;
 drop table t1;
@@ -92,10 +93,111 @@ insert delayed into t1 values(null);
 # Works, since the delayed-counter is 8, which is unused
 insert delayed into t1 values(null);
 
+# Wait until the rows are flushed to the table files.
+FLUSH TABLE t1;
 # Check what we have now
-# must wait so that the delayed thread finishes
-# Note: this must be increased if the test fails
---sleep 1
 select * from t1 order by a;
 
 DROP TABLE t1;
+
+#
+# Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
+#
+SET @@auto_increment_offset= 2;
+SET @@auto_increment_increment= 3;
+SET @@session.auto_increment_offset= 4;
+SET @@session.auto_increment_increment= 5;
+#
+# Normal insert as reference.
+CREATE TABLE t1 (
+  c1 INT NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (c1)
+  );
+INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
+# Check what we have now
+SELECT * FROM t1;
+DROP TABLE t1;
+#
+# Delayed insert.
+CREATE TABLE t1 (
+  c1 INT NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (c1)
+  );
+INSERT DELAYED INTO t1 VALUES (NULL),(NULL),(NULL);
+# Wait until the rows are flushed to the table files.
+FLUSH TABLE t1;
+# Check what we have now
+SELECT * FROM t1;
+DROP TABLE t1;
+#
+# Keep the auto_increment_* values for the next test.
+#
+# Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
+#
+# Normal insert as reference.
+CREATE TABLE t1 (
+  c1 INT(11) NOT NULL AUTO_INCREMENT,
+  c2 INT(11) DEFAULT NULL,
+  PRIMARY KEY (c1)
+  );
+SET insert_id= 14;
+INSERT INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13);
+INSERT INTO t1 VALUES(NULL, 21), (NULL, 22), (NULL, 23);
+# Restart sequence at a different value.
+INSERT INTO t1 VALUES(  69, 31), (NULL, 32), (NULL, 33);
+INSERT INTO t1 VALUES(NULL, 41), (NULL, 42), (NULL, 43);
+# Restart sequence at a different value.
+SET insert_id= 114;
+INSERT INTO t1 VALUES(NULL, 51), (NULL, 52), (NULL, 53);
+INSERT INTO t1 VALUES(NULL, 61), (NULL, 62), (NULL, 63);
+# Set one value below the maximum value.
+INSERT INTO t1 VALUES(  49, 71), (NULL, 72), (NULL, 73);
+INSERT INTO t1 VALUES(NULL, 81), (NULL, 82), (NULL, 83);
+# Create a duplicate value.
+SET insert_id= 114;
+--error 1062
+INSERT INTO t1 VALUES(NULL, 91);
+INSERT INTO t1 VALUES           (NULL, 92), (NULL, 93);
+# Check what we have now
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT SUM(c1) FROM t1;
+DROP TABLE t1;
+#
+# Delayed insert.
+CREATE TABLE t1 (
+  c1 INT(11) NOT NULL AUTO_INCREMENT,
+  c2 INT(11) DEFAULT NULL,
+  PRIMARY KEY (c1)
+  );
+SET insert_id= 14;
+INSERT DELAYED INTO t1 VALUES(NULL, 11), (NULL, 12), (NULL, 13);
+INSERT DELAYED INTO t1 VALUES(NULL, 21), (NULL, 22), (NULL, 23);
+# Restart sequence at a different value.
+INSERT DELAYED INTO t1 VALUES(  69, 31), (NULL, 32), (NULL, 33);
+INSERT DELAYED INTO t1 VALUES(NULL, 41), (NULL, 42), (NULL, 43);
+# Restart sequence at a different value.
+SET insert_id= 114;
+INSERT DELAYED INTO t1 VALUES(NULL, 51), (NULL, 52), (NULL, 53);
+INSERT DELAYED INTO t1 VALUES(NULL, 61), (NULL, 62), (NULL, 63);
+# Set one value below the maximum value.
+INSERT DELAYED INTO t1 VALUES(  49, 71), (NULL, 72), (NULL, 73);
+INSERT DELAYED INTO t1 VALUES(NULL, 81), (NULL, 82), (NULL, 83);
+# Create a duplicate value.
+SET insert_id= 114;
+INSERT DELAYED INTO t1 VALUES(NULL, 91);
+INSERT DELAYED INTO t1 VALUES           (NULL, 92), (NULL, 93);
+# Wait until the rows are flushed to the table files.
+FLUSH TABLE t1;
+# Check what we have now
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT SUM(c1) FROM t1;
+DROP TABLE t1;
+#
+# Cleanup
+SET @@auto_increment_offset= 1;
+SET @@auto_increment_increment= 1;
+SET @@session.auto_increment_offset= 1;
+SET @@session.auto_increment_increment= 1;
+