Affected row count not correct

[Jamie Bohr]

I was using a script that checked the number of records affected after an insert/update. The script now returns 1 even is no rows were updated. I have attached a small script below to show my point. On one Linux system the output is

should be 1: affected=1
should be 0: affected=2
should be 2: affected=2

on another, recently patched, system the output is

should be 1: affected=1
should be 0: affected=1
should be 2: affected=2


I would expect no records to be affected by the second line since the record being inserted is the same and MySQL says 0 rows were updated if I run the update command within MySQL. This appears to be the same bag list at http://bugs.mysql.com/bug.php?id=29941 except I am using MySQL 5 not 4.


#!/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);



Edited 1 time(s). Last edit at 11/12/2008 01:02PM by Jamie Bohr.