Bug #22820 INSERT DELAYED does not behave like normal insert
Submitted: 29 Sep 2006 11:49 Modified: 7 May 2008 9:32
Reporter: Ingo Strüwing Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.1.12 OS:Any (*)
Assigned to: Assigned Account CPU Architecture:Any

[29 Sep 2006 11:49] Ingo Strüwing
Description:
INSERT DELAYED behaves like INSERT DELAYED IGNORE. That is, it ignores insert errors and tries to insert as much as possible.

The below test result requires the fix for Bug#20627+Bug#20830. Otherwise the result would be even more different.

-------------------------------------------------------
*** r/bug-new.result    2006-09-29 10:42:20.000000000 +0300
--- r/bug-new.reject    2006-09-29 14:11:39.000000000 +0300
***************
*** 80,86 ****
  c1    c2
  100   100
  101   121
! 102   141
  200   200
  199   211
  300   300
--- 80,90 ----
  c1    c2
  100   100
  101   121
! 102   123
! 103   132
! 104   133
! 105   141
! 106   143
  200   200
  199   211
  300   300
***************
*** 94,101 ****
  399   411
  SELECT COUNT(*) FROM t1;
  COUNT(*)
! 14
  SELECT SUM(c1), SUM(c2) FROM t1;
  SUM(c1)       SUM(c2)
! 3622  3877
  DROP TABLE t1;
--- 98,105 ----
  399   411
  SELECT COUNT(*) FROM t1;
  COUNT(*)
! 18
  SELECT SUM(c1), SUM(c2) FROM t1;
  SUM(c1)       SUM(c2)
! 4040  4408
  DROP TABLE t1;
-------------------------------------------------------

How to repeat:
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

#
# Bug# - INSERT DELAYED does not behave like normal insert
#
#
# Normal insert as reference.
CREATE TABLE t1 (
  c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 INT(11) DEFAULT NULL,
  PRIMARY KEY (c1),
  UNIQUE INDEX (c2)
  );
INSERT INTO t1 VALUES(100, 100);
#
# Create a duplicate value at begin of statement.
SET insert_id= 100;
--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), (100,  122), (NULL, 123);
# Create a duplicate value in non-auto index at begin of statement.
--error 1062
INSERT INTO t1 VALUES(NULL, 100), (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, 100), (NULL, 143);
# Create a duplicate value in automatic sequence in middle of statement.
INSERT INTO t1 VALUES(200, 200);
SET insert_id= 199;
--error 1062
INSERT INTO t1 VALUES(NULL, 211), (NULL, 212), (NULL, 213);
#
# Ignore a duplicate value at begin of statement.
INSERT IGNORE INTO t1 VALUES(300, 300);
SET insert_id= 300;
INSERT IGNORE INTO t1 VALUES(NULL, 311), (NULL, 312), (NULL, 313);
# Ignore a duplicate value in middle of statement.
INSERT IGNORE INTO t1 VALUES(NULL, 321), (300,  322), (NULL, 323);
# Ignore a duplicate value in non-auto index at begin of statement.
INSERT IGNORE INTO t1 VALUES(NULL, 300), (NULL, 332), (NULL, 333);
# Ignore a duplicate value in non-auto index in middle of statement.
INSERT IGNORE INTO t1 VALUES(NULL, 341), (NULL, 300), (NULL, 343);
# Ignore a duplicate value in automatic sequence in middle of statement.
INSERT IGNORE INTO t1 VALUES(400, 400);
SET insert_id= 399;
INSERT IGNORE INTO t1 VALUES(NULL, 411), (NULL, 412), (NULL, 413);
#
# Check what we have now
SELECT * FROM t1;
SELECT COUNT(*) FROM t1;
SELECT SUM(c1), SUM(c2) 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),
  UNIQUE INDEX (c2)
  );
