locked
Bug in _selectchanges sp ? RRS feed

  • Question

  • Hi,

    We are in the process of setting up replication using Sync Framework 2.0.  The configuration is that of a server and numeours mobile computers to keep in sync.  The mobiles run SqlServer2005/Express and th server runs either SqlServer2000 or SqlServer2008.  Thus, we are talking about collaborative synchronization and SqlSyncProvider on both sides.  (In this test, we use a direct link and not WCF).

    When provisioning the databases, we start by provisioning the server.  From the server, we create the mobile (tables and tracking info).  When synchronizing, we synchronize from the mobile.

    Synchronizing seems to work fine with inserts and updates.  We have a problem with deletes, though.  When a record is deleted from the mobile, the record is not synchronized.  A bit of investigating led us to the '_selectChanges' stored proc which is the procedure gathering the changes to be made by the synchronization process.  If I run it manually on the database, it does not return the deleted record.

    The generated SELECT statement joins both the target and tracking tables with an outer join guaranteeing that all tracking records will be included, which makes perfect sense if we are to include deleted records.

    Digging a bit deeper into it, it turns out that it has a condition in the selection of the deleted records as follows: 
          ... AND [side].[myPrimaryKey] IS NULL
    where myPrimaryKey is the primary key of the tracked table.  In that statement, [side] represents the tracking table and [base] represents the table being tracked.  My interpretation of the condition above is that it is an extra check that makes sure the record has indeed been deleted.  But we check the key value of the tracking table, which is the wrong side of the join.  I would have expected to find [base] and not [side] in this condition.  Actually, if I modify the stored proc in this way, the synchronization seems to work just fine.

    Now, when I delete records from the mobile the behavior is as indicated above but when I delete records from the server it seems to work just fine event with the original procedure.  I have no idea why that would be.  In addition, I have a hard time believing such a problem wouod have escaped Microsoft.  So, is this a known problem or did I do something awfully wrong here?

    Any help would be appreciated.  Thanks.

    Monday, July 12, 2010 6:45 PM

All replies

  • Hi there,

    Did you use the sync provisioning classes to provision?  Do you mind pasting your entire selectChanges proc in here?

    -Jesse

    Tuesday, July 13, 2010 12:09 AM
  • Jesse,

    Yes, we use the sync provisioning classes and all is standard in our setup.  Since the change is very minor, we considered that the safest approach was to let the provisioning take place and then read back the procedures and update them.  Although this is easily done, we are not sure about possible side-effects.

    Tuesday, July 13, 2010 12:24 PM
  • would you mind sharing the selectchanges sp? is your myPrimaryKey] your filter column as well?

    tnx

    Tuesday, July 13, 2010 1:10 PM
  • I did not include it in my original post because it is a bit complicated and requires some explanation, but here it is.

    The mobile is not an integral copy of the server but only a subset.  Since Sync Framework does not support this concept, we implemented it (partly) through filters.  Now the point is to get the selection criterion to the selection procedure.  To do this, we create a temporary table on the connection from the SelectingChanges and ChangesApplied events, ensuring that the table has only one record.  The filter then picks the value from this record.  This explains the strange looking filter you will find in the procedure. Here is the text of the procedure, as extracted from the database:

    CREATE

     

     PROCEDURE [dbo].[Plage_selectchanges]

    @sync_min_timestamp

    BigInt,

    @sync_scope_local_id

    Int,

    @sync_scope_restore_count

    Int

    AS

    BEGIN

    SELECT

     

    [side].[Cle_Plage], [base].[Ref_Usager], [base].[PlageName], [base].[SynchroTag], [side].[sync_row_is_tombstone], [side].[local_update_peer_timestamp] as sync_row_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then COALESCE([side].[restore_timestamp], [side].[local_update_peer_timestamp]) else [side].[scope_update_peer_timestamp] end as sync_update_peer_timestamp, case when ([side].[update_scope_local_id] is null or [side].[update_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_update_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_update_peer_key] end else [side].[scope_update_peer_key] end as sync_update_peer_key, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then [side].[local_create_peer_timestamp] else [side].[scope_create_peer_timestamp] end as sync_create_peer_timestamp, case when ([side].[create_scope_local_id] is null or [side].[create_scope_local_id] <> @sync_scope_local_id) then case when ([side].[local_create_peer_key] > @sync_scope_restore_count) then @sync_scope_restore_count else [side].[local_create_peer_key] end else [side].[scope_create_peer_key] end as sync_create_peer_key FROM [Plage] [base] RIGHT JOIN [Plage_tracking] [side] ON [base].[Cle_Plage] = [side].[Cle_Plage] WHERE ((((Select Top 1 [Init] From #SyncParms) = 1 Or [side].[Cle_Plage] in (Select Cle_Plage From Plage Where Ref_Usager = (Select Cle_Usager From Usager Where UsagerName = (Select Top 1 [UsagerName] From #SyncParms))))) OR ([side].[sync_row_is_tombstone] = 1 AND ([side].[update_scope_local_id] = @sync_scope_local_id OR [side].[update_scope_local_id] IS NULL) AND [side].[Cle_Plage] IS NULL)) AND [side].[local_update_peer_timestamp] > @sync_min_timestamp

    END

    Tuesday, July 13, 2010 1:29 PM
  • Hi,

    The clause that you believe to be a problem,

     

    OR ([side].[sync_row_is_tombstone] = 1 AND ([side].[update_scope_local_id] = @sync_scope_local_id OR[side].[update_scope_local_id] IS NULL) AND [side].[Cle_Plage] IS NULL)

     

    is correct.  The purpose of that clause is for the case where a tombstone is sent to another node (tombstone meaning a delete for an item that the destination has never seen) there is no way for us to know the filter columns values to insert into the side table since the triggers are responsible for populating the filter columns values in the side table. Therefore, we also need to send deletes on where the UpdateScopeLocalId = our scope and the filter columns values are null.

     

    Your own filter clause is fairly complex, have you tried a stripped down version of an enum proc that only uses your where clause to verify it is working as expected?

     

    -Jesse

    Thursday, July 15, 2010 2:58 AM
  • Jesse,

    I have tested the filter clause in stand-alone and in the frame of the selection stored procs.  It seems to work fine.

    There are two things I would like to point here:

    1. The clause you extracted, as a whole, is definitely justified and I understand its presence.  It is only the very last part that I do not understand.  The tables involved are: [Plage] [base] RIGHT JOIN [Plage_tracking] [side].  This join guarantees that all the records from [side] will be present in the set; the ones mssing from [base] will have their join key value to null.  Thus, a test like "[side].[Cle_Plage] IS NULL" seems meaningless to me, whereas "[base].[Cle_Plage] IS NULL" would make a lot of sense.
    2. The effect of the selection filters should not be of any effect anyway because this clause is ORed with the one you show in your text.  Besides, I have tested the same statement  by removing the filter clause and it does not change the selection of the deleted records (deleted records are my concerne here).
    Thursday, July 15, 2010 11:53 AM