MySQL Bugs: #53135: tables w/NDB engine do not return the number of rows deleted in "replace into"

Bug #53135	tables w/NDB engine do not return the number of rows deleted in "replace into"
Submitted:	23 Apr 2010 22:51	Modified:	26 Apr 2010 12:43
Reporter:	Zardosht Kasheff (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-6.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	5.1.46, 5.5

Description:
NDB uses the flag HA_EXTRA_WRITE_CAN_REPLACE to determine if handler::write_row can silently overwrite an existing row when the command is "replace into". As a result, because HA_ERR_DUPP_KEY (or something like that, code is not in front of me) is not returned, the user thinks a new row was written.

The result is that this part of the "replace into" contract is not honored:

"The REPLACE statement returns a count to indicate the number of rows affected. This is the sum of the rows deleted and inserted."

How to repeat:
n/a

Suggested fix:
change the documentation. Do not make this contract binding.

I understand that the user wants to know how the rows were affected, but for NDB, and TokuDB, this is very expensive. It would be nice to change the contract somehow so that this information does not need to be returned.

Thank you for the report.

Verified as described with mysql-5.1-telco-7.1 tree.

Test case for MTR:

--source include/have_ndb.inc

create table t1(f1 int not null primary key, f2 int) engine=ndb;
create table t2(f1 int not null primary key, f2 int) engine=myisam;

replace into t1 values(1,1);
select row_count();
replace into t1 values(1,1);
select row_count();

replace into t2 values(1,1);
select row_count();
replace into t2 values(1,1);
select row_count();

Zardosht,

this has been verified in 7.1 (thanks Sveta!). Nevertheless are version numbers given 5.1.46 and 5.5. This indicates that you are using MySQL "Vanilla" Server with cluster. Please use the official cluster versions 7.1 and the server coming in that version as cluster coming with the standard MySQL Server is old and will be removed from that version soon.

Bernhard,

I wrote 5.1.46 and 5.5 because the aspect of this bug that really interests me is the usage of HA_EXTRA_WRITE_CAN_REPLACE. Storage engine vendors that want to use this flag in a way similar to NDB (e.g. TokuDB), will run into this same problem that NDB has.

The reason I filed the bug (which I realize is not very clear) is that having this contract to return the number of overwritten rows is expensive for storage engines. That NDB does not do it is an example.

-Zardosht