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:
  Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
  Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
  Turned some sleeps into FLUSH TABLEs.
  Added test cases.
mysql-test/t/delayed.test:
  Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
  Bug#20627 - INSERT DELAYED does not honour auto_increment_* variables
  Turned some sleeps into FLUSH TABLEs.
  Added test cases.
sql/sql_insert.cc:
  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.

3 files changed, 362 insertions, 6 deletions

diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result
index a336f3b4108..6295fceec2b 100644
--- a/mysql-test/r/delayed.result
+++ b/mysql-test/r/delayed.result
@@ -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,174 @@ a
 12
 13
 DROP TABLE t1;
+SET @bug20627_old_auto_increment_offset=
+@@auto_increment_offset= 2;
+SET @bug20627_old_auto_increment_increment=
+@@auto_increment_increment= 3;
+SET @bug20627_old_session_auto_increment_offset=
+@@session.auto_increment_offset= 4;
+SET @bug20627_old_session_auto_increment_increment=
+@@session.auto_increment_increment= 5;
+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;
+SET             @@auto_increment_offset=
+@bug20627_old_auto_increment_offset;
+SET             @@auto_increment_increment=
+@bug20627_old_auto_increment_increment;
+SET             @@session.auto_increment_offset=
+@bug20627_old_session_auto_increment_offset;
+SET             @@session.auto_increment_increment=
+@bug20627_old_session_auto_increment_increment;
+SET @bug20830_old_auto_increment_offset=
+@@auto_increment_offset= 2;
+SET @bug20830_old_auto_increment_increment=
+@@auto_increment_increment= 3;
+SET @bug20830_old_session_auto_increment_offset=
+@@session.auto_increment_offset= 4;
+SET @bug20830_old_session_auto_increment_increment=
+@@session.auto_increment_increment= 5;
+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(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=
+@bug20830_old_auto_increment_offset;
+SET             @@auto_increment_increment=
+@bug20830_old_auto_increment_increment;
+SET             @@session.auto_increment_offset=
+@bug20830_old_session_auto_increment_offset;
+SET             @@session.auto_increment_increment=
+@bug20830_old_session_auto_increment_increment;
diff --git a/mysql-test/t/delayed.test b/mysql-test/t/delayed.test
index 55e8f81f763..03d8e20dd8f 100644
--- a/mysql-test/t/delayed.test
+++ b/mysql-test/t/delayed.test
@@ -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,145 @@ 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 @bug20627_old_auto_increment_offset=
+                @@auto_increment_offset= 2;
+SET @bug20627_old_auto_increment_increment=
+                @@auto_increment_increment= 3;
+SET @bug20627_old_session_auto_increment_offset=
+                @@session.auto_increment_offset= 4;
+SET @bug20627_old_session_auto_increment_increment=
+                @@session.auto_increment_increment= 5;
+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;
+#
+# Cleanup
+SET             @@auto_increment_offset=
+    @bug20627_old_auto_increment_offset;
+SET             @@auto_increment_increment=
+    @bug20627_old_auto_increment_increment;
+SET             @@session.auto_increment_offset=
+    @bug20627_old_session_auto_increment_offset;
+SET             @@session.auto_increment_increment=
+    @bug20627_old_session_auto_increment_increment;
+
+#
+# Bug#20830 - INSERT DELAYED does not honour SET INSERT_ID
+#
+SET @bug20830_old_auto_increment_offset=
+                @@auto_increment_offset= 2;
+SET @bug20830_old_auto_increment_increment=
+                @@auto_increment_increment= 3;
+SET @bug20830_old_session_auto_increment_offset=
+                @@session.auto_increment_offset= 4;
+SET @bug20830_old_session_auto_increment_increment=
+                @@session.auto_increment_increment= 5;
+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(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=
+    @bug20830_old_auto_increment_offset;
+SET             @@auto_increment_increment=
+    @bug20830_old_auto_increment_increment;
+SET             @@session.auto_increment_offset=
+    @bug20830_old_session_auto_increment_offset;
+SET             @@session.auto_increment_increment=
+    @bug20830_old_session_auto_increment_increment;
+
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index eaa7d3a72db..e3aecbaace2 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -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,22 @@ static int write_delayed(THD *thd,TABLE *table,enum_duplicates duplic, bool igno
   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. 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;
+
   di->rows.push_back(row);
   di->stacked_inserts++;
   di->status=1;
@@ -2026,6 +2045,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 +2074,20 @@ 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 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.
+      */
+      thd.next_insert_id= 0;
     }
     if (using_ignore)
     {
@@ -2092,6 +2133,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 +2159,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 +2167,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);