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)



Subject: Incorrect rowcount from INSERT ... ON DUPLICATE KEY UPDATE
Download (untitled) / with headers
text/plain 527b
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
I see this issue in 2.9002 and 3.008 so far. May also occur in other versions.
Download (untitled) / with headers
text/plain 1.1k
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.
Download (untitled) / with headers
text/plain 548b
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: Garen
Download (untitled) / with headers
text/plain 275b
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.
Download (untitled) / with headers
text/plain 528b
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


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.