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 ;