View Issue Details

IDProjectCategoryView StatusLast Update
0004642SymmetricDSBugpublic2020-11-21 21:16
Reportersmotycka Assigned Toelong  
Priorityhigh 
Status assignedResolutionopen 
Product Version3.12.5 
Summary0004642: after activation "sync_on_insert", the record field of synchronized table is filled with the value of the sym_data.data_id
Description__source (master) database engine__: **corp-000** //(SQL Server 2012)//
__target (slave) database engine__: **store-001** //(PostgreSQL-11.2)//

I replicate many tables including:
* main data table with occurences of zoological species: **oc_tOccurenceRecord** (//OccurenceRecordID// (primary key), //TaxonID//, //OccurenceLocalityID//, ...)
* table with unique names of localities: **oc_tOccurenceLocality** (//OccurenceLocalityID// (primary key), //OccurenceLocalityTitle//, ...)

The field **oc_tOccurenceRecord.//OccurenceLocalityID//** should be refer to value of field **oc_tOccurenceLocality.//OccurenceLocalityID//**

I want synchronize new names of localities
<code>UPDATE sym_trigger SET sync_on_insert=1 WHERE source_table_name like 'oc_tOccurenceLocality';</code>
Trigger is successfuly created:
<code>2020-11-17 19:55:59,445 INFO [corp-000] [MsSql2008SymmetricDialect] [corp-000-sync-triggers-1] Creating SYM_ON_I_FOR_CRP_TBND_NTRNTVYPRTL_TCCRNCLCLTY_CRP trigger for Database.dbo.oc_tOccurenceLocality</code>

What happens?

Into the parent table **oc_tOccurenceRecord** (both in the **corp** and in the **store** engine) is not saved new created locality id (OccurenceLocalityID), but value of **data_id** from the table **sym_data**, under which is sync event recorded. It's incomprehensibly.

List of events from table **sym_data**:
^ data_id ^ table_name ^ event_type ^ row_data ^
| **2552227** | oc_tOccurenceLocality | I | "157528","new locality name" |
| 2552228 | oc_tOccurenceRecord | U | "3007557","48472","**2552227**" |

The record of table **oc_tOccurenceRecord** save:
* correct value //OccurenceRecordID// = **3007557**
* correct value //TaxonID// = **48472**
* incorrect value //OccurenceLocalityID// = //2552227// The correct value should be **157528**


For this time I must deactivate //sync_on_insert// in the sym_trigger with source_table_name **oc_tOccurenceLocality** because with this bug I have inconsistent data.
Steps To Reproduce=== SymmetricDS ===
<code>UPDATE sym_trigger SET sync_on_insert=1 WHERE source_table_name like 'oc_tOccurenceLocality';</code>

=== OccurenceRecord form on the website ===
* creating new OccurenceRecord with filling new locality name ("on the fly" way)

=== Result ===
New OccurenceRecord is incorrect, because **oc_tOccurenceRecord.OccurenceLocalityID** not equals to the new value **oc_tOccurenceLocality.OccurenceLocalityID**, but it equals value of **sym_data.data_id** (!!!)

Tagsdata sync, dialect: sql-server, trigger

Activities

smotycka

2020-11-17 20:52

reporter   ~0001840

The same problem occurs with other reference values from other reference tables linked to the table oc_tOccurenceRecord.

For all referenced tables I can't use ON INSERT trigger.

elong

2020-11-18 18:51

developer   ~0001844

How do we reproduce this? Here is what we tried:

create table oc_tOccurenceLocality (OccurenceLocalityID integer primary key);

create table oc_tOccurenceRecord (OccurenceRecordID integer primary key, OccurenceLocalityID integer references oc_tOccurenceLocality(OccurenceLocalityID));

insert into oc_tOccurenceLocality values (1);
insert into oc_tOccurenceLocality values (2);
insert into oc_tOccurenceRecord values (1, 1);
update oc_tOccurenceRecord set OccurenceLocalityID = 2 where OccurenceRecordID = 1;

It worked as expected.
sym_data.png (22,800 bytes)   
sym_data.png (22,800 bytes)   

smotycka

2020-11-19 21:35

reporter   ~0001848

In the web-form (with table oc_tOccurenceRecord in background) is new value of Locality name filled (by user) "on the fly".
It means that, when the locality name isn't listed in "autocomplete menu" of the form field "Locality", the user writes new Locality name. This new locality name is automatically inserted into the table "oc_tOccurenceLocality" in the background, with new (autoincremented) OccurenceLocalityID.
Finally, user writes new OccurenceRecord and submits web-form. After this action I see in "sym_data"."row_data" all values (submitted from web-form), but value of OccurenceLocalityID is value of "data_id".
2020-11-19_22-34-57.png (6,836 bytes)   
2020-11-19_22-34-57.png (6,836 bytes)   

smotycka

2020-11-19 22:07

reporter   ~0001849

I attach excel file with exported sym_data.
First record (data_id = 2552199) relates with the activation "sync_on_insert=1" for table oc_tOccurenceLocality.
Second record (data_id = 2552200) and third rercord (data_id = 2552201), both relate with updating new OccurenceRecord (in which is new OccurenceLocality created "on the fly")
New locality (created "on the fly") is finally saved into the table with OccurenceLocalityID = 157527 at 19:56:04.950
Web form with OccurenceRecord (with OccureceRecordID = 3007557) is saved at 19:56:04.973

The OccurenceRecord is saved with OccurenceLocalityID= 2552200 instead of OccurenceLocalityID=157527.

smotycka

2020-11-20 09:21

reporter   ~0001851

I found that problem with an changed referenced value (in the "sym_data"."row_data" of the main OccurenceRecord) to "sym_data"."data_id" value is only by the fields "OccurenceLocalityID", "OccurenceProjectID", which reference to "PRIMARY KEY" fields of the register tables: "oc_tOccurenceLocality" and "oc_tOccurenceProject".

By these tables cannot by set "INSERT TRIGGER" (sync_on_insert=1), because it occurs the inserting of the "sym_data"."data_id" value of previous "sym_data" record, which points to "event_type=I" of referenced "table_name" ("oc_tOccurenceLocality", "oc_tOccurenceProject", ...).

"sym_data" records are created by the trigger on the source table.
Can this problem relates with the fact, that source database system is MSSQL ?

I attach the definition of the generated INSERT trigger, which I can not have:

create trigger SYM_ON_I_FOR_CRP_TBND_NTRNTVYPRTL_TCCRNCLCLTY_CRP on "IntranetovyPortal"."dbo"."oc_tOccurenceLocality" with execute as caller after insert as                                                                                                
   begin                                                                                                                                                                  
     declare @NCT int 
     set @NCT = @@OPTIONS & 512 
     set nocount on                                                                                                                                                       
     declare @TransactionId varchar(1000)                                                                                                                                 
     if (@@TRANCOUNT > 0) begin                                                                                                                                           
       select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0                     
     end                                                                                                                                                                  
      
     if ("SM_testovacia".dbo.sym_triggers_disabled() = 0) begin                                                                                                                           
         insert into  "SM_testovacia"."dbo".sym_data 
           (table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time) 
          select 'oc_tOccurenceLocality','I', 211, 
          case when inserted."OccurenceLocalityID" is null then '' else ('"' + convert(varchar(40), inserted."OccurenceLocalityID",2) + '"') end+','+
          case when inserted."OccurenceLocalityTitle" is null then '' else '"' + replace(replace(convert(nvarchar(255),inserted."OccurenceLocalityTitle") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."OccurenceLocalityDescription" is null then '' else '"' + replace(replace(convert(nvarchar(2000),inserted."OccurenceLocalityDescription") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."OccurenceLocalityType" is null then '' else '"' + replace(replace(convert(nvarchar(30),inserted."OccurenceLocalityType") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."OwnerID" is null then '' else ('"' + convert(varchar(40), inserted."OwnerID",2) + '"') end+','+
          case when inserted."OccurenceLocalityCode" is null then '' else '"' + replace(replace(convert(nvarchar(30),inserted."OccurenceLocalityCode") ,'\','\\'),'"','\"') + '"' end+','+
          case when inserted."Created" is null then '' else ('"' + convert(varchar,inserted."Created",121) + '"') end+','+
          case when inserted."CreatorID" is null then '' else ('"' + convert(varchar(40), inserted."CreatorID",2) + '"') end+','+
          case when inserted."LastUpdated" is null then '' else ('"' + convert(varchar,inserted."LastUpdated",121) + '"') end+','+
          case when inserted."OperatorID" is null then '' else ('"' + convert(varchar(40), inserted."OperatorID",2) + '"') end+','+
          case when inserted."Status" is null then '' else '"' + replace(replace(convert(nvarchar(30),inserted."Status") ,'\','\\'),'"','\"') + '"' end, 
                  'oe_transaction', @TransactionId,  "SM_testovacia".dbo.sym_node_disabled(), null, current_timestamp 
                                                                                                                                           
          from inserted                                                                                   
                                                                                                                                            
          where 1=1               
     end                                                                                                                                                                  
                                                                                                                                                  
     if (@NCT = 0) set nocount off                                                                                                                                        
   end                                                                                                                                                                    
---- go

smotycka

2020-11-20 21:23

reporter   ~0001853

What about IDENTITY property ?

The structure of problematic tables in the source SQL server instance are:
CREATE TABLE oc_tOccurenceLocality (
    OccurenceLocalityID int IDENTITY(1,1) NOT NULL,
    OccurenceLocalityTitle nvarchar(255),
    ...
    CONSTRAINT PK_oc_tOccurenceLocality PRIMARY KEY (OccurenceLocalityID)
)

CREATE TABLE IntranetovyPortal.dbo.oc_tOccurenceProject (
    OccurenceProjectID int IDENTITY(1,1) NOT NULL,
    OccurenceProjectTitle nvarchar(255),
    ...
    CONSTRAINT PK_oc_tOccurenceProject PRIMARY KEY (OccurenceProjectID)
)


Both of these Primary keys are defined with the property: "IDENTITY".

In the context of the "IDENTITY" clause and SQL server I was found one old forum threat -> https://bugs.eclipse.org/bugs/show_bug.cgi?id=288015#c1

Interesting are also the remarks about IDENTITY property -> https://docs.microsoft.com/en-us/sql/t-sql/functions/identity-transact-sql?view=sql-server-ver12
If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert.
As we can see in the previous note (https://www.symmetricds.org/issues/view.php?id=4642#c1851) INSERT trigger should by executed "after insert".
This trigger will insert values into the table "sym_data", which has on the source (corp) SQL server also defined IDENTITY property - on the field "data_id" ->
	[data_id] [bigint] IDENTITY(1,1) NOT NULL,

smotycka

2020-11-20 21:51

reporter   ~0001854

When the problem is related with IDENTITY property, we can try to resolve problem by calling of the value of inserted.OccurenceLocalityID with:
IDENT_CURRENT( 'oc_tOccurenceLocality' )
???

elong

2020-11-20 22:24

developer   ~0001855

I think I understand what you're talking about now. This came up before in issue 0002457 where an application was using @@IDENTITY and it was getting the sym_data.data_id value. Yes, I think IDENT_CURRENT( 'oc_tOccurenceLocality' ) would work. Or SCOPE_IDENTITY() might also work.

smotycka

2020-11-21 21:16

reporter   ~0001856

Thank yout very much for your time and help.
It was very helpfull for me, to see threats:


I also set custom_before_insert_text and custom_on_insert_text with BackupIdentitySeederFunc.
After this I activated sync_on_insert to all replicated tables and .. all it's OK
Problem SOLVED.
Thank you.

Issue History

Date Modified Username Field Change
2020-11-17 20:45 smotycka New Issue
2020-11-17 20:45 smotycka Tag Attached: data sync
2020-11-17 20:45 smotycka Tag Attached: dialect: sql-server
2020-11-17 20:45 smotycka Tag Attached: trigger
2020-11-17 20:52 smotycka Note Added: 0001840
2020-11-18 18:51 elong Note Added: 0001844
2020-11-18 18:51 elong File Added: sym_data.png
2020-11-18 18:51 elong Assigned To => elong
2020-11-18 18:51 elong Status new => feedback
2020-11-19 21:35 smotycka Note Added: 0001848
2020-11-19 21:35 smotycka File Added: 2020-11-19_22-34-57.png
2020-11-19 21:35 smotycka Status feedback => assigned
2020-11-19 22:07 smotycka Note Added: 0001849
2020-11-19 22:07 smotycka File Added: sym_data_of_uncorrectly_saved_value_OccurenceLocalityID.xlsx
2020-11-20 09:21 smotycka Note Added: 0001851
2020-11-20 21:23 smotycka Note Added: 0001853
2020-11-20 21:51 smotycka Note Added: 0001854
2020-11-20 22:24 elong Note Added: 0001855
2020-11-21 21:16 smotycka Note Added: 0001856