Bug #27650 ON DUPLICATE KEY UPDATE fails when setting UNIQUE values with multi row inserts
Submitted: 4 Apr 2007 13:24 Modified: 1 May 2007 17:45
Reporter: Mark Leith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.38, 5.x OS:Any
Assigned to: Gleb Shchepa
Tags: bfsm_2007_04_19, ON DUPLICATE KEY UPDATE

[4 Apr 2007 13:24] Mark Leith
Description:
INSERT ... ON DUPLICATE KEY UPDATE now fails, when using multi-row INSERT statements, and setting the values of the UNIQUE KEY within the ON DUPLICATE KEY UPDATE statement back to themselves.

i.e with the following table:

create table `t1` (
  `c1` int auto_increment, 
  `c2` int unsigned not null, 
  `c3` varchar(100) not null, 
  counter int not null default 1, 
  primary key (c1), 
  unique key (`c2`, `c3`)
) engine = innodb

If we use c2 and c3 within the ON DUPLICATE KEY UPDATE clause, we still get duplicate key errors - when we should not. Such as a query like:

insert into t1 (c1, c2, c3) values 
  (NULL, ?, '?'), 
  (NULL, ?, '?'), 
  (NULL, ?, '?') 
  on duplicate key update 
  c1 = values(c1), 
  c2 = values(c2), 
  counter = counter + 1

Attaching a C test case that reproduces this fairly quickly:

[INFO]   14:15:05 [0219]  3688 - testing DB connection before spawning threads
[ALWAYS] 14:15:05 [0246]  3688 - connected to server: '5.0.38-enterprise-gpl-nt', host: '127.0.0.1 via TCP/IP',  protocol: 10
[INFO]   14:15:05 [0247]  3688 - client library version: 5.1.6-alpha, default charset: latin1
[INFO]   14:15:05 [0281]  3688 - read 3 valid queries from query file
[INFO]   14:15:05 [0332]  3688 - spawning initialization thread
[INFO]   14:15:05 [0365]  3688 - report will be in file 'report_6100.txt'
[INFO]   14:15:05 [0366]  3688 - spawning database stats thread
[ALWAYS] 14:15:05 [0380]  3688 - spawning 25 new thread(s)
[INFO]   14:15:05 [0395]  3688 - completed spawning new database worker threads
[INFO]   14:15:05 [0403]  3688 - now running for 180 seconds.
[INFO]   14:15:05 [0427]  3688 - 25 threads running, 0000000 successful queries.  0000000 failed queries (0.000000 QPS).
[ERROR]  14:15:33 [2269]  3960 - query failed (1062) - Duplicate entry '100-serrulate' for key 2: insert into t1 (c1, c2, c3) val [...]
[ERROR]  14:15:34 [2269]   984 - query failed (1062) - Duplicate entry '100-serrulate' for key 2: insert into t1 (c1, c2, c3) val [...]

This appears to be a regression.

How to repeat:
Build and run the attached C test case
[4 Apr 2007 13:31] Mark Leith
Testcase

Attachment: bug17650_testcase.c (text/plain), 4.61 KiB.

[4 Apr 2007 13:38] Mark Leith
Second test case, generated word "heartbeat's" seems a problem

Attachment: bug17650_testcase.c (text/plain), 4.60 KiB.

[4 Apr 2007 14:08] Mark Leith
Hmm.. issues with C testcase. Simplified:

drop table if exists `t1`;
create table `t1` (`c1` int auto_increment, `c2` int unsigned not null, `c3` varchar(100) not null, counter int not null default 1, primary key (c1), unique key (`c2`, `c3`)) engine = innodb;
insert into t1 (c1, c2, c3) values (NULL, 0, 'heartbeat') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter + 1;
insert into t1 (c1, c2, c3) values (NULL, 6, 'heartbeat') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter + 1;
insert into t1 (c1, c2, c3) values (NULL, 6, 'heartbeat') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter + 1;
insert into t1 (c1, c2, c3) values (NULL, 5, 'Tewell'), (NULL, 1, 'heartbeat') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter + 1;
insert into t1 (c1, c2, c3) values (NULL, 2, 'cohosting'), (NULL, 4, 'heartbeat') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter + 1;
insert into t1 (c1, c2, c3) values (NULL, 4, 'cohosting'), (NULL, 4, 'heartbeat') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter + 1;
insert into t1 (c1, c2, c3) values (NULL, 5, 'Tewell') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter + 1;

select * from t1;

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.11 sec)

mysql> create table `t1` (`c1` int auto_increment, `c2` int unsigned not null, `c3` varchar(100) not null, counter int not null default 1, p
rimary key (c1), unique key (`c2`, `c3`)) engine = innodb;
Query OK, 0 rows affected (0.23 sec)

mysql> insert into t1 (c1, c2, c3) values (NULL, 0, 'heartbeat') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter
 + 1;
Query OK, 1 row affected (0.07 sec)

mysql> insert into t1 (c1, c2, c3) values (NULL, 6, 'heartbeat') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter
 + 1;
Query OK, 1 row affected (0.03 sec)

mysql> insert into t1 (c1, c2, c3) values (NULL, 6, 'heartbeat') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter
 + 1;
