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.

you should add an update trigger and a log table storing the old and new values along with id, session id and current user for debugging. maybe the changes pass but were overwritten by another statement — Sir Rufo, Jan 09 '13 at 00:44
I think Sir Rufo's comment could be super-helpful to your situation, because it'll help nail down the sequence in which things happen and whether it's a more complex scenario in which multiple queries result in the picture you are seeing. — DWright, Jan 09 '13 at 14:35
"[..] or some sort of locking collision [..]" since other fields within the same row were updated, you have no locking issue. the smallest thing innodb can lock is a row. i can not lock a single field. — scones, Jan 25 '13 at 03:31
I'm seeing this issue with our database too when updating large amount of rows. Have you figured out the culprit / fix? — foureight84, Mar 24 '15 at 19:26

score 1 · Answer 1 · answered Jan 08 '13 at 23:21

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

score 0 · Answer 2 · answered Feb 13 '13 at 01:17

Readjust your "strictness" in MySQL to TRADITIONAL:

SET sql_mode = 'TRADITIONAL';

and try the insert from the console without PHP. Then check for errors that could explain it.

Reset the strictness to "forgiving" by:

SET sql_mode = '';

Or if your using a PDO object to connect to the database get an error back through PHP with something like this:

try {
  $sqlstr = "UPDATE grades SET result = '$result', state = 2, date_synced = '$date',updated_at = '$date' WHERE id = $id";
$s = $pdo->exec($sqlstr);
}
catch (PDOException $e) {
  $error = $e->getMessage();
  echo $error;
}

Basically, do whatever it takes to get the error message of what's going on. It's most likely SQL is sending an error but PHP isn't set up to show it in your script.

Actually, it's most likely MySQL is generating a warning because the INT(1) has been truncated or something. So PHP might just ignore the warning and continue. — Luke Watts, Feb 13 '13 at 01:19
FWIW, INT(1) and INT(1000000) are exactly the same for storage and the range of supported values. The argument is not a length limit, it's a display hint. MySQL's INT is always a 32-bit data type. — Bill Karwin, Feb 13 '13 at 01:27

score 0 · Answer 3 · answered Feb 15 '13 at 23:50

0

Try changing the name of the field from 'state' to something else, like 'state_num'. While it is not listed as a reserve word for MySQL, it name might be causing problems.

answered Feb 15 '13 at 23:50

dlink

1,489
17
22

score 0 · Answer 4 · edited Oct 27 '17 at 01:30

Your query that set "state" and other fields runs twice and sometimes one of your task completes faster than another. It updates all fields, and your script that called that task also runs MySQL query that updates "state" field.

For example, you have a PHP script that runs a shell command "tar" in background via exec() and set "state" in DB after complete. Next string of your PHP code updates your "state" in your database. But sometimes bash command that you called runs faster than your PHP script calls MySQL update (if this bash command runs in background mode).

First step: run bash script from PHP that set "state" = 3 (awaits further processing) after complete, update other fields with date etc.

Next step: in your PHP after exec() you set "state" = 2 (in process). In this case if your bash script in background will do the job faster than PHP runs MySQL update query, you will get updated date value and also "state" = 2 (but it had value = 3 one second ago).

I had this issue.

update query sometimes does not update field in mySQL database

4 Answers4