MySQL Bugs: #32779: MySQL and PHP4.4.4 do not properly populate rows affected after update query

Bug #32779	MySQL and PHP4.4.4 do not properly populate rows affected after update query
Submitted:	27 Nov 2007 17:06	Modified:	3 Dec 2007 14:54
Reporter:	Joseph O'Shea	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	5.0.27-community-nt	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I am attempting to use MySQL 5.0 with PHP4.4.4.
I can connect and query successfully, but PHP's mysql_affected_rows() method and
MySQL select row_count() method return a NULL value for actual rows affected.
I have executed the update statement using the MySQL command interpreter and
the update is carried out successfully. However, if I immediately run a command
to get the # of affected rows it always returns 0 (or null).

If this has anything to do with auto commit, how do I turn it OFF. I want full control as to when to commit or rollback changes.

This bug makes it difficult to properly perform error handling in PHP scripts.

How to repeat:
Assuming you have PHP version 4.4.4 and MySQL 5.0

using PHP's mysql_* functions:
connect to mysql (mysql_connect function)
select a database (mysql_select_db function)
build an update query string
parse the query (mysql_query) store the resulting resource object ($result)
$rows_affected = mysql_affected_rows($result);

at this point $rows_affected is NULL in PHP. (acts like boolean false in an if statement)
Copying and pasting the actual query into MySQL command line interpreter, the statement executes OK, but if I attempt to query the affected rows MySQL actually reports 0 rows affected even though I just did it.

Up to this point i have had no trouble using PHP4.4.4 and MySQL5.0 except for this.

This problem seriously affects my PHP scripts if they cannot properly tell if an SQL statement has worked or not.

Suggested fix:
??

Thank you for the report.

Please indicate accurate version of MySQL you use.

> I have executed the update statement using the MySQL command interpreter and
> the update is carried out successfully.  However, if I immediately run a command
> to get the # of affected rows it always returns 0 (or null).

Does this mean you have same problem without using PHP?

MySQL related PHP bugs are handled on the PHP bug system at http://bugs.php.net/

Please re-submit the bug report there and add real code and result output (both PHP and mysql command line client), your current description is too vague ...

Thanks for the quick response.

My Correct version MySQL as reported by select version(); is 5.0.27-community-nt.
The version of PHP I am using is PHP4.4.4 installed using the windows MSI installer package.
At home I run Windows XP Pro SP2 with IIS version 6.  At work I use Windows 2000 Server with IIS Version (???).  THis problem occurs at both home/work with the same results.

The output of "select @@autocommit;" reveals that autocommit is enabled (1); this auto committing not helping the situation.

Further testing I found if I update a row with itself, the number of rows affected will return 0 (zero) when I use "select row_count()" immediately after the updating SQL statement.  However, the "mysql_affected_rows()" API call returns NULL.

If I update a row with genuine changed data, the number of rows affected will return 1 when I use "select row_count()" immediately after, but the API call "mysql_affected_rows()" still does not recognize any rows have changed.

To get around this PHP bug, I wrote a PHP function to run a query that runs "select row_count()" and inspects the affected rows value and handles it.

I will re-post this problem with bugs.php.com as this problem is occurring within PHP.

P.S.  Is there a way to turn off AUTO COMMIT'ing permanently.  I'm sure it is preventing me from using the START TRANSACTION/COMMIT/ROLLBACK functionality.
I followed an example in the MySQL DOC but ROLLBACK'S are not happening when I expect them to.  I suspect that the autocommit "feature" is part of the problem.

If I still have a problem with START TRANSACTION/COMMIT/ROLLBACK's but I am not sure if it is a coding issue or a genuine bug, where is the better place to post the problem to?

Thanks again,
Joseph

Thank you for the feedback.

We have at least one bug #23383 about mysql_affected_rows fixed after version 5.0.27 is released. But as you use precompiled version of PHP extension is better to fill bug at bugs.php.net as hartmut said already.

Also in the initial description you mentioned you had problems in mysql command line client also without using PHP. If yes, please provide step-by-step instructions what you did.

Update: Friday November 30, 2007

I understand why MySQL returned 0 (zero) rows affected when, updating a row contents with itself the database detects there were no actual updates to do and ignores it.  If there are actual data to update I can get the number of rows affected using "SELECT ROW_COUNT()" immediately following the update stmt.

The PHP function mysql_affected_rows() still does not work.  All of my calls to mysql_affected_rows() returns 0 (zero) and acts as a boolean false in a PHP if statement.

I will post the bug on the PHP bug site php.bugs.net when I get a chance.  I have a workaround so I do not need to use mysql_affected_rows() for now.
Another option I may consider is upgrading my PHP to a higher version.

The other problem I mentioned involving TRANSACTIONS has been resolved.  When I created an image database at home of all work related tables, for some reason I had installed MySQL without support for INNODB.  All of my tables at home used MyISAM which does not support TRANSACTION support.
I reconfigured my MySQL instance to allow INNODB tables and rebuilt/re-loaded the DB and I am able to get TRANSACTION/COMMIT/ROLLBACK functionality.

My problems now are solely with my PHP version and not with my MySQL version.

You may update the status to Closed if you wish.

Thank you again for your support.

Joseph O'Shea

Thank you for the feedback.

Closed as "Not a Bug" according to last comment.