View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0005312SymmetricDSBugpublic2022-05-26 15:512025-06-25 17:50
Reporterpsergey Assigned To 
Priorityhigh 
Status acknowledgedResolutionopen 
Product Version3.13.4 
Summary0005312: Incorrect conflict resolution if sync_on_incoming_batch = 1
DescriptionWhere are incorrect conflict resolution if sync_on_incoming_batch = 1 in the table sym_trigger and conflict resolve type is 'NEWER_WINS'.
Updating string should be different on both nodes.
Replication database: MS SQL 2016.

BUG: Old value does not changed to new value.

Note: Conflict is resolved successfully if sync_on_incoming_batch = 0.

Short reproducing description (full reproducing description with scripts is below in the section 'Steps To Reproduce'):
1. Tune two nodes corp004 and corp005.
2. Create test database Sym_ReplTest_2Nodes, create test replicating table and add string into the table.
3. Create bidirectional two nodes replication with sync_on_incoming_batch = 1.
4. Update test string on the node corp004.

Result:
Test string was updated on the node corp004 and was not updated on the node corp005 (conflict detection was logged on the node corp005).

Replication table values at both nodes after update:
Server Name id test_id rectime col1
corp004 1 41068A82-47EC-491C-8D6E-C5BF268066C3 2022-05-26 13:29:13.460 4
corp005 1 BD4C25F2-CF78-4A71-AA1A-9B44C68A54D1 2022-05-26 13:18:50.033 0

Conflict detection records on the node corp005 (symmetric.log):
2022-05-26 16:29:14,285 INFO [corp005-005] [AbstractDatabaseWriterConflictResolver] [corp005-005-dataloader-2] Conflict detected: conflict_corp004_2_corp005 in batch 5 at line 1 for table Sym_ReplTest_2Nodes.dbo.RSTable1
2022-05-26 16:29:14,286 INFO [corp005-005] [AbstractDatabaseWriterConflictResolver] [corp005-005-dataloader-2] Row data: "1","41068A82-47EC-491C-8D6E-C5BF268066C3","2022-05-26 13:29:13.460","4"
2022-05-26 16:29:14,286 INFO [corp005-005] [AbstractDatabaseWriterConflictResolver] [corp005-005-dataloader-2] Old data: "1","FBAC7A5C-85AC-4A6D-B42C-F5A3605100B7","2022-05-26 13:18:30.810","0"
2022-05-26 16:29:14,286 INFO [corp005-005] [AbstractDatabaseWriterConflictResolver] [corp005-005-dataloader-2] Resolve Type: NEWER_WINS
Steps To ReproduceFull reproducing description:

1. Tune two nodes corp004 and corp005.

node corp004: engine/corp004-004.properties:
engine.name=corp004-004
db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://localhost:1433/Sym_ReplTest_2Nodes;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880
db.user=SymmetricDsUser
db.password=Password
db.validation.query=select 1
registration.url=http://192.168.0.187:31415/sync/corp005-005
sync.url=http://192.168.0.188:31415/sync/corp004-004
group.id=corp004
external.id=004
job.routing.period.time.ms=500
job.push.period.time.ms=1000
job.pull.period.time.ms=1000
auto.registration=true
initial.load.create.first=true
log.conflict.resolution=true
job.purge.incoming.cron=0 0 */4 * * *
job.purge.outgoing.cron=0 0 */4 * * *
purge.retention.minutes=120

node corp005: engine/corp005-005.properties:
engine.name=corp005-005
db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://localhost:1433/Sym_ReplTest_2Nodes;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880
db.user=SymmetricDsUser
db.password=Password
db.validation.query=select 1
registration.url=
sync.url=http://192.168.0.187:31415/sync/corp005-005
group.id=corp005
external.id=005
job.routing.period.time.ms=500
job.push.period.time.ms=1000
job.pull.period.time.ms=1000
auto.registration=true
initial.load.create.first=true
log.conflict.resolution=true
job.purge.incoming.cron=0 0 */4 * * *
job.purge.outgoing.cron=0 0 */4 * * *
purge.retention.minutes=120

-----

2. Create test database Sym_ReplTest_2Nodes, create test replicating table and add string into the table.

Script for creating table:

create table dbo.RSTable1 (
 id int not null,
 constraint PK__RSTable1 primary key (id),
 test_id uniqueidentifier not null,
 rectime datetime not null default getutcdate(),
 col1 int not null default 0)
