View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0004355 | SymmetricDS | Bug | public | 2020-04-18 08:46 | 2022-09-12 18:38 |
Reporter | richard@squarepeg.co.za | Assigned To | elong | ||
Priority | normal | ||||
Status | assigned | Resolution | reopened | ||
Product Version | 3.11.3 | ||||
Summary | 0004355: Duplicate Extract Files because multiple active trigger hist for same table | ||||
Description | When 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 Reproduce | Unfortunately, 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 Information | CentOS7, Postgres 9.2 | ||||
Tags | dialect: postgresql | ||||
|
sym queries showing contents of sym_trigger and sym_extract_request for this table. |
|
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'; |
|
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). |
|
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) |
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 |