Bug #49164 READ-COMMITTED reports "matched: 0" on compound PK
Submitted: 27 Nov 2009 13:46 Modified: 10 May 2010 11:29
Reporter: Andrii Nikitin Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.1.41 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[27 Nov 2009 13:46] Andrii Nikitin
Description:
problem is identical as bug #31310, but when PK is compound.

at some condition (when READ-COMMITTED or innodb_locks_unsafe_for_binlog os ON)

UPDATE reports "Rows matched : 0", while SELECT with identical WHERE returns result.

cannot repeat in 5.0.88 and in 5.0.30 with neither READ-COMMITTED or innodb_locks_unsafe_for_binlog .

How to repeat:
Connection1:

SET tx_isolation = 'READ-COMMITTED';
use test;

CREATE TABLE instrumentstates ( PRODUCTID INT, INSTRUMENTID BIGINT,
INSTRUMENTTYPE TINYINT, STATE TINYINT, a TINYINT,
b TINYINT, PRIMARY KEY (PRODUCTID, INSTRUMENTID) ) ENGINE INNODB;

insert into instrumentstates values (1,1,1,1,1,1), (2,2,2,2,2,2),
(3,3,3,3,3,3);

set autocommit=0;

update instrumentstates set state=5;

at this point open new connection to db and execute:

connection2:
SET tx_isolation = 'READ-COMMITTED'; 
set autocommit=0; 
use test;
update instrumentstates set state=6 where productid=1 and instrumentid=1;

problem result for last update is:

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

while it is expected to wait on lock.

Suggested fix:
UPDATE shouldn't report "matched: 0" if SELECT with identical 'WHERE' returns result.

I expect innodb should wait on lock.
[30 Nov 2009 10:55] Andrii Nikitin
The bug probably was assigned wrong category.
Changing to 'Server' as most probably it should be the same as in bug #31310
[30 Nov 2009 12:24] Georgi Kodinov
Andrii, I've asked Gleb to check : The fix for bug #31310 is not solving this problem.
[9 Feb 2010 6:15] Jingqi Xu
We are planing to upgrade from MySQL 5.0.33 to MySQL 5.1.4x. This issue result in a lot of modification in our code.
[10 May 2010 7:44] Marko Mäkelä
This is a duplicate of Bug #52663. I am not closing this bug yet, however, because I saw a strange hang when testing this (the UPDATE in connection 2 seems to hang forever, not observing innodb_lock_wait_timeout or the fact that the transaction in connection 1 was rolled back).
[10 May 2010 11:29] Marko Mäkelä
My test failure was a false alarm (error in the test). This is a duplicate of Bug #52663.
[31 May 2010 8:29] Bugs System
Pushed into 5.1.48 (revid:vasil.dimov@oracle.com-20100531082307-9x08gg1g7zybx2jy) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:16)
[15 Jun 2010 8:14] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:marko.makela@oracle.com-20100511104500-c6kzd0bg5s42p8e9) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:30] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:marko.makela@oracle.com-20100511104500-c6kzd0bg5s42p8e9) (pib:16)
[17 Jun 2010 6:14] Bugs System
Pushed into 5.5.5-m3 (revid:alexey.kopytov@sun.com-20100615145247-8bj0vmuqlotbqsn9) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:16)
[17 Jun 2010 6:18] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615150216-cubqoyn1fj9b6a2p) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (pib:16)
[14 Oct 2010 8:38] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 8:53] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)
[14 Oct 2010 9:08] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)