Search docs.molo17.com
    Troubleshooting guide Pipelines

    Fix target length and truncation errors

    The target database refuses a value because it does not fit the column: Oracle answers ORA-12899: value too large for column, SQL Server answers String or binary data would be truncated. The value is not always longer in characters. Text from an IBM i can grow in bytes when it reaches a UTF-8 target, and a column sized in bytes then fills up before it reaches its character count. This guide shows how to name the column, measure what it must hold, widen it, and size new target columns so that the error does not come back.

    In this guide

    You will learn how to find the table and the column behind the error, measure the longest value in characters and in bytes, widen the column on Oracle and on SQL Server, size the columns of a table that Gluesync creates, why IBM i text grows when it becomes UTF-8, what BYTE and CHAR mean in an Oracle column, and how to recognise the one truncation error that is not about the column at all.

    Quick information

    Goal

    Stop a length or truncation error on the target and size the columns so that it does not return

    Audience

    Gluesync administrators and database administrators

    Level

    Intermediate

    Time required

    About 30 minutes, plus the time of the ALTER TABLE on a large table

    Area

    Control Plane → pipeline → entity → Compare DDL, and Field mapping → Length

    Applies to

    Gluesync 2.3

    Before you begin

    Make sure that:

    1. You can open the pipeline in the Control Plane and read the Notifications and alerts;

    2. You can run SQL on the target database, for example with Query Studio, and you may change the table (ALTER TABLE);

    3. You know the source and the target database type. This guide covers Oracle and SQL Server targets, and IBM i as a source of text that grows in bytes.

    The sizes, defaults and caps in this guide come from the way Gluesync builds the CREATE TABLE of an Oracle and of a SQL Server target. Other targets size columns in their own way: for PostgreSQL see Map IBM i data types.

    Guide steps

    Name the table and the column

    UI

    1. Open the entity that is in Error and read the message. On Oracle it names the column and gives the two sizes. On SQL Server 2019 and later it names the table, the column and the value that did not fit. Older SQL Server versions give only the text String or binary data would be truncated without the column;

    2. If the message does not name the column, select Compare DDL on the entity detail page. The source CREATE TABLE is on the left and the target on the right: the column in error is the one that is shorter on the target;

    3. Write down the table, the column, the length on the source and the length on the target.

    Expected result

    You know which column fails and how long it is on both sides. If both lengths are equal, the value is longer in bytes than in characters: read the section IBM i text grows when it becomes UTF-8. If the target is longer than the source and the error still appears, read The error appears although the target column is wide enough.

    The page Prepare a SQL Server target shows the banner Target write failed and the notification String or binary data found truncated that name the column on a SQL Server target.

    Measure what the column must hold

    SQL

    Do this on the target, because the rows that failed are not there yet. On the source, a check such as WHERE LENGTH(col) > 70 on an IBM i CHAR(70) column never finds a row: a fixed-length column always has its declared length, blanks included.

    1. Widen the column to a generous value first (the next step shows how), so that the entity can write the rows that failed;

    2. When the entity is Active again, measure the longest value in characters and in bytes.

      Oracle:

      SELECT MAX(LENGTH(line_text))  AS chars,
       MAX(LENGTHB(line_text)) AS bytes
FROM   <schema>.<table>;

      SQL Server:

      SELECT MAX(LEN(note))        AS chars,
       MAX(DATALENGTH(note)) AS bytes
