Incrementing in MySQL

Question

I try to increment a field in mysql but it seems that it is not really working for some reasons ...

That is the query I run in mysql query:

UPDATE profil_perso
SET profile_views = profile_views +1
WHERE id = 30537

In the status bar it says : 1 row affected by the last command. No resultset returned. But it didn't seemed to work. At first I thougth that it was simply because there were no rows at all. So then I ran this query:

SELECT *
FROM profil_perso
WHERE id = 30537

And found 1 row and the profile_views field is still at 0...

Any ideas of why this would be that way ?

[edit] profile_views = 1 (set manually) at this moment and it still doesn't work.

Answer 1

Just to verify - is profile_views zero, or null? If you add 1 to null, you still get null, so that could be your problem.

ETA:

So it isn't null. Next question would be, do you have autocommit on or, if not, did you issue a "commit"? Could be that it was updated, but your update never committed.

Answer 2

Didn't you forget to commit a transaction when working with an InnoDB table?

UPDATE:

Since it's a MyISAM, I'll ask yet a couple of stupid questions:

1. Are you absolutely sure you're issuing UPDATE and SELECT against one database?

   I once had a large farm of databases with identical schemata, and this used to be a problem when someone deleted something from the wrong database.

2. Aren't you using some kind of client caching on your client? What client are you using?

And try to REPAIR your table, this also may be the issue.

Answer 3

Add some parens and a coalesce to see if that fixes it:

UPDATE profil_perso
SET profile_views = ( COALESCE(profile_views,0) + 1 )
WHERE id = 30537

Answer 4

If it's null, won't increment.

UPDATE profil_perso
SET profile_views = IF(profile_views,profile_views+1,1)
WHERE id = 30537