View Revisions: Issue #6685
Summary | 0006685: Purge Service recaptures an Insert for stranded data after record had been deleted | ||
---|---|---|---|
Revision | 2024-12-23 19:36 by mdrouard | ||
Description | The purge service is missing deletes when trying to recapture data from a data gap on long running transactions. If a transaction is active for longer than the duration of the parameter routing.stale.dataid.gap.time.ms, data will be marked as stranded. Data that is processed during this time will not be inserted normally, instead this data will be processed as a recapture when the purge job runs. If there is an insert and then a delete of a row that both are outside of the routing.stale.dataid.gap.time.ms length then the delete will not be properly processed in the recapture. |
||
Revision | 2024-12-23 19:36 by mdrouard | ||
Description | The purge service is missing deletes when trying to recapture data from a data gap on long running transactions. If a transaction is active for longer than the duration of the parameter routing.stale.dataid.gap.time.ms, data will be marked as stranded. Data that is processed during this time will not be inserted normally, instead this data will be processed as a recapture when the purge job runs. If there is an insert and then a delete of a row that both are outside of the routing.stale.dataid.gap.time.ms length then the delete will not be properly processed in the recapture. This can result in data being present on target database and not source |
||
Revision | 2024-12-26 22:08 by pbelov | ||
Description | Purge Service recaptures an Insert for stranded data after record had been deleted If a transaction is active for longer than the duration of the parameter routing.stale.dataid.gap.time.ms, data will be marked as stranded. Data in such long database transaction will not be processed normally, instead this data will go through the recapture process when the purge job runs. If there is a stranded Insert (or Update) but the original row had been deleted, then the Purge job will incorrectly keep the original insert (or update) command. This results in data being present on target database and not at the source. |
||
Revision | 2024-12-23 19:36 by mdrouard | ||
Steps To Reproduce | 0. Alter routing.stale.dataid.gap.time.ms to be a period that you are willing to wait to create a data gap. 1. Turn off autocommit on SQL transactions. 2. Run an insert on a source table and do not commit. 3. Run a delete on the record that was inserted after a short period and do not commit. 4. Wait for the roting stale data gap time to pass. 5. Insert a record on a different console and commit it normally before committing the slow transaction 6. Allow routing to run on the fast transaciton and pick up the data gap 7. Commit the transaction that was slow running. 8. Allowing routing/purge service to run 9. Select from the table on source and client and compare the difference. |
||
Revision | 2024-12-26 22:08 by pbelov | ||
Steps To Reproduce | 0. Alter routing.stale.dataid.gap.time.ms to be a period that you are willing to wait to create a stranded data (data gap). 1. Turn off auto-commit on SQL transactions (for testing). 2. Run an insert on a source table and do not commit just yet. 3. Wait for time, exceeding the routing.stale.dataid.gap.time.ms value. 4. Commit the Insert transaction. 5. Run a Delete command targeting the same record that was inserted and commit immediately. 6. Allow the Routing job to run and pick up the data gap. 7. Run manually or wait until the Purge Outgoing job runs. 9. Select from the table on source and client and compare the difference. Observe record missing on the source database but present on the target. |
||
Revision | 2024-12-23 19:36 by mdrouard | ||
Additional Information | |||
Revision | 2024-12-27 13:43 by pbelov | ||
Additional Information | Set up: two SymmetricDS engines with client-server push configuration using two catalogs on the same PostgreSQL database: qaserver catalog for SymmetricDS server engine qaclient catalog for SymmetricDS client engine SET routing.stale.dataid.gap.time.ms=8000 (8 seconds) SET Routing job to run every 5 seconds SET Push job to run every 4 seconds SET Purge Outgoing job to only run at midnight -- Table with test data: CREATE TABLE qaserver.atest_6685_stale_gap ( cl1key INTEGER NOT NULL, clVARCHAR500 VARCHAR(500) NOT NULL, PRIMARY KEY (cl1key) ); -- Test slow INSERT: BEGIN transaction; INSERT INTO qaserver.atest_6685_stale_gap VALUES( 44, '44slow'); SELECT pg_sleep(15); COMMIT transaction; -- Test slow UPDATE: BEGIN transaction; UPDATE qaserver.atest_6685_stale_gap SET clVARCHAR500= '44slow-updated44' where cl1key=44; SELECT pg_sleep(15); COMMIT transaction; -- Test slow DELETE: BEGIN transaction; DELETE FROM qaserver.atest_6685_stale_gap where cl1key=44; SELECT pg_sleep(15); COMMIT transaction; -- Compare data in tables: SELECT 'source' as tbl, * FROM qaserver.atest_6685_stale_gap UNION ALL SELECT 'target' as tbl, * FROM qaclient.atest_6685_stale_gap order by 1,2; -- Review batches and data entries: SELECT b.batch_id, case when b.status is null then 'UNrouted' else b.status end as status , d.* FROM qaserver.sym_data d left outer join qaserver.sym_data_event e on e.data_id = d.data_id left outer join qaserver.sym_outgoing_batch b on e.batch_id = b.batch_id WHERE table_name='atest_6685_stale_gap' and d.data_id >=6961 or d.data_id = (SELECT Max(data_id) FROM qaserver.sym_data where table_name<>'atest_6685_stale_gap' ) order by data_id desc ; |