|
|
|
|
|
You have the sym_trigger.use_handle_key_updates=1, is that correct? I believe that causes the trigger to be generated with logic that is vulnerable to mis-ordering. Here's why. SQL-Server provides two virtual tables to the trigger: "inserted" is the new rows and "deleted" is the old rows. The trigger needs to join those two virtual tables, and it normally joins on the primary key columns. But when you enable handling of primary key updates, it can no longer join on the PK because the new and old values are different. So it assumes row 1 of "inserted" will match row 1 of "deleted", and so on. It used to open a cursor to loop through the rows, but it was changed to join the tables using row_number(), because cursors have a performance penalty. The change in technique was supposed to be equivalent logic. In reality, there is no guarantee from SQL-Server that there is any kind of order on the virtual tables. Does your test fail every time you run it on 3.9, or just sometimes? Does your test work every time on 3.8, or just sometimes? |
|
|
Hi, and thanks for reply. The sym_trigger.use_handle_key_updates setting was not changed by me, and I believe it is still at default (0) value. Also, this test always fails on 3.9, and always works on 3.8 . I`m attaching two database triggers versions for further investigation (generated from 3.8 and 3.9 versions). If You'll look at this fragment - this is the difference which probably causes the error: 3.8: ... from inserted inner join "zol"."dbo"."rightsholder_invoice" orig on orig."rightsholder_invoice_id"=inserted."rightsholder_invoice_id" inner join deleted on deleted."rightsholder_invoice_id"=inserted."rightsholder_invoice_id" 3.9: ... from (select "rightsholder_invoice_id","rightsholder_invoice_datetime","rightsholder_id","number","income_date","invoice_date","sale_date","rightsholder_invoice_type_id","status_description","comment","correcting_rightsholder_invoice_id","GUID","LAST_UPDATE_DATE_TIME", row_number() over (order by (select 1)) as __row_num from inserted) inserted inner join "zol"."dbo"."rightsholder_invoice" orig on orig."rightsholder_invoice_id"=inserted."rightsholder_invoice_id" inner join (select "rightsholder_invoice_id","rightsholder_invoice_datetime","rightsholder_id","number","income_date","invoice_date","sale_date","rightsholder_invoice_type_id","status_description","comment","correcting_rightsholder_invoice_id","GUID","LAST_UPDATE_DATE_TIME", row_number() over (order by (select 1)) as __row_num from deleted)deleted on (inserted.__row_num = deleted.__row_num) PS.: The query used to update multiple rows in my previous post should be: UPDATE [zol].[dbo].[rightsholder_invoice] SET [comment] = 'some_text'; |
|
|
Oh, I`ve just checked that the default value for sym_trigger.use_handle_key_updates changed between versions: In 3.9: https://www.jumpmind.com/downloads/symmetricds/doc/3.9/html/user-guide.html USE_HANDLE_KEY_UPDATES = default 1 In 3.8: https://www.jumpmind.com/downloads/symmetricds/doc/3.8/html/user-guide.html USE_HANDLE_KEY_UPDATES = default 0 Well, that was unexpected, although mentioned in changelog for 3.9: 0003053: [Improvement] Default handle_key_updates in sym_trigger to 1 (chenson) However, error probably still remains in SQL server for this case - as I didn't change PK value, only 'comment' column value on multiple rows at once. |
|
|
This situation was improved in issue 0005157 for SymmetricDS 3.14, where it only uses the "handle key updates" logic when the primary key values are actually changing. |
- Anonymous