MySQL Bugs: #27650: ON DUPLICATE KEY UPDATE fails when setting UNIQUE values with multi row inserts

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	CPU Architecture:	Any
Tags:	bfsm_2007_04_19, ON DUPLICATE KEY UPDATE

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

Testcase

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

Second test case, generated word "heartbeat's" seems a problem

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

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;

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;

Changing to InnoDB category.

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.

5.0.36 doesn't show the problem.  5.0.38 does - so this is a regression.

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.

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

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.

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.

Pushed into 5.0.42

Pushed into 5.1.18-beta

Noted in 5.0.42, 5.1.18 changelogs.

Any idea if this regression actually occurred in 5.0.37 or 5.0.38?

The fix for this bug caused bug #28781. That's fixed in 5.0.48 and 5.1.22 or 23.