db_affected_rows() returns the wrong result?

Written by Domenic on Apr 24th, 2011. It was a Sunday.

Given the following Drupal code:

  1. db_query("UPDATE users SET dummy_column = 'foo'");
  2. printf("Records affected: %d\n", db_affected_rows());

and assuming that dummy_column doesn't exist in the users table, what is printed? Here's a hint from the php manual:

mysql_affected_rows()
Returns the number of affected rows on success, and -1 if the last query failed.

If you said "-1" like me, you're wrong (at least some of the time). The code above will return "1" if you have watchdog enabled.

Today I helped a someone on the Drupal Questions stackexchange site who ran into this problem where the above Drupal code would return "1" instead of "-1" like it seemingly should. I figured out why pretty quickly: if you have watchdog enabled, when db_query() fails it will log the failure in watchdog. Logging in watchdog is normally an "INSERT INTO {watchdog}..." query, which means before you exit the db_query() function, you're writing to the db again, and mysql_affected_rows()/db_affected_rows() returns some unexpected things.

The solution is to look at the result of your initial db_query() call and use your logical operator on that, eg:

  1. $result = db_query("UPDATE users SET name2 = 'a'");
  2. if ($result) {
  3.   printf("Records affected: %d\n", db_affected_rows());
  4. }
  5. else {
  6.   print "Query not successful";
  7. }

Link to the original Drupal Questions page.