Data Replication

 View Only
Expand all | Collapse all

IBM CDC Replication getting SQL Error code 1172, SQL state 42000

  • 1.  IBM CDC Replication getting SQL Error code 1172, SQL state 42000

    Posted Thu April 01, 2021 12:39 AM
    I am replicating data from AS400 to MySQL with IBM CDC Replication.

    Source: AS400 (CDC Engine for DB2 for i) ==> Target: MySQL (CDC Engine for FlexRep)

    Then I have created a subscription with the following table mapping as mirror replication:

    source.item ==> target.item

    Apparently, source.item is from AS400 and target.item is from MySQL.
    target.item has unique key constraint on COL1 to COL8.

    When I run this subscription, I am getting the following error:

    Failed to add a row to table target.item. COL1: [IT] COL2: [2] COL3: [T999] COL4: [H203 ] COL5: [ ] COL6: [20180831] COL7: [9999999] COL8: [FPL].
    Database operation failed.
    A SQL exception has occured. The SQL error code is '1172'. The SQL state is: 42000. The error message is:
    Result consisted of more than one row.

    At first I was wondering if there are duplicated records in source table with COL1='IT' COL2='2' COL3='999' COL4='H203 ' COL5=' ' COL6='20180831' COL7='999999' COL8='FPL', since replicating duplicated key value records will violate the unique key constraint in target, but then there is only one record with such key values in the source table.

    One more thing to mention is that we have a derived column expression in the table mapping:

    NEW_COL ==> %GETCOL(COL_WANTED, "source.lookup", "NOT FOUND", DESCRPF, 5, COL1, COL2, COL3, COL4, COL5)

    Basically we are trying to lookup the column COL_WANTED from source.lookup with matching value of COL1 to COL5.
    I reckon that it is possible for this "joining/lookup" operation to yield multiple result records causing the error.
    But then I have taken away this derived column expression and the same error still prompts.

    Any suggestion will be greatly appreciated. Thanks in advance.

    Kevin Lee

  • 2.  RE: IBM CDC Replication getting SQL Error code 1172, SQL state 42000

    Posted Thu April 01, 2021 05:06 AM
    Hello, broadly speaking the following are possible causes for a duplicate key error on the target:

    1) Journal entries are replicated twice, usually through some operational error associated with refreshing and manually setting the CDC bookmark 
    2) The set of columns on the target that comprise the primary key or unique index constraint are not sufficient to make the data unique on the target
    3) The set of columns on the target is correct, but one or more columns are specified too short to hold the data and the resulting truncation results in a loss of uniqueness (not just obvious things like trying to map a CHAR 40 to a VARCHAR 30, but also where conversion to Unicode results in the number of bytes required exceeding the number of characters specified (e.g. the source takes 2 bytes and the target unicode takes 4 bytes and the column is defined as a number of bytes rather than characters)
    4) The set of columns on the target is correct and appropriately specified to hold all the data, but the data itself loses uniqueness on the target: this  can be true of say timestamp columns where the target timestamp is not as precise to  sub-second divisions as the source - not the issue here though
    5) Product issue
    6) Independent update of the target table

    I would suggest that you review your operational procedures to see if possible cause #1 applies. I would then query the target database to see if the row already exists. You could try refreshing the table. If the table is refreshed OK then it is probably an operational issue. Ultimately you may have to open a case with Product Support

    Robert Philo