FROM   <schema>.<table>;

    3. Choose the final length from the larger of the two numbers that your column type counts (bytes for VARCHAR2(n BYTE) and varchar(n), characters for VARCHAR2(n CHAR) and, in UTF-16 units, for nvarchar(n)), and add room to grow.

    Expected result

    You have two numbers for each column that failed. If bytes is larger than chars, the column was sized in characters but counts bytes.

    Widen the target column

    SQL

    Run the statement on the target, in a maintenance window if the table is large.

    Oracle, sized in characters so that the limit follows the text and not the encoding:

    ALTER TABLE <schema>.<table> MODIFY (line_text VARCHAR2(70 CHAR));

    Oracle, sized in bytes:

    ALTER TABLE <schema>.<table> MODIFY (line_text VARCHAR2(140 BYTE));

    SQL Server:

    ALTER TABLE <schema>.<table> ALTER COLUMN note varchar(300) NULL;

    Then look at the entity in the Control Plane. On SQL Server the entity retries by itself and writes the rows that waited, with no restart. If an entity stays in Error after the ALTER TABLE, stop it and start it again with Where it stopped, so that it continues from its last checkpoint. Do not use Now: it skips the changes made while the entity was stopped (see Find and fix row differences).

    Expected result

    The entity returns to Active and the notification does not come back. The rows that failed are on the target: compare the counts with Count both on the entity detail page.

    Do not change the length in the Field mapping dialog of an entity that already runs just to follow an ALTER TABLE: Edit mapping followed by Confirm stops the entity and asks for a new resync. Alter the table in the database, as above.

    If you cannot widen the column, shorten the value in transit with a UDF (there is no built-in function that cuts text), or filter the long rows out with data filters.

    Size the columns of a table that Gluesync creates

    UI

    Do this before you confirm the Create table statement, so that the first long value does not fail weeks later.

    1. In the Field mapping dialog, open the Target type and Length of every text column and compare them with the table in How Gluesync sizes the columns it creates;

    2. For a column that holds accents, Chinese or other non-ASCII text, give the target a length in characters (VARCHAR2(n CHAR) on Oracle) or a Unicode type (nvarchar on SQL Server), or multiply a byte length by the growth of your text (see IBM i text grows when it becomes UTF-8);

    3. For a column of unknown or very large length, choose nvarchar with Length -1 on SQL Server (MAX);

    4. Read the generated CREATE TABLE, edit it when a length is not right, and confirm.

    Expected result

    The statement shows, for every text column, a length that is at least as large as the longest value you measured, in the unit that the column counts.

    IBM i text grows when it becomes UTF-8

    An IBM i CHAR(n) or VARCHAR(n) column with a single-byte EBCDIC CCSID (for example 37 or 280) holds up to n characters, one byte each. Gluesync converts every character value to Unicode text, as described in AS/400 to MS SQL Server: character encoding Q&A. The target database then stores that text in its own encoding. When the target is UTF-8 and the column counts bytes, the same text can take more bytes than it took on the IBM i:

    Character EBCDIC single-byte CCSID UTF-8

    Letters and digits of ASCII

    1 byte

    1 byte

    Accented letters such as é, ü, ñ

    1 byte

    2 bytes

    Euro sign, in the CCSIDs that have it

    1 byte

    3 bytes

    Chinese, Japanese and Korean ideographs

    2 bytes, in a double-byte CCSID

    3 bytes

    Emoji

    not in the CCSID

    4 bytes

    A 70-character text that has two accented letters needs 72 bytes in UTF-8. In a VARCHAR2(70) column sized in bytes it fails with ORA-12899: value too large for column …​ (actual: 72, maximum: 70), although the text has 70 characters and the source column is CHAR(70). The same happens on SQL Server for a varchar(n) column with a UTF-8 collation, where each é takes two bytes.

    Check the encoding of your target:

    -- Oracle
SELECT parameter, value FROM nls_database_parameters
WHERE  parameter IN ('NLS_CHARACTERSET', 'NLS_LENGTH_SEMANTICS');
    -- SQL Server
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS collation;

    A character set such as AL32UTF8 stores text in UTF-8. A single-byte character set or a non-UTF-8 collation stores a Western accented letter in one byte, and replaces a character that its code page does not have (see the Q&A page above).

    Fix it as in the steps: size the column in characters, or in more bytes than characters. For Chinese text, plan for 3 bytes per ideograph.

    ORA-12899: value too large for column

    What you see. The entity is in Error and the log has ORA-12899: value too large for column "<schema>"."<table>"."<column>" (actual: 72, maximum: 70), for example in the line Error writing transaction on target for entity …​ java.sql.BatchUpdateException: ORA-12899. Oracle gives the size of the value (actual) and the size of the column (maximum), in the unit of the column.

    Why it happens. The value is larger than the column. Three cases are usual:

    • The target column is narrower than the source column;

    • The column is declared in bytes (VARCHAR2(70 BYTE)) and the text has more bytes than characters, see IBM i text grows when it becomes UTF-8;

    • The table was created by Gluesync and the column took the default of the database. The CREATE TABLE that Gluesync generates writes VARCHAR2(70), with no BYTE and no CHAR, so the column follows the parameter NLS_LENGTH_SEMANTICS of the database. That parameter is BYTE unless your database administrator changed it.

    What to do.

    1. Read the declared unit and sizes of the column:

      SELECT column_name, data_type, data_length, char_length, char_used
