Bug #29941 Perl affected row count incorrect for certain cases of ON DUPLICATE KEY UPDATE

Description:
In the example below using 'Server version: 5.0.45 MySQL Community Server (GPL)' on Solaris 8, there appears to be an undocumented side-effect of the ON DUPLICATE KEY syntax. The documentation states that this should return either 1 (data inserted) or 2 (data updated). It looks like in the case where you try 
to update a row where the primary key matches, but there is actually no change to the data it returns 0 when using the command line. 

That is actually a good thing, but should be documented.

However, it doesn't seem to carry over into the Perl interface - Perl returns 1

How to repeat:
mysql> CREATE TABLE test
    -> (
    ->         device          VARCHAR(30)     NOT NULL,
    ->         port            VARCHAR(40)     NOT NULL,
    -> 	       duplex          VARCHAR(4)      NOT NULL,
    -> PRIMARY KEY (device,port)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)

INSERT INTO test (device,port,duplex) VALUES ('rta','Gi1/1','auto');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+--------+-------+--------+
| device | port  | duplex |
+--------+-------+--------+
| rta    | Gi1/1 | auto   | 
+--------+-------+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO test (device,port,duplex) VALUES ('rta','Gi1/1','auto')    
    -> ON DUPLICATE KEY UPDATE duplex='none';
Query OK, 2 rows affected (0.01 sec)

mysql> INSERT INTO test (device,port,duplex) VALUES ('rta','Gi1/1','auto') 
    -> ON DUPLICATE KEY UPDATE duplex='none';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from test;
+--------+-------+--------+
| device | port  | duplex |
+--------+-------+--------+
| rta    | Gi1/1 | none   | 
+--------+-------+--------+
1 row in set (0.01 sec)

mysql> INSERT INTO test (device,port,duplex) VALUES ('rta','Gi1/1','auto')    
    -> ON DUPLICATE KEY UPDATE duplex='none';
Query OK, 0 rows affected (0.00 sec)

*** NOTE 0 rows returned here indicating nothing changed ***

mysql> select * from test;
+--------+-------+--------+
| device | port  | duplex |
+--------+-------+--------+
| rta    | Gi1/1 | none   | 
+--------+-------+--------+
1 row in set (0.00 sec)

mysql> delete from test;
Query OK, 1 row affected (0.01 sec)

===========================================
Using Perl 5.8.7
This is perl, v5.8.7 built for sun4-solaris

with DBI 1.5.8 and DBD::mysql 4.005
===========================================

#!/usr/local/bin/perl -w
use DBI;

my $db="DBI:mysql:findall:localhost;mysql_read_default_group=client";
my $dbh=DBI->connect ($db, undef, undef,
        {RaiseError=>0, PrintError=>0, AutoCommit=>1})
   or die "*** Could not connect to database: $DBI::errstr\n";

my $sth=$dbh->prepare( qq{
        INSERT INTO test (device,port,duplex)
        VALUES (?,?,?)
        ON DUPLICATE KEY UPDATE duplex='none'
   }) or die "error in insert prepare\n";

my $status=$sth->execute('rta','Gi1/1','auto')
  or die "** error on insert/update:Error $DBI::err ($DBI::errstr)\n";
print "insert/update status=$status\n";

my $sth2=$dbh->prepare('SELECT * FROM test')
  or die "error in select prepare\n";
my $stat2=$sth2->execute()
  or die "** error on select:Error $DBI::err ($DBI::errstr)\n";
print "select status=$stat2\n";

while (my @ary=$sth2->fetchrow_array()) {
    print join(',',@ary),"\n";
}
$dbh->disconnect();
exit 0;
==========================

1st run:

$ test.pl
insert/update status=1
select status=1
rta,Gi1/1,auto

2nd run:
$ test.pl
insert/update status=2
select status=1
rta,Gi1/1,none

3rd run:
$ test.pl
insert/update status=1          <<<<< for consistency, seems this should be 0!
select status=1
rta,Gi1/1,none

cut and paste error

In the command line example, there are two instances of

 INSERT INTO test (device,port,duplex) VALUES
('rta','Gi1/1','auto') 
    -> ON DUPLICATE KEY UPDATE duplex='none';

in a row with no SELECT between them. In reality - there was only one - the second was an accidental inclusion. The order should be 
CREATE, INSERT, SELECT, INSERT...ON DUP, SELECT, INSERT...ON DUP, SELECT

Al

Thank you for the report.

Verified as described.

Any idea if/when this may be fixed?

This problem has bitten me too.  Any solutions or workarounds?  Is the problem located in MySQL, DBD::mysql, or DBI?  See also https://rt.cpan.org/Ticket/Display.html?id=58595

Thanks,
--Jonathan

Thank you for the report.

We don't work on DBD::mysql bugs anymore. All its bugs should go to CPAN: https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql

Please refer to CPAN bug report, indicated in comments above.