none
Bidirectional SyncGroup and SqlCeClientSyncProvider.GetChanges RRS feed

  • Question

  • Hi, just a follow-up question regarding Bidirectional sync groups. If you perform an update to a row in a Bidirectional sync group after a new row has been uploaded/sync'd, should GetChanges always select this updated row during a subsequent sync for upload? I'm finding, for new rows, changes are uploaded but then if you modify a row that has already been uploaded the updated row won't always be selected on the client by GetChanges.

    • Edited by SunHunter Monday, May 3, 2010 3:13 PM wrong sync group type
    Monday, May 3, 2010 3:04 PM

Answers

  • My tentative findings are this problem could now be resolved! The bottom line appears to be, if your app has opened a SqlCeConnection to your client database before you have sync'd, you need to close/re-open the connecton once the initial sync has completed (especially if your app is always using the same connection), otherwise changes will not be tracked and your client will not upload all of its changes to the server (I'm not sure why the change tracking infrastructure only works correctly if you have a certain type of SqlCeConnection, but I'll leave that mystery for somebody else. There are no properties in the connection string that can be changed related to change tracking as far as I can tell, so there must be something under the hood that's different).

    I wish this issue had a bit more visibility because I don't expect to be the only person who has had this problem and it was exceptionally difficult to find out the cause.
    I hope this information helps somebody else. Thank you very much June for your input/help.
    • Marked as answer by SunHunter Friday, May 7, 2010 7:06 AM
    Thursday, May 6, 2010 3:55 PM

