View Issue Details

IDProjectCategoryView StatusLast Update
0003712SymmetricDSBugpublic2018-10-25 15:21
ReportergwilmerAssigned To 
Prioritynormal 
Status closedResolutionduplicate 
Product Version3.9.12 
Target Version3.10.0Fixed 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]

Relationships

duplicate of 0003711 closedjosh-a-hicks Table reload request for a table with a foreign to itself and a data row where the row fk is to itself doesn't load any data 

Activities

hanes

2018-10-25 15:20

developer   ~0001297

Last edited: 2018-10-25 15:21

View 2 revisions

Per developer this should be fixed as a result of 3711

Issue History

Date Modified Username Field Change
2018-09-08 09:53 gwilmer New Issue
2018-09-13 14:39 hanes Status new => confirmed
2018-10-10 14:10 hanes Target Version => 3.10.0
2018-10-25 15:20 hanes Status confirmed => closed
2018-10-25 15:20 hanes Resolution open => duplicate
2018-10-25 15:20 hanes Note Added: 0001297
2018-10-25 15:20 hanes Relationship added duplicate of 0003211
2018-10-25 15:21 hanes Note Edited: 0001297 View Revisions
2018-10-25 15:21 hanes Relationship added duplicate of 0003711
2018-10-25 15:21 hanes Relationship deleted 0003211