MySQL decimal field 'Data truncated for column x at row 1' issue

Question

I have a mysql table with a decimal(16,2) field. Seems like the addition operation with another decimal(16,2) field string can cause the Data truncated for column x at row 1 issue, which raises exception in my django project.

I'm aware of multiplication or division operation of that field can cause this issue bacause the result is probably not fit in decimal(16,2) definition, but does the addition and subtraction operation the same?

My MySQL server version is 5.5.37-0ubuntu0.14.04.1. You can reproduce this issue from bellow:

mysql> drop database test;
Query OK, 1 row affected (0.10 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table t(price decimal(16,2));
Query OK, 0 rows affected (0.16 sec)

mysql> insert into t values('2004.74');
Query OK, 1 row affected (0.03 sec)

mysql> select * from t;
+---------+
| price   |
+---------+
| 2004.74 |
+---------+
1 row in set (0.00 sec)

mysql> update t set price = price + '0.09';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t set price = price + '0.09';
Query OK, 1 row affected, 1 warning (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1265 | Data truncated for column 'price' at row 1 |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t;
+---------+
| price   |
+---------+
| 2004.92 |
+---------+
1 row in set (0.00 sec)

Solution 1

There are two problems:

1. You are not storing decimal values, you're trying to store string/varchar, which is converted into double value by mysql, for example following code does not give errors update t set price = price + 0.09; (even executed several times)

2. Anyway this code gives expected warning (note number) update t set price = price + 0.091; you can change it to update t set price = price + cast(0.091 as decimal(16,2)); of course with cast you can use string values too update t set price = price + cast('0.09' as decimal(16,2));

Solution 2

In my case problem occurs when I try to insert a decimal with 3 digits after the the dot like: 0.xxx on a column defined as DECIMAL(10,2)

I changed it to DECIMAL(10,3) OR used php to enter values like 0.xx on DECIMAL(10,2) table