Strange MySQL float behaviour

[Chris]

I'm using a field to store currency xxxxxxxx.yy.

I have FLOAT NOT NULL, I was making many additions and substractions (+0.01 and -0.01) and... one day the field started displaying (on phpmyadmin) only xxx.y values (as if it lost the second 'y' xxx.y?). What's more interesting, when I make 10 substractions of -0.01 then the tenth substraction decreases it by -0.10 (it just does not display in PHP the second digit after the dot). So, it is if it is still stored in MySQL correctly somehow but relay it to PHP incorrectly...

When I "reset" it by querrying "field=1000", everything starts working perfectly again (until a certain thereshold of transactions is done and it breaks again, I guess).

I have no clue how it is even possible.

[BlackScorp]

how did you created a field? maybe you try DECIMAL instead of FLOAT?

http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html

[Chris]

ALTER TABLE ... ADD fieldname FLOAT NOT NULL

I'm more concered with the answer "why" than a solution (I recoded it to use sparate ints for now). Because until I understand why something like that happens I won't dare to use floats ever again...

Yes, DECIMAL sounds like a nice alternative.

[Harkins]

http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html

Floats accumulate error. You really want to be using decimal or integer for currency values.