From: [EMAIL PROTECTED]
Operating system: RedHat 7.3
PHP version: 4.2.3
PHP Bug Type: MySQL related
Bug description: UPDATE works, rows updated, but mysql_affected_rows=0
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.
--
Edit bug report at http://bugs.php.net/?id=20290&edit=1
--
Try a CVS snapshot: http://bugs.php.net/fix.php?id=20290&r=trysnapshot
Fixed in CVS: http://bugs.php.net/fix.php?id=20290&r=fixedcvs
Fixed in release: http://bugs.php.net/fix.php?id=20290&r=alreadyfixed
Need backtrace: http://bugs.php.net/fix.php?id=20290&r=needtrace
Try newer version: http://bugs.php.net/fix.php?id=20290&r=oldversion
Not developer issue: http://bugs.php.net/fix.php?id=20290&r=support
Expected behavior: http://bugs.php.net/fix.php?id=20290&r=notwrong
Not enough info: http://bugs.php.net/fix.php?id=20290&r=notenoughinfo
Submitted twice: http://bugs.php.net/fix.php?id=20290&r=submittedtwice
register_globals: http://bugs.php.net/fix.php?id=20290&r=globals
PHP 3 support discontinued: http://bugs.php.net/fix.php?id=20290&r=php3
Daylight Savings: http://bugs.php.net/fix.php?id=20290&r=dst
IIS Stability: http://bugs.php.net/fix.php?id=20290&r=isapi
