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

• Submitted: 20 Jul 2007 19:19
• Modified: 20 Feb 2013 22:37
• Reporter: Al Sorrell
• Status: Unsupported
• Category: Connectors: DBD::mysql ( Perl )
• Severity: S3 (Non-critical)
• Version: 4.005
• OS: Any (Sun Solaris 8, Mac OS X)
• CPU Architecture: Any
• Tags: count, ON DUPLICATE KEY

[20 Jul 2007 19:19] Al Sorrell

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

[20 Jul 2007 20:11] Al Sorrell

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

[23 Jul 2007 12:21] Sveta Smirnova

Thank you for the report.

Verified as described.

[29 Oct 2007 20:42] Al Sorrell

Any idea if/when this may be fixed?

[23 Jun 2010 18:34] Jonathan Marshall

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

[20 Feb 2013 22:37] Sveta Smirnova

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.