View Issue Details

IDProjectCategoryView StatusLast Update
0004289SymmetricDSBugpublic2020-04-21 19:02
Reporterstvje1 Assigned Toelong  
Priorityhigh 
Status closedResolutionfixed 
Product Version3.11.2 
Target Version3.11.8Fixed in Version3.11.8 
Summary0004289: Oracle: rebuilding triggers can result in changed data not being captured
DescriptionSynchronizing triggers on Oracle consists of two steps:
1. drop trigger
2. create or replace trigger

This approach creates a timeslot without triggers, which means that changes to the source data will not be caputured during this timeslot.

Solution:
On Oracle the drop trigger step is not needed - just go directly to create or replace.
The create or replace action places an exclusive lock on the table while executing, ensuring that no data can be changed until the new trigger is in place and thereby ensuring that all data changes are captured by either the old version or the new version of the trigger.
Steps To ReproduceCreate a workload that changes source data.
While the workload is running rebuild the triggers.
Additional InformationAs the below log snip illustrates it is still possible to change the source data while the triggers get dropped and recreated:
2020-02-24 15:06:01,029 INFO [hq-000] [TriggerRouterService] [background-refresher-2] Synchronizing triggers for ORDERS
2020-02-24 15:06:01,573 INFO [hq-000] [OracleSymmetricDialect] [background-refresher-2] Creating ORDERS_SYM_INS_TRG trigger for ORDERS
2020-02-24 15:06:03,309 INFO [hq-000] [RouterService] [hq-000-job-10] Routed 2 data events in 23 ms
2020-02-24 15:06:03,628 INFO [hq-000] [OracleSymmetricDialect] [background-refresher-2] Creating ORDERS_SYM_UPD_TRG trigger for ORDERS
2020-02-24 15:06:03,985 INFO [hq-000] [OracleSymmetricDialect] [background-refresher-2] Creating ORDERS_SYM_DEL_TRG trigger for ORDERS
2020-02-24 15:06:04,181 INFO [hq-000] [TriggerRouterService] [background-refresher-2] Done synchronizing triggers for ORDERS
Tagsdialect: oracle, trigger

Relationships

related to 0004284 closedelong Sync Triggers: Should enhance syncing of triggers to not delete trigger if platform supports create or replace syntax 

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.11 233c2ec1

2020-04-10 13:29:09

admin

Details Diff
0004289: Oracle: rebuilding triggers can result in changed data not
being captured
Affected Issues
0004289
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/service/impl/TriggerRouterService.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/DatabaseInfo.java Diff File
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/oracle/OracleDdlBuilder.java Diff File

Issue History

Date Modified Username Field Change
2020-02-25 15:31 stvje1 New Issue
2020-02-25 15:31 stvje1 Tag Attached: trigger
2020-03-11 12:39 elong Tag Attached: dialect: oracle
2020-03-25 12:27 elong Assigned To => elong
2020-03-25 12:27 elong Status new => assigned
2020-03-25 12:27 elong Target Version => 3.11.8
2020-04-10 17:30 elong Status assigned => resolved
2020-04-10 17:30 elong Resolution open => fixed
2020-04-10 17:30 elong Fixed in Version => 3.11.8
2020-04-10 18:00 admin Changeset attached => SymmetricDS 3.11 233c2ec1
2020-04-10 19:11 elong Relationship added related to 0004284
2020-04-21 19:02 admin Status resolved => closed