MySQL record UPDATE should fail but does not. Why?

here is an interesting situation.

I start a transaction with MySQL. My transaction involves 3 related queries. Each query must succeed, and if not then none should be written to the database.

Now... on purpose, for the 2nd query...which happens to be an UPDATE query... I changed the pk value identifying the record to be updated to an invalid (non-existing) PK value. I wanted the 2nd query to fail for testi开发者_如何学编程ng purposes. The query is fine, it is just that the c_id value is wrong (the record I'm trying to UPDATE does not exits).

The problem is that the query is executed with an "OK"...

mysql> UPDATE tableX SET bal = 4576.99 WHERE c_id = 3789;  
Query OK, 0 rows affected (0.00 sec)  
Rows matched: 0  Changed: 0  Warnings: 0   

This is a problem because the error (is error from my perspective since a key record that must be updated was not updated in a chain of related queries) was not caught and the transaction thus did not abort and rollback, instead the process goes on to the 3rd query which also succeeds and then the transaction is committed.

So, I find it strange that such an error is not caught by MySQL or not labeled an error by MySQL.

Any insights as to why or how to fix?

---

It is correct, 0 rows were updated. If, for your logic, that is an error you should test the number of affected rows and then raise an error if that number is 0:

  DECLARE count INT;
  UPDATE tableX SET bal = 4576.99 WHERE c_id = 3789;
  SELECT ROW_COUNT() INTO count;
  IF count = 0 THEN
     CALL raise_error;
  END IF;

error will make the transaction rollback. To raise an error just call a routine which doesn't exist as explained on this SO question: How to raise an error within a MySQL function

further info about row_count():

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count