Skip to main content

Official Support Articles

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!

Qlik Replicate: Apply conflicts do not apply to Store Changes tables

cancel
Showing results for 
Search instead for 
Did you mean: 
KellyHobson
Support
Support

Qlik Replicate: Apply conflicts do not apply to Store Changes tables

 

Introduction

 

 The Apply Conflict settings which can be configured under Task Settings -> Error Handling -> Apply Conflicts can vary based on your business needs. For example, UPSERT mode is a handy setting for preventing duplicates or inserting missing updates.

These settings apply only to changes captured with 'Apply Changes' task settings. They do not apply to changes captured with 'Store Changes' enabled. This is because Store Changes by design captures and treats any change as an INSERT.

In the example below, I'll show examples of the same behavior on a Apply Changes only vs a Store Changes only task with mySQL source and target.

 

Set up

 

Original source table:

 

original_source_Table.png

 

applyconflictsON1.png

 

Apply Changes Test

 

For the first part of the article, the task is configured with 'Apply Changes' only ON with defaults. 

 

applyconflictsON2.png

We have enabled UPSERT mode.

applyconflictsON3.png

 

#test 'No record found for applying an UPDATE'
#insert a new value on source table
INSERT INTO kelly.Persons2 VALUES(36,'molly','2010-11-01 05:00:00');

#manually delete from target table
delete from replicate1.Persons2 where PersonID=36;

#update the source table
update kelly.Persons2 set lastname='WALLEY' where PersonID=36;

 

test1.png

 

 

Source table post test:

 

source_table.png

Target table post test:

 

target_table.png

 

Store Changes Test 

 

*Note deleted record with PersonID=36 from both tables before continuing. 

For the second section the task is configured with 'Store Changes' only ON with defaults.  Note UPSERT settings are still set but they will not do anything. 

 

storechangesON1.png

storechangesON2.png

 

 

#test 'No record found for applying an UPDATE'
#insert a new value on source table
INSERT INTO kelly.Persons2 VALUES(36,'molly','2010-11-01 05:00:00');

#skip manual delete on target since we are not capturing changes on target table

#update the source table
update kelly.Persons2 set lastname='WALLEY' where PersonID=36;

 

 

storechangesON3.png

 

From the output of the ct table, we see 3 records. The first is the original insert, the other 2 reflect the update activity but are treated as inserts. Thus, with UPSERT enabled, we will all records as inserts with unique header_stream_positions rather than only the last record with the UPDATE details.

 

 

Environment

 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

 

Version history
Last update:
a week ago
Updated by:
Contributors