MySQL Bugs: #19978: INSERT ... ON DUPLICATE KEY

Bug #19978	INSERT ... ON DUPLICATE KEY - rows affected incorrect
Submitted:	21 May 2006 6:15	Modified:	27 Feb 2007 20:30
Reporter:	Richard Dale	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.20-BK, 4.1.19	OS:	Linux (Fedora Core 4 (Linux) 2.6.16)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
Mysql 4.1.19 running on Fedora Core 4 (Linus) X86_64 with InnoDB tables

According to:
http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html
The rows-affected value is 1 if the row is inserted as a new record and 2 if
an existing record is updated. 

But what happens if the row is neither inserted nor updated?  Right now it
appears to returns '2'.  It should return '0'.

How to repeat:
I have a table called  which has a (combined) primary key of assetid and
date plus several data fields (open, high, low, close - all doubles, and
voume - a bigint).  See the bottom of the email for the table definition.

INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES 
(202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE
KEY UPDATE
open='334.25',high='334.25',low='334.25',close='334.25',volume='1';

The first time around, the insert works fine:
Query OK, 1 row affected (0.03 sec);

The second time around, when there should be neither an insert nor an update
that affects any rows I get:
Query OK, 2 rows affected (0.05 sec)

To check that the update wasn't in fact updating any rows, I tried:

UPDATE price SET
open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE
assetid=202690 AND date='2006-05-18';
Query OK, 0 rows affected (0.02 sec)

Rows matched: 1  Changed: 0  Warnings: 0

So, it appears that the "rows affected" returned by an INSERT ... ON
DUPLICATE KEY is incorrect.

-----
Table definition:

CREATE TABLE `price` (
  `assetid` int(11) NOT NULL default '0',
  `date` date NOT NULL default '0000-00-00',
  `open` double default NULL,
  `high` double default NULL,
  `low` double default NULL,
  `close` double default NULL,
  `volume` bigint(20) default NULL,
  PRIMARY KEY  (`assetid`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

------

I also tried changing the table definition to from DOUBLE to DECIMAL(10,2) and also VARCHAR(20) to avoid any issues with precision but this also has the same problem.

Suggested fix:
If INSERT ... ON DUPLICATE KEY UPDATE
neither inserts nor updates a record then it should return 0.

Thank you for a problem report. I was able to repeat the behaviour you described with 4.1.20-BK:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.20

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `price` (
    ->   `assetid` int(11) NOT NULL default '0',
    ->   `date` date NOT NULL default '0000-00-00',
    ->   `open` double default NULL,
    ->   `high` double default NULL,
    ->   `low` double default NULL,
    ->   `close` double default NULL,
    ->   `volume` bigint(20) default NULL,
    ->   PRIMARY KEY  (`assetid`,`date`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES
    -> (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICA
TE
    -> KEY UPDATE
    -> open='334.25',high='334.25',low='334.25',close='334.25',volume='1';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES  (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='1';
Query OK, 2 rows affected (0.01 sec)

mysql> UPDATE price SET
    -> open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE
    -> assetid=202690 AND date='2006-05-18';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES  (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='2';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from price;
+---------+------------+--------+--------+--------+--------+--------+
| assetid | date       | open   | high   | low    | close  | volume |
+---------+------------+--------+--------+--------+--------+--------+
|  202690 | 2006-05-18 | 334.25 | 334.25 | 334.25 | 334.25 |      2 |
+---------+------------+--------+--------+--------+--------+--------+
1 row in set (0.00 sec)

So, there is no way to distinguish real update (with volume=2) with a "fake" update (all the same values used), if INSERT ... ON DUPLICATE KEY UPDATE is used. It is a bug. Something like

Rows matched: 1  Changed: 0  Warnings: 0

should be returned, at least.

This is probably a bug/feature request in MySQL. InnoDB does not take part in calculating 'affected rows'.

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/19434

ChangeSet@1.2403, 2007-02-06 22:32:56+03:00, evgen@moonbone.local +5 -0
  Bug#19978: INSERT .. ON DUPLICATE erroneously reports some records were
  updated.
  
  INSERT ... ON DUPLICATE KEY UPDATE reports that a record was updated when
  the duplicate key occurs even if the record wasn't actually changed
  because the update values are the same as these in the record.
  
  Now the compare_record() function is used to check whether the record was
  changed and the update of a record reported only if the record differs
  from the original one.

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/19450

ChangeSet@1.2403, 2007-02-07 00:46:03+03:00, evgen@moonbone.local +5 -0
  Bug#19978: INSERT .. ON DUPLICATE erroneously reports some records were
  updated.
  
  INSERT ... ON DUPLICATE KEY UPDATE reports that a record was updated when
  the duplicate key occurs even if the record wasn't actually changed
  because the update values are the same as those in the record.
  
  Now the compare_record() function is used to check whether the record was
  changed and the update of a record reported only if the record differs
  from the original one.

The fix has been pushed to 5.0.36 and 5.1.16-beta.
(It will not be fixed in 4.1)

Noted in 5.0.36, 5.1.16 changelogs.

I just saw this new behaviour and I don't understand (apart from the meaning of 'updated') where was the bug ?

The old behaviour permitted to compute how many rows were new and how many were presents deducted on the return value and the number of rows in the request. I don't think it is still possible to do with the new behaviour.

As a matter of fact, it is a incompatible change of behaviour and should'nt happen in a stable release (One of my application relies on the old behaviour to perform check and will be broken with this change).

Side effects of the fix for this bug are bug#27033(fixed in 5.0.40/5.1.17) and
bug#27006(fixed in 5.0.36/5.1.16 by Monty)

Bug#27006 is fixed in the version 5.0.38, not in 5.0.36.

affected rows does not seem to be correctly calculated in all situations, as you can see from the following example:

CREATE TABLE `webcams` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `owner_id` int(10) unsigned NOT NULL default '0',
  `last_online` int(10) unsigned NOT NULL default '0',
  `num_online` int(10) unsigned NOT NULL default '0',
  `descr` varchar(200) collate latin1_german1_ci NOT NULL default '',
  `last_ip` varchar(15) collate latin1_german1_ci NOT NULL default '',
  `last_port` int(10) unsigned NOT NULL default '0',
  `last_width` smallint(5) unsigned NOT NULL default '0',
  `last_height` smallint(5) unsigned NOT NULL default '0',
  `online` tinyint(3) unsigned NOT NULL default '0',
  `active` tinyint(3) unsigned NOT NULL default '0',
  `pass` varchar(20) collate latin1_german1_ci NOT NULL default '',
  `local_ip` varchar(15) collate latin1_german1_ci NOT NULL default '',
  `skey` varchar(32) collate latin1_german1_ci NOT NULL default '',
  `mkey` varchar(32) collate latin1_german1_ci NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `owner_id` (`owner_id`),
  KEY `online` (`online`),
  KEY `combo4` (`active`,`online`,`last_online`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci AUTO_INCREMENT=3 ;

INSERT INTO webcams SET owner_id=1,active='1',descr='super cam!!',pass='test' ON DUPLICATE KEY UPDATE owner_id=1,active='1',descr='super cam!!',pass='test',online=online+11;
affected rows: 1

INSERT INTO webcams SET owner_id=1,active='1',descr='super cam!!',pass='test' ON DUPLICATE KEY UPDATE owner_id=1,active='1',descr='super cam!!',pass='test',online=online+1;
affected rows: 0 (should be 2!!!)

Corin,

What exact version do you use?