"Data truncation: Data too long for column" causes


This article explains the probable causes for this problem and offers a troubleshooting procedure.



You may get the following Java exception when inserting data using a database output component, like tMysqlOutput.

Data truncation: Data too long for column

The probable causes are:

  • Data length is too long
  • Encoding problem



Data length is too long

The error can be caused by data length greater than the maximum size of the field. Using MySQL as an example, a table definition follows:

create table person (id int(5), name varchar(5));

The size of the name field is 5. If you insert a string such as "Talend", with length greater than 5, this exception will be thrown.

Data truncation: Data too long for column

To resolve this error, change the field size. For example, you can change the size of the field to 10 by executing the following SQL command:

alter table person modify column name varchar(10);

You may not have the relevant permission to modify the database, so you must truncate the data before loading it into the database. For example, you can truncate the data so the length corresponds to the limit (5 in the following example) using an expression in the tMap component.



Encoding problem

If the same problem occurs even though the length of data being inserted is smaller than maximum size of the field, the character encoding of the inserted data might be inconsistent with the table (or column) encoding.


To resolve this error, add an external parameter to specify the character encoding in the database connection URL. Using a MySQL database for example, you can resolve the error as follows:

  1. Execute the following SQL command to check the Collation encoding of the table (person is the table name, for example):


    In the execution result, there is a Collation field that stores the table collation encoding, such as utf8_ general_ci, where UTF-8 is the table encoding.

  2. On the advanced DB components setting panel, set the Additional JDBC Parameters field to:


Version history
Revision #:
8 of 8
Last update:
‎06-13-2019 02:34 AM
Updated by:
Labels (2)
Six Stars

This helped me to realize that my Data Truncation problem was based on a inconsistent data that I was trying to insert. I wasted time looking for job/components problems when in my case was all about incorrect input data.


Thanks so much!