Query OK, 2 rows affected (0.02 sec)

mysql> insert into t1 (c1, c2, c3) values (NULL, 5, 'Tewell'), (NULL, 1, 'heartbeat') on duplicate key update c1 = values(c1), c2 = values(c
2), counter = counter + 1;
Query OK, 3 rows affected (0.03 sec)
Records: 2  Duplicates: 1  Warnings: 0

mysql> insert into t1 (c1, c2, c3) values (NULL, 2, 'cohosting'), (NULL, 4, 'heartbeat') on duplicate key update c1 = values(c1), c2 = value
s(c2), counter = counter + 1;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t1 (c1, c2, c3) values (NULL, 4, 'cohosting'), (NULL, 4, 'heartbeat') on duplicate key update c1 = values(c1), c2 = value
s(c2), counter = counter + 1;
Query OK, 3 rows affected (0.02 sec)
Records: 2  Duplicates: 1  Warnings: 0

mysql> insert into t1 (c1, c2, c3) values (NULL, 5, 'Tewell') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter +
1;
ERROR 1062 (23000): Duplicate entry '5-heartbeat' for key 2
mysql>
mysql> select * from t1;
[4 Apr 2007 14:23] Shane Bester
When `t1` is InnoDB, the duplicate key occurs.  When `t1` is MyISAM, no error occurs.

drop table if exists `t1`;
create table `t1` (`c1` int auto_increment, `c2` int unsigned not null, `c3` varchar(100) not null, counter int not null default 1, primary key (c1), unique key (`c2`, `c3`)) engine = innodb;
insert into t1 (c1, c2, c3) values (NULL, 1, 'a') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter + 1;
insert into t1 (c1, c2, c3) values (NULL, 2, 'a') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter + 1;
insert into t1 (c1, c2, c3) values (NULL, 1, 'a') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter + 1;
insert into t1 (c1, c2, c3) values (NULL, 2, 'a') on duplicate key update c1 = values(c1), c2 = values(c2), counter = counter + 1;
[4 Apr 2007 14:27] Mark Leith
Changing to InnoDB category.
[4 Apr 2007 15:45] Heikki Tuuri
If the bug appears first in 5.0.38, could it be that the fix of:

http://bugs.mysql.com/bug.php?id=27210

has broken 5.0.38?

Or is this associated:

http://bugs.mysql.com/bug.php?id=27033

?

Since InnoDB does not do the actual ON DUPLICATE KEY UPDATE processing at a high level, this is probably a MySQL server bug.
[10 Apr 2007 15:08] Shane Bester
5.0.36 doesn't show the problem.  5.0.38 does - so this is a regression.
[25 Apr 2007 17:48] Bugs System
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/25432

ChangeSet@1.2461, 2007-04-25 22:56:59+05:00, gshchepa@gshchepa.loc +3 -0
  Bug#27650: InnoDB handler doesn't save next autoincrement value after
  updating of  auto_increment column ('auto' in the samples below) with
  NULL values inside multi-row INSERT via ON DUPLICATE KEY UPDATE using
  auto=VALUES(auto).
  InnoDB handler fixed.
[25 Apr 2007 18:26] Timothy Smith
Gleb,

I've spoken with Igor, and we have approval from Heikki (and tacit approval from Sunny), so please do push your fix for this.  I've also notified the InnoDB team that we will be pushing our fix for this bug.

Thank you,

Timothy
[25 Apr 2007 20:15] Bugs System
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/25453

ChangeSet@1.2440, 2007-04-26 01:24:40+05:00, gshchepa@gshchepa.loc +3 -0
  Fixed bug #27650:
  INSERT into InnoDB table may cause "ERROR 1062 (23000): Duplicate entry..."
  errors or lost records after multi-row INSERT of the form:
  "INSERT INTO t (id...) VALUES (NULL...) ON DUPLICATE KEY UPDATE id=VALUES(id)",
  where "id" is an AUTO_INCREMENT column.
[25 Apr 2007 20:52] Bugs System
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/25458

ChangeSet@1.2440, 2007-04-26 02:01:23+05:00, gshchepa@gshchepa.loc +3 -0
  Fixed bug #27650:
  INSERT into InnoDB table may cause "ERROR 1062 (23000): Duplicate entry..."
  errors or lost records after multi-row INSERT of the form:
  "INSERT INTO t (id...) VALUES (NULL...) ON DUPLICATE KEY UPDATE id=VALUES(id)",
  where "id" is an AUTO_INCREMENT column.
  
  It happens because InnoDB handler forgets to save next insert id after
  updating of auto_increment column with new values. As result of that
  last insert id stored inside InnoDB dictionary tables differs from it's
  cached thd->next_insert_id value.
[30 Apr 2007 12:58] Bugs System
Pushed into 5.0.42
[30 Apr 2007 12:59] Bugs System
Pushed into 5.1.18-beta
[1 May 2007 17:45] Paul Dubois
Noted in 5.0.42, 5.1.18 changelogs.
[30 Aug 2007 19:13] Jeremy Cole
Any idea if this regression actually occurred in 5.0.37 or 5.0.38?
[10 Sep 2008 4:17] James Day
The fix for this bug caused bug #28781. That's fixed in 5.0.48 and 5.1.22 or 23.