update query sometimes does not update field in mySQL database

Question

I have an mySQL update query that will sometimes update all the fields and sometimes it will update all the fields except one.

It is failing on about 10% of the calls.

My table is:

CREATE TABLE IF NOT EXISTS `grades` (   
`id` int(11) NOT NULL AUTO_INCREMENT,   
`state` int(1) NOT NULL,   
`result` varchar(255) NOT NULL,   
`date_synced` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`) ) 
ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4395 ;

My query is:

$sqlstr = "UPDATE grades SET result = '$result', state = 2, date_synced = '$date', updated_at = '$date' WHERE id = $id";

When it fails, result, date_synced, and updated_at are updated but state remains unchanged.

There is another query that updates only the state field and that one also intermittently fails.

I have been unable to recreate the problem in our test environment. Could there be something wrong with the production mySQL database or some sort of locking collision?

---

I have more information. I am using mysqli. The other query that only updates the state is using mysql. Would that cause a problem?

I thought InnoDB locked by row. It doesn't allow partial row updates, does it?

---

Another update to address the comments.

My code flow is pretty linear.

The row is created with state=0.
<flash stuff here> and the row is updated with state=1
A cron job pulls all state=1 and sends an api call
if api call is successful, the row is updated with state=2, result, date_synced, and updated_at
if api call is error, the row is updated with state=3, result, and updated_at

The state field is never set back to 0 (after the flash) or 1 (after the api call). Since date _synced and result are being set but (sometimes) state is still 1, it is like the update to the state field is being dropped.

I will add the update trigger and see if that gives me more information.

Answer 1

Does it state that "{n} Rows Affected?"

Also, is it repeatable; can you run the same query on the exact same data and it will do different things?

If so, you could have a corrupt installation, or a corrupt database.

Have you tried doing a repair & optimize on the tables? That could help.

Sorry about the buckshot answer :P