go
insert dbo.RSTable1 (id, test_id) select 1, newid();
go

-----

3. Create bidirectional two nodes replication with sync_on_incoming_batch = 1.

Script for creating replication:

use Sym_ReplTest_2Nodes;
GO

-- Clear and load SymmetricDS Configuration
delete from sym_conflict;
delete from sym_trigger_router;
delete from sym_trigger;
delete from sym_router;
delete from sym_channel where channel_id in ('forward', 'backward', 'peer');
delete from sym_node_group_link;
delete from sym_node_group;
delete from sym_node_host;
delete from sym_node_identity;
delete from sym_node_security;
delete from sym_node;

-- Channels
-- Channel "peer" for all tables
insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description)
values('peer', 1, 100000, 1, 'transactional data from one peer to another');

-- Node Groups
insert into sym_node_group (node_group_id) values ('corp005');
insert into sym_node_group (node_group_id) values ('corp004');

-- Node Group Links
-- Sends changes to other peer
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('corp005', 'corp004', 'P');
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('corp004', 'corp005', 'P');

-- Triggers
-- Triggers for tables on "peer" channel
insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('RSTable1','RSTable1','peer',current_timestamp,current_timestamp);

-- Triggers sync_on_incoming_batch
update sym_trigger set sync_on_incoming_batch = 1

-- Routers
-- Router sends all data from one peer to another
insert into sym_router
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('corp005_2_corp004', 'corp005', 'corp004', 'default',current_timestamp, current_timestamp);

insert into sym_router
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('corp004_2_corp005', 'corp004', 'corp005', 'default',current_timestamp, current_timestamp);

