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
• Status: Duplicate
• 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:[e-mail]-20100531082307-9x08gg1g7zybx2jy) (version source revid:[e-mail]-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:16)

[15 Jun 2010 8:14] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100511104500-c6kzd0bg5s42p8e9) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:30] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100511104500-c6kzd0bg5s42p8e9) (pib:16)

[17 Jun 2010 6:14] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615145247-8bj0vmuqlotbqsn9) (version source revid:[e-mail]-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:16)

[17 Jun 2010 6:18] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615150216-cubqoyn1fj9b6a2p) (version source revid:[e-mail]-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:[e-mail]-20101014082627-jrmy9xbfbtrebw3c) (version source revid:[e-mail]-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:[e-mail]-20101014083757-5qo48b86d69zjvzj) (version source revid:[e-mail]-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:[e-mail]-20101014084420-y54ecj85j5we27oa) (version source revid:[e-mail]-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)