View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0003901 | SymmetricDS | Bug | public | 2019-04-04 23:35 | 2020-06-17 15:26 |
| Reporter | j.mackay@backinmotion.com.au | Assigned To | pmarzullo | ||
| Priority | urgent | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.10.0 | ||||
| Target Version | 3.11.0 | Fixed in Version | 3.11.0 | ||
| Summary | 0003901: MSSQL: Updating multiple rows in source table results in reversed rows in destination table | ||||
| Description | The 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 | ||||
| Tags | dialect: sql-server, trigger | ||||
|
|
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? |
|
|
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. |
|
|
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. |
|
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 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 | ||
| 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 |