View Issue Details

IDProjectCategoryView StatusLast Update
0003712SymmetricDSBugpublic2018-09-13 14:39
ReportergwilmerAssigned To 
Prioritynormal 
Status confirmedResolutionopen 
Product Version3.9.12 
Target VersionFixed in Version 
Summary0003712: Syncing a table with FK to itself and then issuing a delete * from table on src results in FK error on target
DescriptionIf:
  - you are synchronizing a table that contains a foreign key to itself,
  - you have rows in the source table
  - you issue a delete * from table on the source
then:
  - data (deletes) are captured at the source in an order that doesn't consider fk dependencies
  - the batch results in an fk error at the target
Steps To Reproduce1. create the following table:

CREATE TABLE "TEST"(
    "ID" INTEGER NOT NULL,
    "NAME" CHAR(50) NULL,
    "PARENT_ID" INTEGER,
    PRIMARY KEY ("ID")
);
ALTER TABLE "TEST"
    ADD CONSTRAINT "CONSTRAINT_273C" FOREIGN KEY ("PARENT_ID") REFERENCES "TEST" ("ID");

2. create a simple 2 node sync scenario with this table flowing from server to client

3. Insert the following data into the table:

insert into test values (1,'parent',null)
insert into test values (2,'child',1)
insert into test values (3,'grandchild',2)

4. allow the above data rows to sync from server to client

5. issue a delete * from table on the source node

6. note the capture order of the batch

data_id data_event_type table_name pk_data
245 DELETE TEST "1"
246 DELETE TEST "2"
247 DELETE TEST "3"

7. When data_id 245 is attempted to be deleted on the target, an FK error is received as data_id 246 has fk to 245

Error Message:Referential integrity constraint violation: "CONSTRAINT_273C: PUBLIC.TEST FOREIGN KEY(PARENT_ID) REFERENCES PUBLIC.TEST(ID) (1)"; SQL statement: delete from "DEMO-STORE-001"."PUBLIC"."TEST" where "ID" = ? [23503-176]

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2018-09-08 09:53 gwilmer New Issue
2018-09-13 14:39 hanes Status new => confirmed