Data truncation error: Data too long for column

Overview

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


Symptoms

You may get this 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 too long
  • Encoding problem

Procedure

Data length too long

The error can be caused by a 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, the 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:

alter table person modify column name varchar(10);

However, you may not have the relevant permission to modify the database. In this case, you need to truncate the data before loading it into the database. For example, you can truncate the data such that the length corresponds to the limit, 5 in the following example, using an expression in the tMap component.

tmap.png


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:

SHOW TABLE STATUS LIKE 'person';

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.

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

    "useUnicode=true&characterEncoding=utf8"

     1_019.png

Version history
Revision #:
4 of 4
Last update:
‎06-15-2017 04:32 PM
Updated by:
 
Labels (1)
Tags (2)