MySQL decimal field 'Data truncated for column x at row 1' issue
decimal, mysql, sql
Solution
There are two problems:
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)
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));`
Problem
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) ```