MySQL Bugs: #40673: Perl affected row count incorrect for certain cases of ON DUPLICATE KEY UPDATE

Bug #40673	Perl affected row count incorrect for certain cases of ON DUPLICATE KEY UPDATE
Submitted:	12 Nov 2008 19:10	Modified:	13 Dec 2008 4:39
Reporter:	Jamie Bohr	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connectors: DBD::mysql ( Perl )	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Please see http://bugs.mysql.com/bug.php?id=29941 and http://forums.mysql.com/read.php?51,233987,233987#msg-233987 as a reference.

A value of affected rows is 1 is even is no records were updated.  When I run the same SQL from within MySQL 0 rows updated is returned.

Here is a script that will show the problem:

#!/usr/bin/perl

use DBI qw(:sql_types);

=pod
# SQL to create a test table named updated in the test DB.

CREATE TABLE `test`.`updated` (
`some_column` VARCHAR( 10 ) NOT NULL ,
`update_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY ( `some_column` )
) ENGINE = MYISAM
=cut

my $db_ptr = DBI->connect("dbi:mysql:test:localhost", 'root', '') ||
die "Connect failed: $DBI::errstr\n";

$sql = 'TRUNCATE TABLE `updated`';
$results = $db_ptr->prepare("$sql");
$results->execute;

$sql='
INSERT INTO updated (some_column)
VALUES ("somevalue")
ON DUPLICATE KEY UPDATE some_column="somevalue"
';

$results = $db_ptr->prepare("$sql");
$affected = $results->execute;
printf("should be 1: affected=%d\n", $affected);

$results = $db_ptr->prepare("$sql");
$affected = $results->execute;
printf("should be 0: affected=%d\n", $affected);

$sql='
INSERT INTO updated (some_column)
VALUES ("somevalue")
ON DUPLICATE KEY UPDATE some_column="othervalue"
';

$results = $db_ptr->prepare("$sql");
$affected = $results->execute;
printf("should be 2: affected=%d\n", $affected);

How to repeat:
Run the same update SQL command from within Perl.  The first time the command runs the affected rows should be some number while the second time the command is ran 0 rows affected should be returned.

Suggested fix:
Return 0 rows affected

Thank you for a problem report. Isn't it a duplicate of bug #29941?

Bug #29941 was filed for MySQL 4, I wanted to make sure it was known the problem still exists and bug #29941 has not been updated in over a year.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".