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)