MySQL Bugs: #27210: INNODB ON DUPLICATE KEY UPDATE

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

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';

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).

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.

Thank you for the additional comment.

I still can't repeat it in myself environment. Please provide your configuration file.

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

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

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.

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

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...

I will check if the bug is in InnoDB or in the query cache.

Ok, if it affects also MyISAM, then this is not an InnoDB bug. Changing this to a 'MySQL Server' bug.

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

Bug #27307 was marked as duplicate of this one

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.

Bug #27387 was marked as duplicate of this one

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

Possible duplicate: bug #27744

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

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.

Bug #27968 was marked as duplicate of this one.

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.

Pushed into 5.0.42

Pushed into 5.1.18-beta

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*?

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.