MySQL Bugs: #27954: INSERT ... ON DUPLICATE KEY UPDATE fails with large multi-insert

Bug #27954	INSERT ... ON DUPLICATE KEY UPDATE fails with large multi-insert
Submitted:	19 Apr 2007 14:23	Modified:	10 May 2007 18:07
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.0.38	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any
Tags:	insert, ON DUPLICATE KEY UPDATE, regression

Description:
INSERT ... ON DUPLICATE KEY UPDATE fails when running against a large dataset, with a large multi-value INSERT statement. 

With the attached test case you case, that does:

start transaction;

select count(*) from test.t1;
select * from test.t1 where l='FOO-0146214';

INSERT ... <large list of rows including one with above value for l which did not exist previously> ON DUPLICATE KEY UPDATE k=VALUES(k), m=VALUES(m);

select count(*) from test.t1;
select * from test.t1 where l='FOO-0146214';

You see:

Query OK, 0 rows affected (0.00 sec)

+----------+
| count(*) |
+----------+
|   161397 |
+----------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

Query OK, 4696 rows affected (0.18 sec)
Records: 2348  Duplicates: 2348  Warnings: 0

+----------+
| count(*) |
+----------+
|   161397 |
+----------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

No rows are INSERTed, and no rows are UPDATEd. The row that is being selected within the test case at the start and end *is* inserted towards the end of the INSERT:

"(NULL,455362,'1.23','FOO-0146214','1.23'),"

How to repeat:
Run attached create and test SQL scripts

Test case uploaded here:

ftp://ftp.mysql.com/pub/mysql/upload/bug27954.zip

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26102

ChangeSet@1.2469, 2007-05-04 19:30:39+05:00, gshchepa@gleb.loc +3 -0
  Fixed bug #27954.
  This bug affects multi-row INSERT ... ON DUPLICATE into table
  with PRIMARY KEY of AUTO_INCREMENT field and some additional UNIQUE indices.
  If the first row in multi-row INSERT contains duplicated values of UNIQUE
  indices, then following rows of multi-row INSERT (with either duplicated or
  unique key field values) may me applied to _arbitrary_ records of table as
  updates.
  This bug was introduced in 5.0. Related code was widely rewritten in 5.1, and
  5.1 is free of this problem. 4.1 was not affected too.
  
  The value of last updated record AUTO_INCREMENT field was used to calculate
  next_insert_id (even if it was insertion of NULL into that field).
  handler::adjust_next_insert_id_after_explicit_value() function is fixed.

comments sent by mail

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26241

ChangeSet@1.2469, 2007-05-08 00:24:25+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #27954.
  This bug affects multi-row INSERT ... ON DUPLICATE into table
  with PRIMARY KEY of AUTO_INCREMENT field and some additional UNIQUE indices.
  If the first row in multi-row INSERT contains duplicated values of UNIQUE
  indices, then following rows of multi-row INSERT (with either duplicated or
  unique key field values) may me applied to _arbitrary_ records of table as
  updates.
  This bug was introduced in 5.0. Related code was widely rewritten in 5.1, and
  5.1 is already free of this problem. 4.1 was not affected too.
  
  When updating the row during INSERT ON DUPLICATE KEY UPDATE, we called
  restore_auto_increment(), which set next_insert_id back to 0, but we
  forgot to set clear_next_insert_id back to 0.
  restore_auto_increment() function has been fixed.

Pushed into 5.1.19-beta

Pushed into 5.0.42

Noted in 5.0.42 changelog.

For INSERT ... ON DUPLICATE KEY UPDATE statements that affected many
rows, updates could be applied to the wrong rows.

Here is a simple sql sequence that demonstrates the bug:

DROP TABLE IF EXISTS test1;

CREATE TABLE test1 (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
uid INTEGER,
t INTEGER
);
CREATE UNIQUE INDEX index_1 ON test1 (uid);

INSERT INTO test1 (uid,t) VALUES
(10001, 1),
(10002, 2),
(10003, 3),
(10004, 4),
(10005, 5),
(10006, 6),
(10007, 7)
;

INSERT INTO test1 (uid,t) VALUES
(10003,103),
(10006,106),
(10007,107)
ON DUPLICATE KEY
UPDATE t=VALUES(t);

mysql> SELECT * FROM test1;
+----+-------+------+
| id | uid   | t    |
+----+-------+------+
|  1 | 10001 |    1 |
|  2 | 10002 |    2 |
|  3 | 10003 |  103 |
|  4 | 10004 |  106 | <<< OOOPS!
|  5 | 10005 |  107 | <<<
|  6 | 10006 |    6 |
|  7 | 10007 |    7 |
+----+-------+------+