Syncs _tracking record but not data row RRS feed

  • Question

  • Using SF 2.1

    I am experiencing a strange and inconsistent bug.  Sometimes when I provision an additional scope it will sync the tracking records but not the actual data records.

    Scenario.  I provision a database with several scopes containing several separate tables each.  I sync the database.  No problems, all is well.  I then provision an additional few scopes that contain some of the same tables as in the first set of scopes, but with different filter parameters.  This time when I sync it pulls own the _tracking records and syncs those into the client database, but it doesn't copy the records in the actual data tables...???

    This doesn't always happen though.  It works most of the time as it should, but every now and then it behaves in this way and I have not been able to establish any pattern whatsoever for what leads to this behavior. 

    Anybody else experience this?

    Saturday, October 9, 2010 1:35 PM

All replies

  • have you specified CreateProceduresForAdditionalScope for the additional scope on the same table? The default is CreateOrUseExisting and I have seen this happen where an additional scope for the same table is pointing to the same SelectChanges sp
    • Marked as answer by AJ8829 Monday, October 11, 2010 11:42 AM
    • Unmarked as answer by AJ8829 Wednesday, October 13, 2010 8:16 AM
    Sunday, October 10, 2010 1:07 AM
  • I'm sorry but I got a little ahead of myself. I thought this had solved the problem but it didn't. I will add another clue to the puzzle. The rows being synced are filtered. After I provision for the first time, then sync, it will sync the records. After I sync a second time it will only pull down the _tracking records. So if I reprovision the client before each sync all is well, otherwise I get synced records for the first sync and then just _tracking for all subsequent syncs. Note also that in that status is shows the records are being successfully applied even though all it pulled down was the _tracking records.
    Wednesday, October 13, 2010 8:28 AM
  • Here is another clue.  Running SQL Server Profiler I can see the _selectchanges SP is being executed on sync and that it returns the appropriate records.  So the orchestrator is running SelectChanges, changed records are returned by the SQL Server, SyncStats show changes as having been applied, yet no record is getting inserted in the actual database table.
    Wednesday, October 13, 2010 9:18 AM
  • can you try enabling Sync Tracing and see if it gives any clue?
    Wednesday, October 13, 2010 3:11 PM
  • What do you mean by Sync Tracing?

    I have an update for this problem, and it's rather serious.  This appears be a bug that can actually result in data loss.  I've got two problem here, one related to the existing problem with _tracking records and a new problem.

    Here's the scenario.

    The way my system is designed is users have access to rows in tables based on Access Rules.  Scopes are filtered based on these access rules.  I modify the _selectchanges SP to filter records based on these access rules.  Works great.

    I had a client that I needed to give access to a new set of data.  His permissions changed so this expanded the data he is allowed to see.  So he resynced and nothing happened.  He didn't see any new changes because the new data was old, the sync_min_timestamp value passed into the _selectchanges SP exceeded the timestamps on all these old records he was granted access to.

    Question 1) How can you force old data back down to a particular sync client?  I'm thinking the only way to handle this is create additional tracking tables that monitor client syncs by user.  By tracking this I can display records on _selectchanges regardless of the sync_min_timestamp if it's new (old) data they were just given access to.

    Back to the problem.  Ok, so to force the changes back down to the client I executed a command like "UPDATE table SET Id=Id".  I did this just to update the _tracking timestamps on all the records so the client would download them.  The drawback to this is it forces all clients to redownload the records.

    So the client then synced and it worked, it saw the changes.  But all it did was download the _tracking rows.  NO DATA ROWS WERE DOWNLOADED!  Then the next time the client synced it uploaded all those _tracking records to the server as deleted rows.  So then the records got wiped out on the server!!!!!! 

    Question 2) Why would the client download only the _tracking records?

    So I update a record on the server, client syncs and only downloads _tracking rows.  Then the next time the client syncs up it deletes the records on the server.  This is a huge problem!!!



    Monday, November 15, 2010 7:02 AM
  • Wednesday, November 17, 2010 9:39 AM
  • I have a similar problem, and am using SyncFx2.1 with dynamic filters enabled for the application. The application has access based privileges, so the users can see only the data assigned to them. Everything works fine if we associate the privileges  at the creation level of the users. The data syncs properly, but the following scenario fails:

    UserX is assigned with 5 records at the time of creation, on login the data syncs from the server to local .sdf file and the UserX is able to see all the 5 records. Later userX is re-assigned with 2 more records, when userx tries to resync the data is not getting synced to the local .sdf file.


    Vishal Mohan 

    Saturday, January 22, 2011 6:49 PM
  • are those records assigned to UserX via another mapping table? have those two records been recently updated so that their timestamps has changed since the initial sync?
    Sunday, January 23, 2011 7:31 AM
  • Thanks JuneT, here the detail follows:

    First Snapshot of the central database:


    UserID     Name      ProductID      CreatedDateStamp
    UserX       XYZ         PID001         01/14/2011


    ProductID      ProductName         CreatedDateStamp
    PID001          Desktop                 01/14/2011
    PID002          Laptop                   01/14/2011
    PID003          Printer                   01/14/2011

    UserX synced the data, PID001 got downloaded from central server to local .sdf file.

    Second Snapshot of the central database:


    UserID     Name      ProductID      CreatedDateStamp
    UserX       XYZ         PID001         01/14/2011
    UserX       XYZ         PID002         01/20/2011


    ProductID      ProductName         CreatedDateStamp
    PID001          Desktop                 01/10/2011
    PID002          Laptop                   01/10/2011
    PID003          Printer                   01/10/2011

    When UserX syncs the data, PID002 is not downloaded. But UserAccess table records is synced, i think since there are no changes on the Product table from the first sync the records are not getting synced to local db.

    The _selectchanges sp as follows

    ALTER PROCEDURE [dbo].[Product_selectchanges]
    	@sync_min_timestamp BigInt,
    	@sync_scope_local_id Int,
    	@sync_scope_restore_count Int,
    	@sync_update_peer_key Int,
    	@LoggedInUserId Int
    SELECT [side].[ProductID], [base].[ProductName], [base].[ CreatedDateStamp], [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 [Product] [base] RIGHT JOIN [Product_tracking] [side] ON [base].[ ProductID] = [side].[ ProductID] WHERE (([side].[ProductID] IN (SELECT [UserAccess].[ProductID] FROM UserAccess WHERE [UserAccess].[UserID] = @LoggedInUserId)) 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].[SubTopicID] IS NULL)) AND ([side].[update_scope_local_id] IS NULL OR [side].[update_scope_local_id] <> @sync_scope_local_id OR ([side].[update_scope_local_id] = @sync_scope_local_id AND [side].[scope_update_peer_key] <> @sync_update_peer_key)) AND [side].[local_update_peer_timestamp] > @sync_min_timestamp
    Sunday, January 23, 2011 3:12 PM
  • you are right on your analysis. the scenario described here and the behavior is actually ( unfornately by designed behavior ). the current design of tracking changes and getchanges is based on single table level. there is not "logical record" with the current release.

    you might want to use join query to get the product table changes.



    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Yunwen Bai Wednesday, January 26, 2011 9:11 PM
    Wednesday, January 26, 2011 9:10 PM