MysqlDataTruncation: Data truncation: Out of range value for column 'column' at row 1

Question

I am using java and MySQL. I am trying to insert a value into a table.

When I insert, I get this exception:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of 
range value for column 'idPxxx' at row 1

Only 5% of inserts produces this exception, others work. What does the error mean and how do I prevent it?

Answer 1

It means that the data you are storing in idPxxx doesn't fit. For example a string might be too long, or a number too large.

What is the datatype of idPxxx? And what are you trying to store in it?

Answer 2

How to produce this Exception on the MySQL console:

mysql> create table penguin (mydecimal decimal(9,8));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into penguin values (1234.1234);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1264 | Out of range value for column 'mydecimal' at row 1 |
+---------+------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from penguin;
+------------+
| mydecimal  |
+------------+
| 9.99999999 |
+------------+
1 row in set (0.00 sec)

You tried to cram 1234 into a column that could take maximum 9.9 repeated. Notice the row is still inserted.

You can get the MySQL console to prevent this conversion using strict mode:

mysql> set sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into penguin values (5678.5678);
ERROR 1264 (22003): Out of range value for column 'mydecimal' at row 1

The insert command failed in attempting add a second row to penguin:

mysql> select * from penguin;
+------------+
| mydecimal  |
+------------+
| 9.99999999 |
+------------+
1 row in set (0.00 sec)

Solutions

1. Expand the size of the datatype in your column to accept the value you put in there.
2. Shrink the size of the value that you are trying to cram into a small data type.