MySQL increment value of attribute - misses sometimes(?)

Question

I have a column called views in my table A. I want to increment the views column like so:

UPDATE A set views = views + 1 WHERE ID = blabla LIMIT 1;

This seems like the way to do it, at least to me. Or so I thought.

Seems like when I (from PHP) do:

$views = get_viewcount($id);
$views++:
save_viewcount($id, $views);  //here we just update views with the $views variable

This always works. But carries the penalty of an extra roundtrip to the DB with the get_viewcount and then incrementing it in PHP and sending it back. It always works.

The SQL statement above "sometimes" works. I know - I too hate the word "sometimes" in programming - but put another way, I cannot say WHEN but at times it doesn't increment it when I do it in SQL directly in one go.

Suggestions?

Answer 1

You need to isolate the bug in your system. It is very unlikely to be a problem with MySQL.

I would suggest running a ton of tests, and looking at database logs, etc. It is most likely the query is simply not getting executed due to some logic in your system, or due to the request dying/ending before it reaches the query.

Answer 2

You must be doing something wrong.

If you tell mysql:

UPDATE foo SET views = views+1 WHERE id = 1337;

it will increment it.

Try it on the command line.

Whatever code you're using to run the sql is failing, not the sql statement itself.

And what's the point of LIMIT=1 on an UPDATE query?

Answer 3

What I recommend doing is to set PHP to echo out the query it's running, the result it's getting back, etc., etc. Everything you possibly can. Look at SQL logs if applicable to see what queries are being run on what tables. Basically you need to see exactly where the fail point it.

When you state that the SQL statement sometimes works, is that on a basis of being called from your code, or being called via a mysql (assuming that is what you are using) prompt? If it's the prior, have you tried running it in a command prompt to see if you get the same result as your code? If not, then you can rule out the database and start looking specifically at your code.

Good luck!

Answer 4

I would look at where and when you do your BEGIN TRANSACTION / COMMIT processing.

It could be you are not checking the SQL return code and missing a "DEADLOCK" warning.

Answer 5

I think you guys are wrong.

I am facing the same issue. Having query

UPDATE workers set jobs=jobs+1 WHERE id=XX

and it really sometimes will not increment!

How do I know? Right before the statement I have a debug output. I see the debug output printed 10000 times, but the actual value in database is 9980-9999!

I am running several threads so can this be a concurrency problem?