Bug #20195: INSERT DELAYED with auto_increment is assigned wrong values

The INSERT DELAYED should not maintain its own private auto-increment
counter, because this is assuming that other threads cannot insert
into the table while the INSERT DELAYED thread is inserting, which is
a wrong assumption.

So the start of processing of a batch of INSERT rows in the 
INSERT DELAYED thread must be treated as a start of a new statement
and cached next_insert_id must be cleared.


mysql-test/r/delayed.result:
  test suite for the bug
mysql-test/t/delayed.test:
  test suite for the bug
sql/sql_insert.cc:
  Reset auto-increment cacheing before processing
  the next batch of inserts in the handler thread

1 files changed, 49 insertions, 0 deletions

diff --git a/mysql-test/t/delayed.test b/mysql-test/t/delayed.test
index 5ae757b1fde..55e8f81f763 100644
--- a/mysql-test/t/delayed.test
+++ b/mysql-test/t/delayed.test
@@ -50,3 +50,52 @@ insert into t1 values (1);
 insert delayed into t1 values (1);
 select * from t1;
 drop table t1;
+
+#
+# Bug #20195: INSERT DELAYED with auto_increment is assigned wrong values
+#
+CREATE TABLE t1 ( a int(10) NOT NULL auto_increment, PRIMARY KEY (a));
+
+# Make one delayed insert to start the separate thread
+insert delayed into t1 values(null);
+
+# Do some normal inserts
+insert into t1 values(null);
+insert into t1 values(null);
+
+# Discarded, since the delayed-counter is 2, which is already used
+insert delayed into t1 values(null);
+
+# Discarded, since the delayed-counter is 3, which is already used
+insert delayed into t1 values(null);
+
+# Works, since the delayed-counter is 4, which is unused
+insert delayed into t1 values(null);
+
+# Do some more inserts
+insert into t1 values(null);
+insert into t1 values(null);
+insert into t1 values(null);
+
+# Delete one of the above to make a hole
+delete from t1 where a=6;
+
+# Discarded, since the delayed-counter is 5, which is already used
+insert delayed into t1 values(null);
+
+# Works, since the delayed-counter is 6, which is unused (the row we deleted)
+insert delayed into t1 values(null);
+
+# Discarded, since the delayed-counter is 7, which is already used
+insert delayed into t1 values(null);
+
+# Works, since the delayed-counter is 8, which is unused
+insert delayed into t1 values(null);
+
+# 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;