Incrementing in MySQL

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.

From stackoverflow

• 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.

  Erick : It is at zero. I thought at first that it would be this and set it to 1. Still remains at 1 after the update.

  Erick : The table is MyISAM so it's not supposed to affect it, isn't it ?

• 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.

  Erick : it's a MyISAM :-)

  Erick : Man ... how foul I am ... it's working totally ok now. I was selecting from the _dev db instead of the _production db. Lol! It's weird, the _production db was in bold both times. Totally working now.

  Quassnoi : Welcome to the club :)

  Eric Petroelje : ha, that would explain it.

• 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
  

  Erick : Tried and it remains 1.

• If it's null, won't increment.

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

  David Caunt : You (I) learn something new every day!