Weird zeros behaviour (double) on MariaDB 5.5.60

Question

Dear friends from SO!

I'm having an odd issue with a MariaDB 5.5.60 running on linux.

For some reason, I'm doing simple operations with the difference column, as you can see, is a double.

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `email` varchar(256) DEFAULT NULL,
  `username` varchar(256) NOT NULL,
  `password` varchar(256) NOT NULL,
  `won` int(11) NOT NULL DEFAULT '0',
  `lose` int(11) NOT NULL DEFAULT '0',
  `difference` double NOT NULL DEFAULT '0',
  `available_balance` double NOT NULL DEFAULT '0',
  `pending_received_balance` double NOT NULL DEFAULT '0',
  `spawns` int(11) NOT NULL DEFAULT '0',
  `condicion` varchar(256) NOT NULL DEFAULT 'offline',
  `address` varchar(256) NOT NULL DEFAULT 'offline',
  `eliminado` int(12) NOT NULL DEFAULT '0',
  `creacion` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

The problem:

-917.3700000000001

This happens when the system starts adding and subtracting values. As these numbers represents money, we can't tolerate any arbitrary difference.

One of the queries that can lead to this problems is the following:

    UPDATE users SET
    difference                  = difference - '$xfer->value_final',
    available_balance           = '$user->available_balance',
    pending_received_balance    = '$user->pending_received_balance',
    condicion                   = '$user->condicion'
    WHERE id                    = '$user->id'

As you can see, the value is being updated to substract $xfer->value_final, which, in this case is 101.93.

It's even a bit hard to replicate, as I had to place the same order at least 10 times, before getting the long zero tailed value.

Once the value happens once, then, it will of course remain full of zeros for every next operation, or until fixed.

This values are coming from the BTC blockchain, however I'm 100% sure that there's always 2 decimals on them, as I'm always storing them in another table.

¿Does anyone know why this could be happening? ¿Is this a bug? ¿Or am I missing something?

¿How can I solve this?

Thanks in advance; Chris

Use `DECIMAL` instead of `DOUBLE` if you know you will always have precision to two decimal digits. _Especially_ never use floating point values for money, ever. — Amadan, Aug 23 '18 at 03:48
@Amadan, thanks for your quick reply, however, decimal is supposed to have even more precision, according to the manuals... I'm working with 2 places after the coma... this should work properly even with float... I'll try decimal, but I'm not even sure where I'm missing it. — Chris Russo, Aug 23 '18 at 03:53
Read the linked question, which should answer why you _never ever_ use floats for money. `DECIMAL` has exactly the precision you specify, and will never pull a shenanigan like [0.2 + 0.1 = 0.30000000000000004](https://stackoverflow.com/questions/7545015/can-someone-explain-this-0-2-0-1-0-30000000000000004). — Amadan, Aug 23 '18 at 03:58
How did you declare the column? Something like `difference DECIMAL(10, 2) NOT NULL DEFAULT 0` should not do that. (Increase `10` appropriately to your limits.) — Amadan, Aug 23 '18 at 04:06
`difference` decimal(10,0) NOT NULL DEFAULT '0', Ok, I see... — Chris Russo, Aug 23 '18 at 04:10
`DECIMAL(10, 2)` should give you `-99999999.99` to `+99999999.99` (two decimal digits, max ten digits total). `DECIMAL(10, 0)` will, unsurprisingly, give you `-9999999999` to `+9999999999` (ten digits maximum with no decimal digits). — Amadan, Aug 23 '18 at 04:12

Chris Russo · Answer 1 · 2018-08-23T04:22:40.230

Thanks @Amadan!

Here's how we solve:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `email` varchar(256) DEFAULT NULL,
  `username` varchar(256) NOT NULL,
  `password` varchar(256) NOT NULL,
  `won` int(11) NOT NULL DEFAULT '0',
  `lose` int(11) NOT NULL DEFAULT '0',
  `difference` decimal(10,2) NOT NULL DEFAULT '0.00',
  `available_balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `pending_received_balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `spawns` int(11) NOT NULL DEFAULT '0',
  `condicion` varchar(256) NOT NULL DEFAULT 'offline',
  `address` varchar(256) NOT NULL DEFAULT 'offline',
  `eliminado` int(12) NOT NULL DEFAULT '0',
  `creacion` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

All doubles have been replaced for Decimal 10,2.

Weird zeros behaviour (double) on MariaDB 5.5.60

1 Answers1