Arnold Roa Arnold Roa - 2 months ago 8
MySQL Question

MySQL Configuration to auto truncate decimal value

I've a table with a field

DECIMAL(9,2)
and I've 2 servers running both mysql 5.7

If I run this code

INSERT INTO `money_accounts` (`balance`) VALUES (9999999999.99);


On one of the servers it got inserted and value is truncated, on the other it raise a
Out of range value for column balance
error.

My question is, what is the configuration value that makes this happen? or why it's happening in one server and not in the other?

Answer

If we read the documentation: http://dev.mysql.com/doc/refman/5.7/en/out-of-range-and-overflow.html

When MySQL stores a value in a numeric column that is outside the permissible range of the column data type, the result depends on the SQL mode in effect at the time

If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard.

If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.

So it means that You should set necessary sql mode that will not fail when out-of-range error happens.

There is no config parameter about that.

And resolution of same issue we can see here that says that disabling STRICT_TRANS_TABLES and STRICT_ALL_TABLES modes can fix Your problem.