Bug #58040 affected rows indicates incorrect number (4294967296)
Submitted: 8 Nov 2010 3:35 Modified: 2 Dec 2010 5:19
Reporter: Mikiya Okuno Email Updates:
Status: Closed Impact on me:
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:mysql-5.1-telco-7.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: 7.x

[8 Nov 2010 3:35] Mikiya Okuno
When deleting many lows using IN syntax in WHERE clause, affected rows displays incorrect value 4294967296. This happens only when amount of deleted data size is big.

Turning off engine condition pushdown optimization doesn't have luck. This happens on NDB only.

How to repeat:
set group_concat_max_len=1024*1024;

create table t (a int unsigned not null primary key auto_increment, b varchar(188) character set latin1) engine ndb;
insert into t values(NULL, 'this is a test');
insert into t select NULL, b from t; # repeat 20 times

select @sql:=concat('delete from t where a in (', group_concat(a), ')') as s from (select a from t order by rand() limit 1000) as tt\G

prepare stmt from @sql;

execute stmt;
execute stmt; # <=== In the second trial, the affected rows field turns out to be 4294967296.

select @sql:=concat('explain select * from t where a in(', group_concat(a), ')') as s from (select a from t order by rand() limit 1000) as tt\G

prepare stmt from @sql;
execute stmt;

Suggested fix:
[8 Nov 2010 3:37] MySQL Verification Team
The operation logs when the problem is and is not happening.

Attachment: bug58040.txt (text/plain), 31.84 KiB.

[8 Nov 2010 3:45] MySQL Verification Team
Cluster configuration.

Attachment: config.ini (application/x-wine-extension-ini, text), 1.27 KiB.

[1 Dec 2010 12:03] 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:


3357 jonas oreland	2010-12-01
      ndb - bug#58040 - make sure affected rows get correctly computed even when splitting statement into several batches
[1 Dec 2010 12:05] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.21 (revid:jonas@mysql.com-20101201120643-emu5n5ostwkbqgqb) (version source revid:jonas@mysql.com-20101201120643-emu5n5ostwkbqgqb) (merge vers: 5.1.51-ndb-7.0.21) (pib:23)
[1 Dec 2010 12:16] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.40 (revid:jonas@mysql.com-20101201120427-xi0k12fqs0m2ehvv) (version source revid:jonas@mysql.com-20101201120427-xi0k12fqs0m2ehvv) (merge vers: 5.1.51-ndb-6.3.40) (pib:23)
[1 Dec 2010 13:42] Jonas Oreland
pushed to 6.3.40, 7.0.21 and 7.1.10
[2 Dec 2010 5:19] Jon Stephens
Documented bugfix in the NDB-6.3.40, 7.0.21, and 7.1.10 changelogs, as follows:

        The number of rows affected by a statement that used a WHERE
        clause having an IN condition with a value list containing a
        great many elements, and that deleted or updated enough rows
        such that NDB processed them in batches, was not computed or
        reported correctly.