Bug #2709 Affected Rows for ON DUPL. KEY undocumented, perhaps illogical
Submitted: 11 Feb 2004 11:12 Modified: 18 Oct 2004 18:02
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 OS:
Assigned to: Paul DuBois CPU Architecture:Any

[11 Feb 2004 11:12] Dean Ellis
Two parts:

1) The method by which the return value for mysql_affected_rows() is calculated for INSERT ... ON DUPLICATE KEY UPDATE statements is not clearly documented.

2) Currently, it returns a value calculated like REPLACE (1 for each INSERT plus 1 for each of these which was actually processed as an UPDATE), so that a 5 row INSERT with 2 duplicates returns 7 (5 inserts, 2 updates) rather than 5 (3 inserts, 2 updates).

How to repeat:

Suggested fix:
1) Document this in the INSERT Syntax chapter (and perhaps in mysql_affected_rows() chapter).

2) Provided Monty approves, change the calculation to return the presumably more logical value.
[12 Feb 2004 10:38] Sergei Golubchik
for five rows with two duplicates affected_rows should be 5: 3 inserts, 2 updates
[16 Feb 2004 3:35] Sergei Golubchik
this is a server issue, not only docs.
[11 Mar 2004 2:21] Victor Vagin
subj: bk commit - 4.1 tree (vva:1.1756)

  1.1756 04/03/04 16:54:10 vva@eagle.mysql.r18.ru +4 -0
  fixed Bug #2709 "Affected rows for ON DUPL.KEY undocumented, perhaps illogical" and
  added commands enable_affected_rows and disable_affected_rows to client/mysqltest.c
[15 Mar 2004 4:19] Michael Widenius
Assume the following case:
create table t1(a int primary key, b int);
insert into t1 values(1,1);
insert into t1 values(1,1) on duplicate key b=b+1;

It's better that we return 2 as row count for affected rows as this allows the application to easy know what happened:
1 -> Row was inserted
2 -> Row was updated
[16 Oct 2004 16:48] Jason McManus
I would like to second the motion for the documentation to be updated to reflect this return value in both the function reference as well as the INSERT statement syntax.  I was bitten by this issue in testing the return results on this function (and the respective query in mysql-client), and initially my program returned it as invalid and rolled back the transaction (which clearly was partially my fault...).  I will add it to the comments on the respective pages on dev.mysql.com, but it would be nice if it was included in the officially released documentation as well, so it could be disseminated in other forms.

However, now that I understand how to interpret the return value, it is logical and does provide more information, and thus is a convenient feature.
[18 Oct 2004 18:02] Paul DuBois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
[27 Sep 2005 6:57] rajesh
i was created the table with the field NAME
and iam inserting the string into that name field
as given below:

insert into rajesh NAME values(' rajesh 'kumar');
then iam getting the error why so.
reply me immly.
[27 Sep 2005 6:59] rajesh
i was created the table with the field NAME
and iam inserting the string into that name field
as given below:

insert into rajesh NAME values(' rajesh 'kumar');
 //that means i am inserting singlequotes along with that text

then iam getting the error why so.what is the solution for this to insert a single quote or double quote.so kindly reply me immly.