Incorrect rowcount from INSERT ... ON DUPLICATE KEY UPDATE [rt.cpan.org #58595]

[mbeijen]

trafficstars

Migrated from rt.cpan.org#58595 (status was 'open')

Requestors:

• [email protected]

From [email protected] on 2010-06-22 14:47:30:

When an "INSERT ... ON DUPLICATE KEY UPDATE" is completed, MySQL returns
the number of rows affected: 1 if an INSERT occurred, or 2 if an UPDATE
occurred (http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html).

However, DBD::mysql fails to carry that behavior into Perl.  It always
returns 1.  As a result, there's no direct way for Perl apps to tell
whether an insert occurred or an update occurred.

Details and test cases are reported here:
http://bugs.mysql.com/bug.php?id=29941

Thanks,
--Jonathan A. Marshall

From [email protected] on 2010-06-22 15:32:29:

I see this issue in 2.9002 and 3.008 so far.  May also occur in other
versions.

From [email protected] on 2010-06-23 17:36:03:

This bug also appears in the current version of DBD::mysql, 4.014.

Here's a correction and a simple test case.

CORRECTION:
 - MySQL returns 2 and DBD::mysql returns 2 if there was an update.
 - MySQL returns 1 and DBD::mysql returns 1 if there was an insert.
 - MySQL returns 0 but DBD::mysql returns 1 if no records were changed.
   ---> In this case, DBD::mysql is supposed to return 0E0.

TEST CASE:
 CREATE TABLE t ( a INT PRIMARY KEY, b INT );

 mysql> INSERT t (a,b) VALUES(3,4) ON DUPLICATE KEY UPDATE b=9;
 Query OK, 1 row affected (0.00 sec)

 mysql> INSERT t (a,b) VALUES(3,4) ON DUPLICATE KEY UPDATE b=9;
 Query OK, 2 rows affected (0.02 sec)

 mysql> INSERT t (a,b) VALUES(3,4) ON DUPLICATE KEY UPDATE b=9;
 Query OK, 0 rows affected (0.00 sec)

NOTE ------^ MySQL returned 0 here.

 mysql> DELETE FROM t;

 print $dbh->do( "INSERT t (a,b) VALUES(3,4) ON DUPLICATE KEY UPDATE b=9" );
1

 print $dbh->do( "INSERT t (a,b) VALUES(3,4) ON DUPLICATE KEY UPDATE b=9" );
2
 print $dbh->do( "INSERT t (a,b) VALUES(3,4) ON DUPLICATE KEY UPDATE b=9" );
1
^------ NOTE DBI returned 1 here, but it should have returned 0E0.

From [email protected] on 2010-08-27 21:20:19:

It appears this bug is related to the mysql_client_found_rows option. If
mysql_client_found_rows is turned off
(DBI->connect("...;mysql_client_found_rows=0")), DBI reports the correct
number of rows updated (zero).

This matches the behavior of the equivalent separate INSERT and UPDATE
statements when mysql_client_found_rows is turned on. INSERT IGNORE ...
will return zero, because no rows were inserted, and UPDATE ... will
return one because one row was matched, even though it was not updated.

Short answer: Turn mysql_client_found_rows off.

From https://www.google.com/accounts/o8/id?id=aitoawn_dieb3kc_wyl1edorstakxfknwbpugiw on 2011-11-17 18:18:30

On Fri Aug 27 17:20:19 2010, EFISCHER wrote:
...
> 
> Short answer: Turn mysql_client_found_rows off.

In that case, the mysql_client_found_rows=0 should be the default to be 
the least surprising; if not, the DBI documentation should explicitly 
mention this as a deviation.

From [email protected] on 2017-04-07 22:42:07:

On Å tv Nov 17 13:18:30 2011, https://www.google.com/accounts/o8/id?id=AItOawn_DiEb3KC_WyL1eDoRstaKxFknWBpUgiw wrote:
> On Fri Aug 27 17:20:19 2010, EFISCHER wrote:
> ...
> > 
> > Short answer: Turn mysql_client_found_rows off.
> 
> In that case, the mysql_client_found_rows=0 should be the default to be 
> the least surprising; if not, the DBI documentation should explicitly 
> mention this as a deviation.

There was a discussion about this problem and it is not a bug. See:
https://github.com/perl5-dbi/DBD-mysql/issues/111