-- Trigger Routers
-- Send all items to all stores
insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('RSTable1','corp005_2_corp004', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('RSTable1','corp004_2_corp005', 100, current_timestamp, current_timestamp);

-- Conflicts
delete from sym_conflict;

insert into sym_conflict (conflict_id, target_channel_id, source_node_group_id, target_node_group_id, detect_type, resolve_type, ping_back, resolve_changes_only, resolve_row_only, create_time, last_update_time)
values ('conflict_corp004_2_corp005', 'peer', 'corp004', 'corp005', 'USE_CHANGED_DATA', 'NEWER_WINS', 'OFF', 0, 1, current_timestamp, current_timestamp);

insert into sym_conflict (conflict_id, target_channel_id, source_node_group_id, target_node_group_id, detect_type, resolve_type, ping_back, resolve_changes_only, resolve_row_only, create_time, last_update_time)
values ('conflict_corp005_2_corp004', 'peer', 'corp005', 'corp004', 'USE_CHANGED_DATA', 'NEWER_WINS', 'OFF', 0, 1, current_timestamp, current_timestamp);

-----

4. Update test string on the node corp004.

SQL request:

use Sym_ReplTest_2Nodes
go
set nocount on;
go
declare
 @col1 int = 4, -- server id
 @test_id uniqueidentifier, @maxid int;
set @test_id = newid();
update dbo.RSTable1 set test_id=@test_id, rectime=default, col1=@col1 where id=1;
go

-----

Result:
Test string was updated on the node corp004 and was not updated on the node corp005 (conflict detection was logged on the node corp005).

Replication table values at both nodes after update:
Server Name id test_id rectime col1
corp004 1 41068A82-47EC-491C-8D6E-C5BF268066C3 2022-05-26 13:29:13.460 4
corp005 1 BD4C25F2-CF78-4A71-AA1A-9B44C68A54D1 2022-05-26 13:18:50.033 0

Conflict detection records on the node corp005 (symmetric.log):
2022-05-26 16:29:14,285 INFO [corp005-005] [AbstractDatabaseWriterConflictResolver] [corp005-005-dataloader-2] Conflict detected: conflict_corp004_2_corp005 in batch 5 at line 1 for table Sym_ReplTest_2Nodes.dbo.RSTable1
2022-05-26 16:29:14,286 INFO [corp005-005] [AbstractDatabaseWriterConflictResolver] [corp005-005-dataloader-2] Row data: "1","41068A82-47EC-491C-8D6E-C5BF268066C3","2022-05-26 13:29:13.460","4"
2022-05-26 16:29:14,286 INFO [corp005-005] [AbstractDatabaseWriterConflictResolver] [corp005-005-dataloader-2] Old data: "1","FBAC7A5C-85AC-4A6D-B42C-F5A3605100B7","2022-05-26 13:18:30.810","0"
2022-05-26 16:29:14,286 INFO [corp005-005] [AbstractDatabaseWriterConflictResolver] [corp005-005-dataloader-2] Resolve Type: NEWER_WINS
Tagsconflict manager

Activities

psergey

2022-05-27 11:33

reporter   ~0002083

The bug can lead to tables inconsistency on two nodes if somebody updates the string on both nodes simultaneously.

Steps to reproduce:

1. Check test table on both nodes (test strings are the same on both nodes):

SELECT * FROM [Sym_ReplTest_2Nodes].[dbo].[RSTable1] WHERE id=1
Server_Name id test_id rectime col1
corp004 1 2B8659B3-5180-4D83-9A41-47F336E44E35 2022-05-27 09:54:20.793 4
corp005 1 2B8659B3-5180-4D83-9A41-47F336E44E35 2022-05-27 09:54:20.793 4

2. Update both tables simultaneously with different data using SQL request:

use Sym_ReplTest_2Nodes
go
set nocount on;
go
declare
 @col1 int = 0, -- server id
 @test_id uniqueidentifier, @maxid int;
if @@SERVERNAME = 'corp004' set @col1=4;
if @@SERVERNAME = 'corp005' set @col1=5;
set @test_id = newid();
update dbo.RSTable1 set test_id=@test_id, rectime=default, col1=@col1 where id=1;
go

3. Check test table on both nodes (test strings are different on both nodes):
SELECT * FROM [Sym_ReplTest_2Nodes].[dbo].[RSTable1] WHERE id=1

Server_Name id test_id rectime col1
corp004 1 29081664-5FB5-4D9B-860E-978579A50B19 2022-05-27 09:54:57.770 4
corp005 1 CEE78D8E-D7BC-4600-A09A-F4F66E4CA63C 2022-05-27 09:54:57.773 5

Conflict detection records on the node corp004 (symmetric.log):

2022-05-27 12:54:58,304 INFO [corp004-004] [AbstractDatabaseWriterConflictResolver] [corp004-004-dataloader-98] Conflict detected: conflict_corp005_2_corp004 in batch 199 at line 1 for table Sym_ReplTest_2Nodes.dbo.RSTable1
2022-05-27 12:54:58,304 INFO [corp004-004] [AbstractDatabaseWriterConflictResolver] [corp004-004-dataloader-98] Row data: "1","CEE78D8E-D7BC-4600-A09A-F4F66E4CA63C","2022-05-27 09:54:57.773","5"
2022-05-27 12:54:58,304 INFO [corp004-004] [AbstractDatabaseWriterConflictResolver] [corp004-004-dataloader-98] Old data: "1","2B8659B3-5180-4D83-9A41-47F336E44E35","2022-05-27 09:54:20.793","4"
2022-05-27 12:54:58,304 INFO [corp004-004] [AbstractDatabaseWriterConflictResolver] [corp004-004-dataloader-98] Resolve Type: NEWER_WINS

Conflict detection records on the node corp005 (symmetric.log):

2022-05-27 12:54:59,205 INFO [corp005-005] [AbstractDatabaseWriterConflictResolver] [corp005-005-dataloader-101] Conflict detected: conflict_corp004_2_corp005 in batch 197 at line 1 for table Sym_ReplTest_2Nodes.dbo.RSTable1
2022-05-27 12:54:59,205 INFO [corp005-005] [AbstractDatabaseWriterConflictResolver] [corp005-005-dataloader-101] Row data: "1","29081664-5FB5-4D9B-860E-978579A50B19","2022-05-27 09:54:57.770","4"
2022-05-27 12:54:59,205 INFO [corp005-005] [AbstractDatabaseWriterConflictResolver] [corp005-005-dataloader-101] Old data: "1","2B8659B3-5180-4D83-9A41-47F336E44E35","2022-05-27 09:54:20.793","4"
2022-05-27 12:54:59,205 INFO [corp005-005] [AbstractDatabaseWriterConflictResolver] [corp005-005-dataloader-101] Resolve Type: NEWER_WINS

Issue History

Date Modified Username Field Change
2022-05-26 15:51 psergey New Issue
2022-05-26 15:51 psergey Tag Attached: conflict manager
2022-05-27 11:33 psergey Note Added: 0002083
2025-06-25 17:50 cquamme Status new => acknowledged