Skip Menu |

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 58595
Status: resolved
Priority: 0/
Queue: DBD-mysql
People
Owner: Nobody in particular
Requestors: cpan [...] wellrounded.com
Cc: pali [...] cpan.org
AdminCc:
Bug Information
Severity: Important
Broken in:
  • 2.9002
  • 3.0008
  • 4.014
Fixed in: (no value)

History Show all quoted text
  Tue Jun 22 10:47:30 2010 cpan [...] wellrounded.com - Ticket created
Subject: Incorrect rowcount from INSERT ... ON DUPLICATE KEY UPDATE
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
  Tue Jun 22 11:32:29 2010 cpan [...] wellrounded.com - Correspondence added
I see this issue in 2.9002 and 3.008 so far. May also occur in other versions.
  Tue Jun 22 11:32:30 2010 cpan [...] wellrounded.com - Status changed from 'new' to 'open'
  Tue Jun 22 11:33:27 2010 cpan [...] wellrounded.com - Broken in 3.0008 added
  Tue Jun 22 11:35:00 2010 cpan [...] wellrounded.com - Status changed from 'open' to 'new'
  Tue Jun 22 16:30:51 2010 cpan [...] wellrounded.com - Broken in 4.014 added
  Wed Jun 23 13:36:03 2010 cpan [...] wellrounded.com - Correspondence added
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 ); Show quoted text
mysql> INSERT t (a,b) VALUES(3,4) ON DUPLICATE KEY UPDATE b=9;
Query OK, 1 row affected (0.00 sec) Show quoted text
mysql> INSERT t (a,b) VALUES(3,4) ON DUPLICATE KEY UPDATE b=9;
Query OK, 2 rows affected (0.02 sec) Show quoted text
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. Show quoted text
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.
  Fri Aug 27 17:20:19 2010 pause [...] ethan.elfhame.net - Correspondence added
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.
  Fri Aug 27 17:20:21 2010 The RT System itself - Status changed from 'new' to 'open'
  Thu Nov 17 13:18:30 2011 https://www.google.com/accounts/o8/id?id=AItOawn_DiEb3KC_WyL1eDoRstaKxFknWBpUgiw - Correspondence added
From: Garen
On Fri Aug 27 17:20:19 2010, EFISCHER wrote: ... Show quoted text
> > 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.
  Fri Apr 07 18:40:55 2017 pali [...] cpan.org - Cc PALI added
  Fri Apr 07 18:42:07 2017 pali [...] cpan.org - Correspondence added
On Štv Nov 17 13:18:30 2011, https://www.google.com/accounts/o8/id?id=AItOawn_DiEb3KC_WyL1eDoRstaKxFknWBpUgiw wrote: Show quoted text
> 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
  Wed Nov 15 02:48:48 2017 MICHIELB [...] cpan.org - Correspondence added
Ticket migrated to github as https://github.com/perl5-dbi/DBD-mysql/issues/184
  Wed Nov 15 02:48:50 2017 MICHIELB [...] cpan.org - Status changed from 'open' to 'resolved'