com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'column_name'

Question

I am using Java JDBC with MySQL and I get this error:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: 
Data too long for column 'column_name'

How do I remedy this error?

Answer 1

I faced the same issue in my Spring Boot Data JPA application when I was going to insert an image file in the database as bytes - it gave me the same error.

After many R&D I found a solution like below.

---

I added the following line in my application.properties file and it resolved the issue:

spring.datasource.url=jdbc:mysql://localhost:3306/conweb?sessionVariables=sql_mode='NO_ENGINE_SUBSTITUTION'&jdbcCompliantTruncation=false

Hope it will be helpful to someone.

Answer 2

How to reproduce this error in MySQL terminal

mysql> create table penguins(val CHAR(2));
Query OK, 0 rows affected (0.08 sec)

mysql> insert into penguins values("AB");
Query OK, 1 row affected (0.00 sec)

mysql> insert into penguins values("ABC");
Query OK, 1 row affected, 1 warning (0.01 sec)

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

This is the error you are getting, however in the MySQL terminal shows it as a warning and still inserts your row, just silently truncates it.

The MySQL JDBC is not as forgiving, and spits a hard error and will not insert the row.

---

2 Solutions

Either Increase the capacity of the data type you are inserting into, or decrease the size of the content you are placing in there.

Legend to increase the size of your column:

If you are using   then use this
-----------------  --------------
smallint           int
int                long
TEXT               LONGTEXT
LONGTEXT           CLOB
CLOB               Store the content to disk, and save the filename in the db.

Answer 3

This may seem pretty obvious, and it is. You apparently tried to insert or update a row with a value for the 'column_name' column that is larger than will fit into said column.

If it's CHAR or VARCHAR, for example, you probably have a string that's longer than the defined size of the column.

Your choices for fixing this are also somewhat obvious - either make sure that the data that you store is no larger than the column (perhaps by truncating longer strings before storing them), or increase the size of the database column.