FROM   all_tab_columns
WHERE  owner = '<SCHEMA>' AND table_name = '<TABLE>'
AND    data_type LIKE '%CHAR%'
ORDER  BY column_id;

      char_used is B for a column in bytes and C for a column in characters; data_length is in bytes and char_length in characters;

    2. Widen the column as in the step Widen the target column, in characters or in more bytes. Oracle limits a VARCHAR2 to 4000 bytes unless the database uses MAX_STRING_SIZE=EXTENDED, whichever unit you choose;

    3. For the tables that Gluesync creates next, edit the length in the Create table statement, see the step Size the columns of a table that Gluesync creates.

    Check that it is fixed. The entity is Active, and SELECT MAX(LENGTHB(<column>)) FROM <table> is not larger than data_length of the column.

    SQL Server: String or binary data would be truncated (errors 2628 and 8152)

    What you see. The entity is in Error with a TargetWritingException and the message String or binary data would be truncated. SQL Server returns it as error 8152 on older versions and compatibility levels, with no table and no column. SQL Server 2019 and later return error 2628, String or binary data would be truncated in table '<table>', column '<column>'. Truncated value: '<value>'., which names the column.

    Why it happens. A value is larger than its target column, in bytes for varchar(n) and varbinary(n), in UTF-16 code units for nvarchar(n). Ten é do not fit in a varchar(10) column with a UTF-8 collation. Ten emoji do not fit in an nvarchar(10) column.

    What to do.

    1. Find the column, as in the first step. With error 8152, compare the widths with Compare DDL and with the queries of Prepare a SQL Server target;

    2. Widen the column as in the third step. The entity retries by itself;

    3. If the column is already as wide as the source column, read the next section.

    Check that it is fixed. The entity returns to Active and SELECT MAX(DATALENGTH(<column>)) FROM <table> is not larger than the byte length of the column.

    The error appears although the target column is wide enough

    What you see. String or binary data would be truncated on a SQL Server target, on a binary or varbinary column (for example a source CHAR …​ FOR BIT DATA column that holds hexadecimal data), with a target column that is exactly as long as the source column. The first step finds no difference between the two lengths.

    Why it happens. When Gluesync writes a large batch of changes to SQL Server, it stages the rows in a temporary table. In some releases that table declared its binary and varbinary columns without a length, and SQL Server created them one byte long, so every longer value was rejected. The data and the target column were correct. The temporary table now declares the length of the column.

    What to do.

    1. Update Gluesync to the latest release of your line (2.2 or 2.3) from Platform updates, and check the version shown in the Control Plane;

    2. Do not reload the table. The entity continues from the batch that it was stuck on;

    3. Start the entity if it is stopped.

    Check that it is fixed. The entity returns to Active and the binary column has the same value as on the source. A row count that is larger on the target than on the source is a different problem: see Choose keys for tables without a primary key.

    How Gluesync sizes the columns it creates

    For an Oracle or a SQL Server target, Gluesync copies the length of the source column into the CREATE TABLE. It does not enlarge the length for UTF-8. When the source length is unknown or larger than the limit of the target type, it uses the limit:

    Target Text column Length used

    Oracle

    CHAR, NCHAR

    Source length, at most 2000

    Oracle

    VARCHAR, VARCHAR2, NVARCHAR2, and RAW (binary)

    Source length, at most 32767. The unit follows NLS_LENGTH_SEMANTICS; a database without MAX_STRING_SIZE=EXTENDED refuses more than 4000 bytes with ORA-00910

    SQL Server

    varchar, char

    Source length, at most 8000. Unknown length: MAX for varchar, 8000 for char

    SQL Server

    nvarchar

    Source length, at most 4000. Unknown length: MAX

    SQL Server

    binary, varbinary

    Source length. Unknown length (-1): MAX

    Two consequences follow. A source column longer than the cap of the target type is created at the cap: for example a source VARCHAR(10000) becomes varchar(8000) on SQL Server, so set the length to MAX before you confirm. And a text column in characters on the source becomes a column of the same number, in bytes or in characters according to the target, so a UTF-8 target needs the check of the section IBM i text grows when it becomes UTF-8.

    Verify the result

    You have successfully fixed the error if:

    • The entity is Active and no Target write failed or ORA-12899 notification appears for it;

    • For each column that failed, the longest value on the target, in the unit of the column, is smaller than the column length;

    • Count both shows the same number of rows on the source and on the target, or the difference that you expect;

    • The CREATE TABLE statements of the tables that Gluesync creates next have lengths that you checked.

    Troubleshooting

    Problem Possible cause What to do

    ORA-12899 with actual larger than maximum by a few units, and the source column has the same length as the target

    The target column counts bytes and the text has accented or non-Latin characters

    Change the column to VARCHAR2(n CHAR) or to a larger byte length, see ORA-12899: value too large for column

    The check WHERE LENGTH(col) > n finds no row on the IBM i

    A CHAR(n) column always has the length n

    Measure on the target with LENGTHB or DATALENGTH, as in the second step

    String or binary data would be truncated without a column name

    SQL Server 2017 or earlier, or an older compatibility level (error 8152)

    Compare the widths with Compare DDL and the queries of the SQL Server guide

    The same error on a varbinary column that is as long as the source

    The temporary table of an older release had binary columns of one byte

    Update Gluesync, see The error appears although the target column is wide enough

    After the ALTER TABLE the entity stays in Error

    The entity did not retry yet, or the error comes from another column

    Wait a few seconds, then stop and start it with Where it stopped, and read the new message

    The created table has varchar(8000) where the source has a longer column

    The source length is above the cap of the target type

    Set the length to MAX in Field mapping before you create the table

    Oracle refuses the CREATE TABLE with ORA-00910

    A length above 4000 bytes on a database without MAX_STRING_SIZE=EXTENDED

    Choose 4000 or less, or ask the database administrator to enable the extended size

    ? instead of a character in the target text

    The target column or the collation has no code point for it

    Use a Unicode type (nvarchar) or a UTF-8 character set, see the Q&A page of the first section

    Informix target, snapshot: Could not insert new row into the table. [SQLState=IX000, errorCode=-271] while the dbspaces have free space

    The dbspace of the table uses a 4 KB page size

    In the reported case the error ended after the page size was raised to 16 KB. Ask your Informix administrator to check the page size of the dbspace of the table

    Related guides

    Contact MOLO17 Support if you need assistance with a length or truncation error.