SQL UPDATE not working?

Question

In my LAMP application (using CodeIgniter), I have a call to $this->db->update... that generates a SQL like the following:

UPDATE `MyTable` SET `MyProcess` = 5 WHERE `Id` = 1

The problem is that this seems to work intermittently -- and I currently have no idea what might be wrong. Is there anything about MySQL I need to know about when trying to update? I'm adding & updating records a lot (but probably no more than one query every 5 seconds). When I run the query in phpMyAdmin, it works fine.

Suggestions on how to troubleshoot this?

Answer 1

One thing that can catch people out is that if your id for some reason doesn't exist in the table you won't get any error from the UPDATE statement - it will just silently do nothing.

You can use for example mysql_affected_rows (or equivalent for your database API) to see that a row was in fact updated, and if not display or log an error message. This should help you troubleshoot your problem.

Answer 2

This is an addition to Mark's answer. mysql_affected_rows also returns 0 if you try to update a record but the values in that record do not change.

For example; current values in MyTable:

+----+-----------+
| ID | MyProcess |
+----+-----------+
| 1  | 5         |
+----+-----------+

Then when you run your query:

UPDATE `MyTable` SET `MyProcess` = 5 WHERE `Id` = 1

mysql_affected_rows will return 0

Could this be what is happening in your case?