1

I have a table like this:

mysql> describe transactions;
+-----------------------+---------------+------+-----+---------+----------------+
| Field                 | Type          | Null | Key | Default | Extra          |
+-----------------------+---------------+------+-----+---------+----------------+
| id                    | int(11)       | NO   | PRI | NULL    | auto_increment |
| cost                  | float(10,2)   | YES  |     | NULL    |                |
+-----------------------+---------------+------+-----+---------+----------------+

I (naively?) thought that would be a good way to store amounts. I am 3 days from release, and I discovered this:

mysql> UPDATE transactions SET cost = 162295.99 WHERE id = 14891;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from transactions where id = 14891;
+-------+---------------+
| id    | cost          |

+-------+---------------+
| 14891 |     162295.98 | 
+-------+---------------+

...?!? I entered .99, not .98! So:

  • Why is this doing it?
  • How do I get out of this problem?
  • Would decimal work? I tried with:

    mysql> alter table transactions modify cost decimal(12,2) NULL DEFAULT NULL; Query OK, 1120 rows affected, 119 warnings (0.14 sec) Records: 1120 Duplicates: 0 Warnings: 119

    mysql>

However I am getting scary warnings. With "show warnings" I get:

mysql> show warnings;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Note  | 1265 | Data truncated for column 'cost' at row 10  |
| Note  | 1265 | Data truncated for column 'cost' at row 369 |
| Note  | 1265 | Data truncated for column 'cost' at row 371 |
| Note  | 1265 | Data truncated for column 'cost' at row 378 |
| Note  | 1265 | Data truncated for column 'cost' at row 402 |
| Note  | 1265 | Data truncated for column 'cost' at row 403 |
+-------+------+---------------------------------------------
  • How do I see which one "row 10" is anyway?

  • KEY QUESTION: How do I get out of this hole safely?

  • (Rhetorical question bonus question) Why didn't this happen like 3 months ago, when fixing would have been easy?

  • The reason that your data shows up as .98 is that "The FLOAT and DOUBLE types represent approximate numeric data values" More on these datatypes here: dev.mysql.com/doc/refman/8.0/en/floating-point-types.html – Randi Vertongen Mar 8 '19 at 12:09
  • The actual problem in this case is that FLOAT has only 24 significant bits (about 7 significant digits). That is not enough bits to distinguish between ...98 and ...99. DOUBLE, with 53 bits, would "fix" that. But you should use DECIMAL for money. – Rick James Mar 13 '19 at 16:01
4

Known problem with IEEE floats. Use DECIMAL

To fix

  • add new column of correct data type
  • copy data over (check for conversion errors here)
  • drop bad column
  • rename new column to correct name

Why? Murphy's Law.

| improve this answer | |
  • Fantastic, problem solved, I can now release. Thanks! – Merc Mar 9 '19 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.