php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #20290 UPDATE works, rows updated, but mysql_affected_rows=0
Submitted: 2002-11-06 12:20 UTC Modified: 2002-11-07 01:11 UTC
From: ces at vaultbbs dot com Assigned:
Status: Not a bug Package: MySQL related
PHP Version: 4.2.3 OS: RedHat 7.3
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: ces at vaultbbs dot com
New email:
PHP Version: OS:

 

 [2002-11-06 12:20 UTC] ces at vaultbbs dot com
I have a simple section of code that attempts to UPDATE a row in a MySQL table.  If mysql_affected_rows() = 0 I assume the row didn't exist so I go ahead and INSERT it.  This works about 95% of the time.  

The other 5% of the time, the row does exist and IS updated, but mysql_affected_rows() returns 0--so I go ahead and INSERT it.  I then end up with a duplicate entry.  I could define the appropriate columns as "unique" so that the INSERT would fail, but I feel that something else is wrong here and doing this would be a work-around for what might be a real problem.

My table is entirely numeric, no strings.  It's not a case of updating a row to its same values and so it's not counted as an affected row.  The value DOES change.

My code is:

$SQL = "UPDATE UserStats SET MS=MS+$Var,MC=MC+1 
       WHERE idUser=$idUser AND Month=$Month";
$updateStats = mysql_db_query ($sDB, $SQL, $nConnection);
if(mysql_affected_rows($nConnection) == 0)
 {
 $SQL = "INSERT INTO UserStats SET idUser=$idUser,
   Month=$Month, MS=$Var,MC=1";
 $updateStats = mysql_db_query ($sDB, $SQL, $nConnection);
 }

MS, MC, and idUser are all BigInts.  Month is MediumInt. The $Var variable is always non-zero.  I've dumped the SQL queries to a logfile--they are always valid queries, so it's not an issue of one of my variables not being defined and producing an improper SQL.  I've also tried the mysql_affected_rows without the $nConnection paramter.

In summary, the above UPDATE *ALWAYS* works in that the actual row in question is always updated correctly in the MySQL database.  However, sometimes the mysql_affected_rows() returns 0 instead of 1; so my code continues to INSERT a new row and I end up with a duplicate.

MySQL version is 3.23.49.  Same UPDATE instruction works fine when executed manually multiple times in MySQL command-line, etc.  Always returns the correct number of rows having been updated.

I am not sure if this is a PHP problem or a MySQL problem, but I lean towards PHP since MySQL *IS* updating the row as requested and I can't duplicate the problem outside of PHP.  I'm also open to it being a code problem on my end, but at this point I don't see how.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2002-11-07 01:11 UTC] georg@php.net
Please check possible errors first (mysql_errno), and don't use deprecated functions (mysql_db_query).

If the problem still exists, set the status of this bug report back to open.
 
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Fri Oct 07 23:05:54 2022 UTC