Bug #27210 INNODB ON DUPLICATE KEY UPDATE
Submitted: 16 Mar 2007 12:31 Modified: 29 Aug 2007 2:10
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.36, 5.0.37 OS:Linux (Linux, Windows)
Assigned to: Timothy Smith
Tags: bfsm_2007_04_19, ON DUPLICATE KEY UPDATE

[16 Mar 2007 12:31] [ name withheld ]
Description:
Since 5.0.36 ON DUPLICATE KEY UPDATE does not work on INNODB tables (but does work on myisam tables). Gives no errors but does not perform any update.

How to repeat:
CREATE TABLE `test` (
  `test1` varchar(3) NOT NULL,
  `test2` varchar(4) NOT NULL,
  PRIMARY KEY  (`test1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 
-- Data i tabell `test`
-- 

INSERT INTO `test` (`test1`, `test2`) VALUES 
('tes', '5678');
INSERT INTO `test` (`test1`, `test2`) VALUES 
('tes', '1234') ON DUPLICATE KEY UPDATE `test2` = '1234';
[16 Mar 2007 13:32] Sveta Smirnova
Thank you for the report.

I can not repeat it with current sources: update happens. Please accurate package version you use (name of file you downloaded).
[16 Mar 2007 14:17] Sveta Smirnova
Hm... I can not repeat it with version 5.0.36 too:

$mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.36-enterprise-gpl MySQL Enterprise Server (GPL)

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

mysql> use test;
Database changed
mysql> CREATE TABLE `test` (
    ->   `test1` varchar(3) NOT NULL,
    ->   `test2` varchar(4) NOT NULL,
    ->   PRIMARY KEY  (`test1`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `test` (`test1`, `test2`) VALUES 
    -> ('tes', '5678');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test` (`test1`, `test2`) VALUES 
    -> ('tes', '1234') ON DUPLICATE KEY UPDATE `test2` = '1234';
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test;
+-------+-------+
| test1 | test2 |
+-------+-------+
| tes   | 1234  | 
+-------+-------+
1 row in set (0.00 sec)

mysql> \q
Bye

Please provide configure string you used to compile sources.
[19 Mar 2007 8:11] Sveta Smirnova
Thank you for the additional comment.

I still can't repeat it in myself environment. Please provide your configuration file.
[19 Mar 2007 13:43] Heikki Tuuri
Hi!

Please show the screen shots from both connections that demonstrate the bug.

Could this be a bug in the query cache? Does the bug occur if you switch off the query cache?

Regards,

Heikki
[19 Mar 2007 17:01] Shane Bester
All, I repeated this problem as described by tech, on 5.0.36.

after restart, the value really is different:

mysql> select * from test;
+-------+-------+
| test1 | test2 |
+-------+-------+
| tes   | 5678  |
+-------+-------+
1 row in set (0.00 sec)

mysql> select * from test;
ERROR 2006 (HY000): MySQL server has gone away  <----restart mysqld
No connection. Trying to reconnect...
Connection id:    1
Current database: nsp

+-------+-------+
| test1 | test2 |
+-------+-------+
| tes   | 1234  |
+-------+-------+
1 row in set (0.22 sec)

Will post more info, shortly
[19 Mar 2007 17:10] Shane Bester
testcase for 5.0.36
-------------------

set global query_cache_size=10*1024*1024;
set global query_cache_type=1;
\r
drop table if exists `test`;
CREATE TABLE `test` (`test1` varchar(3) NOT NULL,`test2` varchar(4) NOT NULL,PRIMARY KEY  (`test1`)) ENGINE=InnoDB DEFAULT CHARSET=latin1		    ;
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
\r
select * from test;
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234') ON DUPLICATE KEY UPDATE `test2` = '1234';
select * from test;
flush tables;
select * from test;

notice how the last two SELECT results differ.
cannot repeat on 5.0.38.
[20 Mar 2007 12:01] Martin Friebe
also repeatable on current community 5.0.37, with innodb and myisam.
only with query cache.

5.0.38 / 5.0.40 are not affected for me neither
[20 Mar 2007 15:10] Valerii Kravchuk
Verified just as described in Shane's last comment with 5.0.36/5.0.37 (that is, query cache is involved). As it is repeatatble with Enterprise binaries, it surely affects some customers...
[20 Mar 2007 16:00] Heikki Tuuri
I will check if the bug is in InnoDB or in the query cache.
[20 Mar 2007 16:01] Heikki Tuuri
Ok, if it affects also MyISAM, then this is not an InnoDB bug. Changing this to a 'MySQL Server' bug.
[20 Mar 2007 16:30] Martin Friebe
I also noted that the "insert" returns ZERO rows affected, while hitting the dub-key.

on earlier or non affected mysql version it will say "2 rows" affected.

Thus I think, even so the update on duplicate key is executed, the error is somewhere there. it returns 0 rows affected, which probably leads to the query cache not being cleaned
[20 Mar 2007 21:31] Sveta Smirnova
Bug #27307 was marked as duplicate of this one
[21 Mar 2007 10:10] Dmitry Lenev
Hi Martin, All!

I think Martin is right in his last comment! (Thank you for the hint!) Preliminary investigation shows that this problem is likely to has the same source as the bug#27006. And the latter was fixed by the following ChangeSet by Monty:

ChangeSet 1.2413.7.2 2007/02/21 19:38:40 monty@mysql.com
  After merge fixes
  This also fixes a bug in counting number of rows that are updated when we have many simultanous queries
sql/sql_insert.cc 1.215 2007/02/21 19:38:39 monty@mysql.com
  After merge fixes
  (This actually fixes a bug in old code when many connections are in use)

That is why it is not repeatable in 5.0.38 and later versions.
[23 Mar 2007 8:50] Sveta Smirnova
Bug #27387 was marked as duplicate of this one
[26 Mar 2007 23:42] Timothy Smith
I will add a test case for this problem to our test suite.  The bug is already fixed (since 5.0.38) as it had the same root cause as another bug.  However, it would be nice to have a test case for this.

Regards,

Timothy
[12 Apr 2007 11:59] Sveta Smirnova
Possible duplicate: bug #27744
[19 Apr 2007 20:38] 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/24949

ChangeSet@1.2439, 2007-04-19 22:38:12+02:00, tsmith@quadxeon.mysql.com +2 -0
  innodb_mysql.test, innodb_mysql.result:
    Add test case for Bug #27210: INNODB ON DUPLICATE KEY UPDATE
[19 Apr 2007 21:09] Timothy Smith
A test case for this bug has been added to MySQL 5.0 and 5.1, queued in the -maint team trees.  It will be available in the top-level repos as soon as possible.

Note that this does *not* include any actual server code changes.  It is only an additional test case, to demonstrate that the bug is, indeed, fixed.

See Dmitri's comment above (21 March) regarding bug #27006 for explanation.
[20 Apr 2007 7:48] Sveta Smirnova
Bug #27968 was marked as duplicate of this one.
[20 Apr 2007 13:43] Paul Dubois
This bug was fixed by the patch for Bug#27006, so I have added
Bug #27210 to the changelog entries for Bug#27006 in the
5.0.38, 5.1.17 changelogs.
[26 Apr 2007 11:35] Bugs System
Pushed into 5.0.42
[26 Apr 2007 11:36] Bugs System
Pushed into 5.1.18-beta
[7 Jul 2007 0:24] Jeremy Cole
The ChangeLog comment for this bug sucks.

>>>>>
AFTER UPDATE triggers were not activated by the update part of INSERT ... ON DUPLICATE KEY UPDATE statements. (Bug#27006, Bug#27210)

This bug was introduced by the fix for Bug#19978.
<<<<<

No mention of the interaction between the query cache and ON DUPLICATE KEY UPDATE being *completely broken*?
[29 Aug 2007 2:10] Paul Dubois
Noted in 5.0.38, 5.1.17 changelogs.

An INSERT ... ON DUPLICATE KEY UPDATE statement might modify values
in a table but not flush affected data from the query cache, causing
subsequent selects to return stale results. This made the combination
of query cache plus ON DUPLICATE KEY UPDATE very unreliable.

This bug was introduced by the fix for Bug#19978.