All replies

  • I'm not sure if this coincidence or not, but my application handled an OutOfMemoryException (loading a bitmap) before instigating a sync. Could GetChanges be failing internally and reporting a false zero figure? What I am finding is that for both uploadonly and bi-directional sync, GetChanges is returning zero changes after rows have been added or modified. (This is Microsoft Synchronization Services for ADO.NET v1.0 Service Pack 1 for devices)
    Monday, May 3, 2010 9:18 PM
  • Hi SunHunter

    Can you tell me which version of the Microsoft Sync Framework you are using?

     


    Maria del Mar Alvarez Rohena Microsoft Sync Framework
    Monday, May 3, 2010 9:21 PM
  • Hi, yes, it's Microsoft Synchronization Services for ADO.NET v1.0 Service Pack 1 for devices -

    The device is also running SQL Server Compact Edition 3.5 SP1
    Monday, May 3, 2010 9:38 PM
  • Here's evidence of this issue (from my application log):

     

    04/05/2010 08:15:29 Start (synchronous) sync... (ClientID: 81e95aa2-4d79-41e7-9167-038b2ceeb711)

    ########################
    04/05/2010 08:17:04 Sync completed...
    ########################
    04/05/2010 08:18:14 OutOfMemoryException    at Microsoft.AGL.Common.MISC.HandleAr(PAL_ERROR ar)
       at System.Drawing.Bitmap._InitFromMemoryStream(MemoryStream mstream)
       at System.Drawing.Bitmap..ctor(String filename)
       at ScrutinizeMobile.frmIncident.fromFile()
       at ScrutinizeMobile.frmIncident.toolBarIncident_ButtonClick(Object sender, ToolBarButtonClickEventArgs e)
       at System.Windows.Forms.ToolBar.OnButtonClick(ToolBarButtonClickEventArgs e)
       at System.Windows.Forms.ToolBar.WnProc(WM wm, Int32 wParam, Int32 lParam)
       at System.Windows.Forms.Control._InternalWnProc(WM wm, Int32 wParam, Int32 lParam)
       at Microsoft.AGL.Forms.EVL.EnterMainLoop(IntPtr hwnMain)
       at System.Windows.Forms.Application.Run(Form fm)
       at ScrutinizeMobile.Program.Main()

    ########################
    04/05/2010 08:20:55 Inserted PK: IncidentUID PKValue: 98c7e933-9d4c-4acb-bc4b-e926d2167894 RowsAffected: 1
    ########################
    04/05/2010 08:20:55 Inserted PK: IncidentCommentUID PKValue: b18a50e8-05c8-4e36-8729-bbde0c748449 RowsAffected: 1
    ########################
    04/05/2010 08:20:55 Inserted PK: IncidentImageUID PKValue: f59cdcb0-8945-472e-aa8d-77af9b0b1097 RowsAffected: 1
    ########################
    04/05/2010 08:20:58 Inserted PK: InspectionUID PKValue: 694d700e-6616-4584-98c1-92ae844ddec0 RowsAffected: 1
    ########################
    04/05/2010 08:20:58 Inserted PK: InspectionUID PKValue: 4598bfa7-770c-4ef4-9823-e37b45e83d91 RowsAffected: 1
    ########################
    04/05/2010 08:20:59 Inserted PK: InspectionUID PKValue: 61fdb88a-39b0-4561-860e-02d8678db913 RowsAffected: 1
    ########################
    04/05/2010 08:20:59 Inserted PK: InspectionUID PKValue: 7c3fa5ea-aa16-43d7-ae3d-79b9823dd2f3 RowsAffected: 1
    ########################
    04/05/2010 08:20:59 Inserted PK: InspectionUID PKValue: 4157d401-411b-412d-96fa-4c96f28c82d9 RowsAffected: 1
    ########################
    04/05/2010 08:20:59 Inserted PK: InspectionUID PKValue: 5f9e6364-5962-43e3-8835-ecb3b3429480 RowsAffected: 1
    ########################
    04/05/2010 08:20:59 Inserted PK: InspectionUID PKValue: 0ba483cd-3149-4d7c-8b98-95702bbb02dd RowsAffected: 1
    ########################
    04/05/2010 08:20:59 Inserted PK: InspectionUID PKValue: 9a77bcbc-4773-48ca-90ae-f4cd29e59158 RowsAffected: 1
    ########################
    04/05/2010 08:20:59 Inserted PK: InspectionUID PKValue: c5f509aa-eca7-4b62-bddd-6953a04b0eac RowsAffected: 1
    ########################
    04/05/2010 08:20:59 Inserted PK: InspectionUID PKValue: b09b90bb-dfbb-4f1c-acdf-dbf0e5c01c78 RowsAffected: 1
    ########################
    04/05/2010 08:20:59 Inserted PK: InspectionUID PKValue: 93bebcb4-4c5e-4fa7-ab07-775feeda8881 RowsAffected: 1
    ########################
    04/05/2010 08:20:59 Inserted PK: InspectionUID PKValue: 87a38499-e679-4266-8625-d00c08d2346b RowsAffected: 1
    ########################
    04/05/2010 08:20:59 Inserted PK: InspectionUID PKValue: 701df9be-b724-4d1c-9452-23865ec2254a RowsAffected: 1
    ########################

    04/05/2010 08:20:59 Start (asynchronous) sync... (ClientID: 81e95aa2-4d79-41e7-9167-038b2ceeb711)
    ########################

    04/05/2010 08:21:01 GetChanges: Table Inspections - 0 rows
    ########################
    04/05/2010 08:21:03 GetChanges: Table Incidents - 0 rows
    ########################
    04/05/2010 08:21:04 GetChanges: Table IncidentComments - 0 rows
    ########################
    04/05/2010 08:21:05 GetChanges: Table IncidentImages - 0 rows
    ########################
    04/05/2010 08:21:29 Sync completed...

     

    From above:

    04/05/2010 08:17:04: The first sync completes ok.

    04/05/2010 08:18:14: I got OutOfMemoryException within the application loading a bitmap

    04/05/2010 08:20:55: After this, I insert rows into four different tables (which are defintely inserted)

    04/05/2010 08:20:59: I sync again and GetChanges in the SqlCeClientSyncProvider doesn't pick up the fact that all these new rows that have been added (it is reporting zero changes for all four tables).

    04/05/2010 08:21:29: The sync of zero rows is 'successful' and the device will not try and sync these rows again because it thinks it has already sent them (the anchors are now set to after the rows have been added).

     

    The end result is a big problem to me because GetChanges is falsely saying there are zero changes and the UploadOnly sync groups do not re-send the rows that have not uploaded. This means that the client is not uploading all of its data to the server.

    This is the first time I've managed to reproduce this issue on my own device in this much detail, but the application is in production and missing data issue has been an on-going problem reported by the end-users. 

    Tuesday, May 4, 2010 8:47 AM
  • From the log we have

    04/05/2010 08:15:29 Start (synchronous) sync... (ClientID: 81e95aa2-4d79-41e7-9167-038b2ceeb711)
    04/05/2010 08:20:59 Start (asynchronous) sync... (ClientID: 81e95aa2-4d79-41e7-9167-038b2ceeb711)

    So one sync seems a synchronous sync and another one is asynchronous sync.  Any difference between these 2 synces? Is this a multi-threaded application?  Do you have one thread for the end-user to do INSERT/UPDATE data and another thread to sync?

    Thanks.


    Leo Zhou ------ This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, May 4, 2010 1:27 PM
    Answerer
  • The initial sync is in the foreground, and then once there is data on the device, the sync is run on a background thread while the user continues working. The sync is triggered by application logic each time a user completes some logical unit of work within the application so it runs every five minutes or so during normal use. To answer your question directly, the user enters data from forms on the main application thread (data updates applied via SqlCeCommand objects) and the sync works on a different thread. We are not caching any data in DataSets or using DataAdapters. In addition, in the instance above, the background sync was instigated after the rows had already been inserted.

    The issue is intermittent, everything seems to work correctly 90% of the time and this problem has only manifested itself because users have noticed some data not being uploaded (and to narrow the problem down this far has taken quite a long time, hence some other related threads from me on this board dating back several months - e.g. http://social.msdn.microsoft.com/Forums/en-US/syncdevdiscussions/thread/2e7af1a7-fc0a-43a5-8675-93a9beb327bc).

    The OutOfMemory error might not be the cause but it could be a clue, maybe GetChanges is silently aborting when there is low memory instead of raising an error. One of the tables in the above example contains binary data (photograps stored in 'image' fields), the photos aren't huge (less than 100kB) but thought it worth mentioning.

    Tuesday, May 4, 2010 2:57 PM
  • Hi, just thinking about this overnight and having read somewhere that __sysSyncArticles stores the anchor values for each sync. I don't know much about this table, but my app deletes the sdf file during 'log out' of the app. It then creates the file during 'log in' (by syncing a new database from the server). If I delete the sdf file, I assume that the __sysSyncArticles table is stored inside the same sdf file and there is no tracking data stored anywhere else? (just thinking if the database somehow gets out of step with the tracking tables)

    Wednesday, May 5, 2010 9:53 AM
  • you're using the offline provider where the client is the only one maintaining information on what was sent and received, so i think deleting the SDF wipes out all tracking information for that database.

    have you looked at this as well just in case your using the Compact method? http://support.microsoft.com/default.aspx/kb/969858/?p=1

     

    Wednesday, May 5, 2010 11:58 AM
    Moderator
  • Sorry to keep posting, just trying to resolve this issue as I'm under a lot of pressure from my client to resolve the issue. I noticed a post on a MSDN blog ( http://blogs.msdn.com/sidharth/archive/2009/04/23/change-tracking-fails-for-sqlce-version-3-5-5692-1.aspx ) stating a similar issue where "the __sysChangeTxBsn or __sysInsertTxBsn columns don’t get modified as you update or insert values into the database". This could also be a cause of the symptoms that I have described above. In my case the version of System.Data.SqlServerCe.dll is 3.5.5692.0 which according to the MSDN blog post, should work correctly.

    Wednesday, May 5, 2010 12:06 PM
  • Sorry, thanks JuneT, must have cross-posted. I started to use compacting very recently (to reduce app footprint on device) but the problem was happening before doing this, so don't this is the issue here. Thanks anyway and if you have any other thoughts I'd be glad to hear them.
    Wednesday, May 5, 2010 12:18 PM
  • install Sync FX v1 SP1? (assuming nothing in Sync Fx out-of-the box fixes it, you have at least access to the Change Tracking APIs to do it yourself)

    btw, i remember on your other post where MS suggested enabling the Sync Fx tracing, have you enabled that? just in case there is something there more verbose than what syncstatistics provides.

    you mention you have Sync Fx running in a background thread and is triggered by the UI, do you recycle the sync objects (sync agent, local and remote providers) in between calls or you just repeatedly invoke Synchronize on the same object over and over again?

     

    Wednesday, May 5, 2010 1:05 PM
    Moderator
  • Yes SP1, enabled tracing on the server (but not on the client as not sure if you can do verbose tracing on the client?) Each sync occurs on a new background thread. Multiple syncs will not occur at one time (no overlapping sync) When a sync completes, the thread dies. When the next sync occurs, a new thread is spawned. The sync agent is recycled (not a static object). I don't think the issue is threading since I spent a lot of time diagnosing this when we first found the problem with missing data. I'm really starting to think there is a problem in the SqlCeClientSyncProvider, I particularly now suspect the change metadata. Until I find the problem , I can't take on new work and my client is getting increasingly impatient because I have spent weeks (unpaid) trying to get to the bottom of this problem (so it's a big problem for me as well as the client!)

    Wednesday, May 5, 2010 1:53 PM
  • The MSDN blog post is certainly raising an important issue. It said:


     "... if you have an existing connection to the SQLCE DB open before the table in concern is configured for change tracking. It looks like the open connection
     does not recognise that change tracking has been enabled and treats the table as a normal table. The result of this is that *even if the table has been configured
     for change tracking*, if you use the same connection to update the table, the __sysChangeTxBsn or __sysInsertTxBsn do not get modified."

    I use a singleton pattern for my SqlCeConnection (to save opening and closing each time I make a database call which, as everyone knows, is quite an expensive
    operation on a device). In my app, on start-up, I show show a login form. Once the user is authenticated to the server, I retrieve and store
    some user details locally (config, permissions etc). I then sync some tables into the same local database.

    My app is multi-threaded with sync running on a worker thread (not unusual because users don't want to stop working while they wait for sync to finish).
    However, the SqlCeConnection itself is not thread safe and I think the problem has arisen because of this. All my SqlCeConnection, SqlCeCommand
    object operations are wrapped in 'lock' statements). However, all my change metadata is populated erratically (so erratically, that finding
    the cause of this problem has taken me weeks).

    The fact that the blog starts with the sentence with "It looks like...", very much sounds like the problem has not yet been officially verified, but it is
    a such a serious issue and it has not been flagged up very visibly in the documentation.

    Thursday, May 6, 2010 9:17 AM
  • hi jon,

    has  releasing the connection after you change tracking is enabled fixed the problem?

    regards,

    junet

    Thursday, May 6, 2010 11:33 AM
    Moderator
  • Hi junet, yes I explicity close my open connection before the initial sync (which is the only connection that my code uses to interact with the database). The initial sync occurs on the main thread (on which all data is populated), I don't see why the metadata wouldn't be being populated correctly since it was created on the main thread with no other open connections. The background sync uses a different thread to incrementally upload changes, but it is still pointing at the same database which should have been initialised corrrectly by the main thread.

    As I say, the system works fine 90% of the time (the 10% it doesn't work is the problem). For example, when a user logs out, the sdf file is deleted by my app and when the user logs back the file is created again but the change tracking no longer works (sometimes), but both times the sdf is created in the same way. (Hence an earlier question asking is all change tracking metadata stored in the sdf, or is there something else external).

    From the blog post (which is the only mention that I can find anywhere about this problem) it seemed like you have to make sure that there are no other connections before the initial sync occurs, The blog post is not entirely clear, but it is the only thing I have to go on and the problem seems too similar to my owjn to believe it is not true.


    Thursday, May 6, 2010 2:17 PM
  • Mmmm, interesting that you read it as *after* and me as *before*, I'll try closing/re-opening the database connection after the initial sync completes...
    Thursday, May 6, 2010 3:02 PM
  • My tentative findings are this problem could now be resolved! The bottom line appears to be, if your app has opened a SqlCeConnection to your client database before you have sync'd, you need to close/re-open the connecton once the initial sync has completed (especially if your app is always using the same connection), otherwise changes will not be tracked and your client will not upload all of its changes to the server (I'm not sure why the change tracking infrastructure only works correctly if you have a certain type of SqlCeConnection, but I'll leave that mystery for somebody else. There are no properties in the connection string that can be changed related to change tracking as far as I can tell, so there must be something under the hood that's different).

    I wish this issue had a bit more visibility because I don't expect to be the only person who has had this problem and it was exceptionally difficult to find out the cause.
    I hope this information helps somebody else. Thank you very much June for your input/help.
    • Marked as answer by SunHunter Friday, May 7, 2010 7:06 AM
    Thursday, May 6, 2010 3:55 PM