MySQL Configuration to auto truncate decimal value

Question

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?

score 2 · Answer 1 · edited May 23 '17 at 12:10

The definition

DECIMAL(9,2)

means 9 digits of total precision, with 2 digits after the decimal place. The value

9999999999.99

has 12 total digits of precision, with 2 after the decimal place. Hence, the value is out of range. Rightfully, MySQL should have thrown an out of range error in both cases. In the case where it "worked," my guess is that truncation occurred.

By the way, you should be using DECIMAL(12,2) or wider to store the value in your question.

Update:

One possible explanation for why one of your servers was doing the insertion while the other failed is that the first has traditional mode turned off. Run the following on both servers:

SELECT @@SESSION.sql_mode

If you see output looking like

STRICT_TRANS_TABLES, STRICT_ALL_TABLES, ...

then the server is running in traditional mode, which means it won't truncate but will reject. To turn it off, try running

SET SESSION sql_mode=''

and them the insert should succeed (with truncation). But in any case, you should not be relying on truncation in production. If you need more precision, then widen the column.

Reference: Automatically trimming length of string submitted to MySQL

yes.. i know that, the question is why on un server it worked, I want to know the configuration value that makes the autotruncation, i'm not able to find it in mysql variables or documentation. — Arnold Roa, Sep 30 '16 at 01:13

score 1 · Accepted Answer · edited May 23 '17 at 10:34

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.

MySQL Configuration to auto truncate decimal value

2 Answers2