Uploaded image for project: 'Pentaho Data Integration - Kettle'
  1. Pentaho Data Integration - Kettle
  2. PDI-7288

Synchronize after merge doesnt handle systems that perform renames as a delete then insert - causes database constraint errors

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Severity: High
    • Resolution: Won't Fix
    • Affects Version/s: 4.3.0 GA (4.5.0 GA Suite Release)
    • Fix Version/s: 5.1.0 GA
    • Component/s: Step
    • Labels:
    • Story Points:
      8
    • PDI Sub-component:
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.
    • Sprint Team:
      Pervach

      Description

      Say you have this in your table

      ID: 1
      NAME: DAN

      And in your input stream you have this:

      ID: 2, NAME: DAN, change_field: new
      ID: 1, NAME: DAN, change_field: deleted

      If you have a unique constraint on NAME then this will generate an error.

      So I tried to change the ordering in the code, and thought I had a solution but it didnt work - i tried re-ordering the code around line 374 in SynchronizeAfterMerge.java, but that didnt help ( i.e. do the delete first )

      But that idea, to do deletes first, is only a partial fix to the specific case above. Really wouldnt it be better if it used a single transaction for all the updates/inserts/deletes and then you'd never have this problem?

      SQL for the table:

      CREATE TABLE "INTERFACE_STAGING"."TEST_SAM_DAN"
      ( "ID" NUMBER PRIMARY KEY,
      "NAME" VARCHAR2(2000 CHAR),
      );
      CREATE UNIQUE INDEX "idx_sam_dan" ON TEST_SAM_DAN ( NAME )

        Attachments

          Activity

            People

            Assignee:
            dzmitry_by Dzmitry Prakapenka (Inactive)
            Reporter:
            camdk3 Dan Keeley (codek)
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: