View Issue Details

IDProjectCategoryView StatusLast Update
0004355SymmetricDSBugpublic2022-09-12 18:38
Reporterrichard@squarepeg.co.za Assigned Toelong  
Prioritynormal 
Status assignedResolutionreopened 
Product Version3.11.3 
Summary0004355: Duplicate Extract Files because multiple active trigger hist for same table
DescriptionWhen doing an initial extract on one of the schemas in my database, one of the tables (same one each time) is extracted twice. Result is of course duplicate entries on the receiving database. I have checked that the triggers are only registered once. I have cleaned the TEMP directories.

Here is the sym log show the table is extracted twice:
2020-04-18 10:34:28,819 INFO [home] [DataExtractorService] [home-extract-default-6] Starting request 30385 to extract table medical_exam_work_site into batches 9900016 through 9900037 for node IZIN3DVW.30-9C-23-A1-88-77.3
2020-04-18 10:34:34,415 INFO [home] [DataExtractorService] [home-extract-default-6] Done with request 30385 to extract table medical_exam_work_site into batches 9900016 through 9900037 for node IZIN3DVW.30-9C-23-A1-88-77.3
2020-04-18 10:34:34,416 INFO [home] [DataExtractorService] [home-extract-default-6] Starting request 30386 to extract table medical_exam_work_site into batches 9900038 through 9900059 for node IZIN3DVW.30-9C-23-A1-88-77.3
2020-04-18 10:34:36,422 INFO [home] [DataExtractorService] [home-extract-default-6] Done with request 30386 to extract table medical_exam_work_site into batches 9900038 through 9900059 for node IZIN3DVW.30-9C-23-A1-88-77.3
Steps To ReproduceUnfortunately, I have no way to create this scenario. It just one day started on a database that has been running without issue on the same version for months.
Additional InformationCentOS7, Postgres 9.2
Tagsdialect: postgresql

Activities

richard@squarepeg.co.za

2020-04-18 09:01

reporter   ~0001703

sym queries showing contents of sym_trigger and sym_extract_request for this table.
sym_trigger.csv (965 bytes)

elong

2020-05-21 16:34

developer   ~0001719

There are two extract requests for same table, and they have the same load_id and appear to have come from the same table reload request. If you have the trigger associated with multiple trigger routers, it would behave that way. You can see what trigger routers are configure like this:

select * from sym_trigger_router where trigger_id = 'IZIN3DVW/medical_exam_work_site';

richard@squarepeg.co.za

2021-05-30 15:18

reporter   ~0001945

I have finally managed to track down the reason for this issue, although not the root cause. For some reason there are 2 entries for the same table in sym_trigger_hist with a NULL inactive_time. This results in this table being included twice in sym_extract_request for an initial load (via "allTriggerHistSql" query).

richard@squarepeg.co.za

2021-05-30 15:19

reporter   ~0001946

allTriggerHistSql.txt (1,219 bytes)   
select trigger_hist_id, trigger_id, create_time, inactive_time from sym.sym_trigger_hist where trigger_id= 'IZIN3DVW/medical_exam_work_site' and source_schema_name = 'IZIN3DVW' order by trigger_id;
 trigger_hist_id |           trigger_id            |       create_time       |       inactive_time
-----------------+---------------------------------+-------------------------+----------------------------
             273 | IZIN3DVW/medical_exam_work_site | 2017-04-22 11:28:39.8   | 2017-08-03 19:26:16.866163
             529 | IZIN3DVW/medical_exam_work_site | 2017-08-03 19:26:16.828 | 2020-01-02 16:24:40.199501
            6256 | IZIN3DVW/medical_exam_work_site | 2020-01-02 16:24:40.095 | 2020-04-05 19:30:38.841414
           11594 | IZIN3DVW/medical_exam_work_site | 2020-04-05 19:25:58.115 | 2020-04-06 08:07:04.707563
           12701 | IZIN3DVW/medical_exam_work_site | 2020-04-06 08:07:04.523 | 2020-04-14 08:01:08.161381
           14740 | IZIN3DVW/medical_exam_work_site | 2020-04-14 08:01:07.862 | 2020-06-14 14:10:55.794913
           11593 | IZIN3DVW/medical_exam_work_site | 2020-04-05 19:24:56.171 |
           20584 | IZIN3DVW/medical_exam_work_site | 2020-06-14 14:10:54.359 |
(8 rows)
allTriggerHistSql.txt (1,219 bytes)   

Issue History

Date Modified Username Field Change
2020-04-18 08:46 richard@squarepeg.co.za New Issue
2020-04-18 08:46 richard@squarepeg.co.za Tag Attached: dialect: postgresql
2020-04-18 09:01 richard@squarepeg.co.za File Added: sym_trigger.csv
2020-04-18 09:01 richard@squarepeg.co.za File Added: sym_extract_request.csv
2020-04-18 09:01 richard@squarepeg.co.za Note Added: 0001703
2020-05-21 16:34 elong Assigned To => elong
2020-05-21 16:34 elong Status new => feedback
2020-05-21 16:34 elong Note Added: 0001719
2020-07-22 17:52 elong Status feedback => closed
2020-07-22 17:52 elong Resolution open => unable to reproduce
2021-05-30 15:18 richard@squarepeg.co.za Status closed => feedback
2021-05-30 15:18 richard@squarepeg.co.za Resolution unable to reproduce => reopened
2021-05-30 15:18 richard@squarepeg.co.za Note Added: 0001945
2021-05-30 15:19 richard@squarepeg.co.za Note Added: 0001946
2021-05-30 15:19 richard@squarepeg.co.za File Added: allTriggerHistSql.txt
2021-05-30 15:19 richard@squarepeg.co.za Status feedback => assigned
2022-09-12 18:38 elong Summary Duplicate Extract Files => Duplicate Extract Files because multiple active trigger hist for same table