none
SyncFX 2.1 : problem with Metadata thinking that records are deleted !? RRS feed

  • Question

  • We have a scope that syncronize some tables between two countries 

    in the 'Master' end, there is a Navision ERP system that we extract data from into the the synchronized tables. 

    The extraction method uses the SQL MERGE operator that automatically merge data into a table and deletes when not found.

    The problem we face is that some rows gets deleted and then re-created, using the same Primary Key.

    The problem is now that the Sync Metadata thinks that the data is deleted, even after the data has been re-inserted (sync_row_is_tombstone = 1)

    Why is this happening ?... shouldn't the MetaData row become active again, once the row is re-inserted ?

    edit 1:

    I've tested a vanilla setup where i manually delete and re-create using the same key.. in this case the sync_row_is_tombstone gets updated to 0 which is correct... :-/

    edit 2:

    I've written a hack of  a job, that joins the _tracking table where 'sync_row_is_tombstone = 1' with the tables i have troubles with:

    1. insert data into temptable
    2. delete from table
    3. insert into table (effectivly updating metadata)
    4. delete temptable 

    I need to do this, to be sure that metadata will push the data to the client database... i know i could simply update the metadata but i'm not 100% confident how the data should look like. 

    • Edited by Montago Tuesday, July 10, 2012 1:25 PM
    Tuesday, July 10, 2012 11:39 AM

All replies

  • are new rows always inserted via SQL Merge? am assuming you have SQL Merge that deletes a row and another SQL Merge re-inserting the same rows, either way, theys should be be firing triggers to update the metadata.

    Wednesday, July 11, 2012 1:22 AM
    Moderator
  • The whole content of the table is more or less controlled by the merge function but some updates do occur by a program (Linq 2 SQL DataContext )

    the layout of on of the tables look like this:

    SET ansi_nulls ON 
    SET quoted_identifier ON 
    SET ansi_padding ON 

    CREATE TABLE [dbo].[resources] 
      ( 
         [dataset]     [NVARCHAR](3) NOT NULL, 
         [lxbenummer]  [BIGINT] NOT NULL, 
         [medarbejder] [VARCHAR](20) NOT NULL, 
         [navn]        [VARCHAR](50) NOT NULL, 
         [birth date]  [DATETIME] NOT NULL, 
         CONSTRAINT [PK__Resource__EFB4713E4A03EDD9] PRIMARY KEY CLUSTERED ( 
         [dataset] ASC, [medarbejder] ASC )WITH (pad_index = OFF, 
         statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks = on, 
         allow_page_locks = on) ON [PRIMARY] 
      ) 
    ON [PRIMARY] 

    and the Merge function:

    MERGE [resources] TARG 
    using navi2009views.dbo.resources SRC 
    ON ( TARG.lxbenummer = src.lxbenummer ) 
       AND TARG.medarbejder = src.medarbejder 
       AND TARG.navn = src.navn 
       AND TARG.[birth date] = src.[Birth Date] 
    WHEN NOT matched BY target THEN 
      INSERT 
      VALUES (dataset, 
              lxbenummer, 
              medarbejder, 
              navn, 
              [birth date]) 
    WHEN NOT matched BY source THEN 
      DELETE; 

    We did a couple of tests with the Merge function before we used it in production and we found that the metadata would update just as expected...

    We've been using LINQ2SQL for years now and lately for about 2 years with the sync framework, without a problem like the one we have now. 

    It's a real mystery why the metadata suddently thinks that data has been deleted, without that being the case :-/

    edit

    would / could it be possible that a DELETE is fired and the DELETE_TRIGGER will only fire partially ?... that it succeed in updating the metadata, but fail to actually delete the data ? thereby invalidating the metadata ? 

    • Edited by Montago Wednesday, July 11, 2012 7:34 AM
    Wednesday, July 11, 2012 7:31 AM
  • must be an issue with the order in which the trigger is executed.

    in your case, a same row having the same PK can actually be deleted and reinserted in the same MERGE statement since your MERGE condition is not just on the PK.

    but the order of trigger execution is not guaranteed, so it could be that the last trigger to execute is the delete.

    try setting the trigger order so that Delete trigger executes first (see: http://technet.microsoft.com/en-us/library/ms189568.aspx)


    Wednesday, July 11, 2012 7:53 AM
    Moderator
  • (note i didn't write the Merge script)

    The reason why the Merge is selecting on ALL columns, is to prevent doing UPDATE's which would force a full re-sync every time the MERGE is called !!

    I will look into ordering the triggers as you suggested and until i do, we'll use the unload-delete-reload-drop method i explained... 

    thanks

    edit:

    the link specify that you can order AFTER-triggers, meaning that if you have 2x AFTER-triggers you can order which will fire before the other... 

    SyncFX uses FOR-triggers which fire simultanious to the event - i'm not sure, but according to MSDN, AFTER-triggers fire after a successful operation but say nothing about FOR-triggers, i suspect that FOR-triggers fire even though an operation is unsuccessful ?

    edit 2:

    apparently FOR and AFTER might be the same .. hmm... 

    • Edited by Montago Wednesday, July 11, 2012 11:46 AM
    Wednesday, July 11, 2012 8:50 AM
  • FOR and AFTER are the same.

    there are only two types of triggers, instead of and AFTER (FOR) (http://technet.microsoft.com/en-us/library/ms189799)

    Thursday, July 12, 2012 6:33 AM
    Moderator