Updating with $qpdb->query() always returns 0 rows affected

Question

I'm experiencing an odd situation while running an UPDATE with $wpdb->query().

Given the users table, with columns user_id (Primary Key) and status, and the user 3 existing and with status 0, I run the following query:

update users
set status = 8
where user_id = 3

On my machine, running the query with $wpdb->query, I get the result of 1: 1 row affected.

On another machine I run the query with the PHP code and always get 0 rows: before running the query I checked the status and it was 0, and if I run it with another tool (e.g. command line mysql client), it says me that is 1 affected row.

The query method of wpdb returns the number of affected rows in case of update (https://codex.wordpress.org/Class_Reference/wpdb#Running_General_Queries).

My machine and the remote one have got the same PHP version (5.4.40), same mysql server version, and same wordpress version (4.2.2). About the mysql PHP extensions, in local I've installed mysql, mysqli and mysqlnd: on the other machine I've got mysql and mysqli.

Is there someone with some clues about the possible reason? Thank you.

Edit 1

I tried to run the query also with the update method, but the result is always 0 rows affected. I start thinking that I found an incoherency in the $wpdb abstraction layer.

Answer 1

wpdb uses PHP's mysqli_affected_rows() or mysql_affected_rows(), which can be disabled via an argument in php.ini. I am guessing that that is what has happened. Check the disabled_functions line in your php.ini file and remove the references to those function.

Of the top of my head, I am not sure if extensions like Suhosin mess with that function but that is certainly possible.

Answer 2

I hope may answer is still useful. I have had the same problem with 3 servers and a bit of php script in a WP page to read/write to the WP db. One server worked perfectly, the other two would always return 0 on wpdb->update. Truly indicating no changes were made to the db, although inspecting the db showed the changes were indeed made. As it turned out, the affected pages were run twice due to a AJAX interference caused by Yoast SEO. The resulting html only showed the latest result however. Turning Yoast SEO off solved the problem and as it turned out the not affected server had Yoast SEO turned off to start with. In my case is was Yoast, but any AJAX interaction can cause the problem you so thoroughly described.