View Issue Details

IDProjectCategoryView StatusLast Update
0003901SymmetricDSBugpublic2020-06-17 15:26
Reporterj.mackay@backinmotion.com.au Assigned Topmarzullo  
Priorityurgent 
Status closedResolutionfixed 
Product Version3.10.0 
Target Version3.11.0Fixed in Version3.11.0 
Summary0003901: MSSQL: Updating multiple rows in source table results in reversed rows in destination table
DescriptionThe update triggers are being joined by row_number() instead of Primary Key ID.

When we update several rows in a table in one UPDATE, under some circumstances SymmetricDS changes the row data for all affected primary keys. e.g.
for the table: ClientID (PK), Practice ID, Name
1, 23, 'Jeff'
2, 25, 'Andrew'
3, 24, 'John'
If we run Update Client SET PracticeID = PracticeID, and the bug gets triggered, we end up with:
1, 24, 'John'
2, 25, 'Andrew'
3, 23, 'Jeff'

This consistently happens when we update a large number of rows (for example "Update Client SET PracticeID = PracticeID" on a 29162 row table triggers this every time), but we've seen it intermittently when updating as few as 2 rows.

We have found that this has been occuring on 3.9.10 as well as 3.10. Databases that were upgraded from Symmetric 3.8.30 to 3.9.10 don't seem to have these problematic triggers.

We've narrowed it down to this part of the generated update trigger:
FROM (SELECT ClientID, row_number() OVER (ORDER BY (select 1)) as __row_num FROM inserted) AS inserted
INNER JOIN (SELECT ClientID, row_number() OVER (ORDER BY (select 1)) AS __row_num FROM deleted) AS deleted ON (inserted.__row_num = deleted.__row_num)

Databases upgraded from 3.8.30 have the following clause, which does not cause problems:
FROM inserted inner join Client orig on orig.ClientID=inserted.ClientID inner join deleted on deleted.ClientID=inserted.ClientID

Tagsdialect: sql-server, trigger

Relationships

related to 0002864 closedpmarzullo Raise an error if an MSSQL Update fails to get captured 
related to 0004354 closedelong MsSql - create trigger query fails when keys contain spaces 
related to 0004413 closedjosh-a-hicks MSSQL support for binary types in the primary key 

Activities

elong

2019-04-15 20:41

developer   ~0001410

The problem is SQL-Server uses statement-level triggers without a way to match old and new rows for multi-row updates. So, we have some workarounds in place to deal with it.

The join that you say is working is based on the configuration of sym_trigger.use_handle_key_updates = 0. It joins the old and new rows on primary key (PK). However, that trigger it is susceptible to losing changes when the PK is updated. For example, if you update the PK from 1 to 2, it will join inserted and deleted tables and get no rows to capture.

When you set use_handle_key_updates = 1, it joins the old and new rows on the row_number(). Every old row gets a new row. Now, they might be matched up incorrectly, but it shouldn't matter on most systems -- all the old rows will be updated and replaced by the new rows, almost like it deleted the old rows and inserted the new rows.

I tried your unit test but the rows came out correctly in the target database.

create table client (
  client_id integer primary key,
  practice_id integer,
  name varchar(50)
);

insert into client values (1, 23, 'Jeff');
insert into client values (2, 25, 'Andrew');
insert into client values (3, 24, 'John');

update client set practice_id = practice_id;

I can try inserting, updating, deleting with 30,000 rows to see if I can get something to happen. Are there any other settings you can think of that I should know about. Are you using any parameters that change trigger behavior? Are you using transformations?

pmarzullo

2019-08-13 20:00

developer   ~0001570

Possibly able to remove the use of the parameter use_handle_key_updates by modifying the trigger to join on row number if primary key updates were changed, otherwise join on primary key.

See UPDATE(<column name) and/or COLUMNS_UPDATED() for determining if primary keys were updated.

pmarzullo

2019-09-13 13:11

developer   ~0001598

Functionality changed to create delete events then insert events when primary key changed.
This doubles the number of sym_data events for an update, but the end result is correct.

When updates of non-key columns occur, the normal join of the insert and delete tables within the trigger is still executed to get the old data and the new data.

One drawback is that you don't get the old data when updating the primary key because there is no way to successfully join the delete and insert tables using primary keys, because they were changed.

This fix also fixes Sybase.

Related Changesets

SymmetricDS: 3.11 671e53fc

2019-09-13 09:05:27

Philip Marzullo

Details Diff
0003901: MSSQL: Updating multiple rows in source table results in
reversed rows in destination table
Affected Issues
0003901
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/ase/AseTriggerTemplate.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mssql/MsSqlTriggerTemplate.java Diff File
mod - symmetric-core/src/main/java/org/jumpmind/symmetric/db/AbstractTriggerTemplate.java Diff File

SymmetricDS: 3.11 9f2dbd45

2019-10-25 16:24:36

admin

Details Diff
0003901: MSSQL: Updating multiple rows in source table results in
reversed rows in destination table
Affected Issues
0003901
mod - symmetric-core/src/main/resources/symmetric-schema.xml Diff File

Issue History

Date Modified Username Field Change
2019-04-04 23:35 j.mackay@backinmotion.com.au New Issue
2019-04-15 20:41 elong Note Added: 0001410
2019-04-15 20:41 elong Assigned To => elong
2019-04-15 20:41 elong Status new => feedback
2019-04-24 16:22 admin Tag Attached: dialect: sql-server
2019-04-24 16:22 admin Tag Attached: trigger
2019-08-13 20:00 pmarzullo Note Added: 0001570
2019-09-13 12:57 pmarzullo Assigned To elong => pmarzullo
2019-09-13 13:04 pmarzullo Target Version => 3.11.0
2019-09-13 13:11 pmarzullo Status feedback => resolved
2019-09-13 13:11 pmarzullo Resolution open => fixed
2019-09-13 13:11 pmarzullo Fixed in Version => 3.11.0
2019-09-13 13:11 pmarzullo Note Added: 0001598
2019-09-27 21:00 Changeset attached => SymmetricDS 3.11 671e53fc
2019-10-25 20:10 elong Relationship added related to 0002864
2019-10-25 21:00 admin Changeset attached => SymmetricDS 3.11 9f2dbd45
2019-11-15 14:04 admin Status resolved => closed
2020-04-23 18:56 elong Relationship added related to 0004354
2020-06-17 15:26 elong Relationship added related to 0004413