INSERT DELAYED INTO t1 VALUES(100, 100);
#
# Create a duplicate value at begin of statement.
SET insert_id= 100;
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), (100,  122), (NULL, 123);
# Create a duplicate value in non-auto index at begin of statement.
INSERT DELAYED INTO t1 VALUES(NULL, 100), (NULL, 132), (NULL, 133);
# Create a duplicate value in non-auto index in middle of statement.
INSERT DELAYED INTO t1 VALUES(NULL, 141), (NULL, 100), (NULL, 143);
# Create a duplicate value in automatic sequence in middle of statement.
INSERT DELAYED INTO t1 VALUES(200, 200);
SET insert_id= 199;
INSERT DELAYED INTO t1 VALUES(NULL, 211), (NULL, 212), (NULL, 213);
#
# Ignore a duplicate value at begin of statement.
INSERT DELAYED IGNORE INTO t1 VALUES(300, 300);
SET insert_id= 300;
INSERT DELAYED IGNORE INTO t1 VALUES(NULL, 311), (NULL, 312), (NULL, 313);
# Ignore a duplicate value in middle of statement.
INSERT DELAYED IGNORE INTO t1 VALUES(NULL, 321), (300,  322), (NULL, 323);
# Ignore a duplicate value in non-auto index at begin of statement.
INSERT DELAYED IGNORE INTO t1 VALUES(NULL, 300), (NULL, 332), (NULL, 333);
# Ignore a duplicate value in non-auto index in middle of statement.
INSERT DELAYED IGNORE INTO t1 VALUES(NULL, 341), (NULL, 300), (NULL, 343);
# Ignore a duplicate value in automatic sequence in middle of statement.
INSERT DELAYED IGNORE INTO t1 VALUES(400, 400);
SET insert_id= 399;
INSERT DELAYED IGNORE INTO t1 VALUES(NULL, 411), (NULL, 412), (NULL, 413);
#
# 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), SUM(c2) FROM t1;
DROP TABLE t1;

Suggested fix:
I made a proposal in conjunction with Bug#20627+Bug#20830. See http://lists.mysql.com/commits/12548. But it was rejected as not belonging to these bugs. The suggested fix is:

===== sql/sql_insert.cc 1.226 vs edited =====
--- 1.226/sql/sql_insert.cc     2006-09-29 13:46:48 +02:00
+++ edited/sql/sql_insert.cc    2006-09-29 13:45:29 +02:00
@@ -1362,6 +1362,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;
@@ -1370,7 +1371,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;
@@ -2156,6 +2157,27 @@ bool delayed_insert::handle_inserts(void
       */
       table->file->ha_release_auto_increment();
       thd.auto_inc_intervals_in_cur_stmt_for_binlog.empty();
+      /*
+        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;
     }
     thd.first_successful_insert_id_in_prev_stmt= 
       row->first_successful_insert_id_in_prev_stmt;
@@ -2195,6 +2217,13 @@ bool delayed_insert::handle_inserts(void
       info.error_count++;                              // Ignore errors
       thread_safe_increment(delayed_insert_errors,&LOCK_delayed_status);
       row->log_query = 0;
+      /*
+        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().
+       */
+      error_in_statement= TRUE;
     }
 
     if (using_ignore)
[11 Oct 2006 7:41] Sergei Golubchik
I think it'd be too big incompatible change, that would silently break many existing applications that use INSERT DELAYED
[10 Nov 2006 0:03] Konstantin Osipov
On the other hand, we downgrade silently INSERT DELAYED to a normal insert if the table, e.g., uses views. So there is an implicit assumption that INSERT DELAYED works the same way as normal insert in 5.0.
One of those problems has to be fixed.
[2 Feb 2008 9:32] Konstantin Osipov
Ingo,
INSERT DELAYED <tmp table> currently always picks the base table if there is one.
So we're going to fix this by downgrading DELAYED to a normal insert if there is a temporary table.
It seems to be a common opinion that DELAYED just an optimization tag and we should ignore it if we can't support it.
[2 Feb 2008 9:32] Konstantin Osipov
The above was JFYI.
[18 Feb 2008 16:31] Ingo Strüwing
Requested feedback from the architecture board.
[25 Feb 2008 17:33] Ingo Strüwing
On request of Lars, I copy here the question to the architects:

Hi architects,

due to different opinions in the bug report
http://bugs.mysql.com/bug.php?id=22820 I ask for a decision.

INSERT DELAYED behaves like INSERT IGNORE.

Sergei said that changing the behavior of INSERT DELAYED would silently
break many existing applications that use INSERT DELAYED.

Konstantin said we downgrade silently INSERT DELAYED to a normal insert
if the table, e.g., uses views. So there is an implicit assumption that
INSERT DELAYED works the same way as normal insert. If I understand
correctly, he suggests either to make DELAYED compatible, or not to
downgrade in all the cases we do today.

My opinion is that most bug fixes change behavior. The respective
question is: is the original behavior a bug or a feature. We fixed
DELAYED to respect auto_increment_increment, auto_increment_offset, and
SET INSERT_ID. This was also likely to break applications that counted
on this misbehavior.

Statement based binlogging downgrades INSERT DELAYED to normal insert
too and thus changes the behavior.

None of the mentioned differences in behavior is documented.

My proposal is still to fix INSERT DELAYED to respect the IGNORE flag,
or its absence respectively.

Regards
Ingo
[5 Apr 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[7 May 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[8 May 2008 8:13] Konstantin Osipov
To clarify my point, I suggest we don't fix this bug.