locked
Misterious redundancy of data after the synchronization on the client! I'm needing HELP ? RRS feed

  • Question

  • Hi, I'm João and i'm developing an windows application that synchronizes data bidirectionaly, so everything works just fine because in all the tables with bidirectional sync i never insert new rows i just update data in the existing rows so when i synchronize the client uploads that information and then downloads the changes, until here so far so good, but i have a special case, i have a table in the client where i create new rows its a table with a very basic structure the fields are like this:

    • AnomalieSolutionID | SolutionID | AnomalieID

    So my problem is:

     

    - when i add the new rows on the client i check to see what happens on the client database and i have my records there like its supposed to be;

     

    - when i synchronize i have the new rows on the server too...due to my low experience in the area i want to make an observation that is: the date of the creation date is one hour late in the server than it is on the client, probably because on the client i call the DateTime.Now to set the creation date and on the server its called the function getutcdate()..i dont now if this can be the cause of the problem. anyway, continuing...

     

    - after the synch i check the client database and i have my records with the same date that is on the server now (one hour late) and i have one extra record duplicated, exactly like this (this is very strange, im beginning to believe in machine gost's):

     

    AnomalieSolutionID | SolutionID | AnomalieID

    1                                  100           125

    2                                  100           126

    100                              100            125

     

    Does some one know why??? the table is set to bidirectional sync like the other tables where i need bidirectional sync.

    I'm really sad, this app its interoperating with an web application and its everything working really good. But this bug

    could mean a project failure and this cant happen.

     

    Can somebody give me an hand on this??

     

    Thanks in advance.

    • Moved by Max Wang_1983 Friday, April 22, 2011 7:45 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, April 22, 2008 10:23 PM

Answers

  • Glad to see both of you come up with different solutions ( workarund ) to address this problem. One of the design goal of the sync service is to only ship the building blocks and let users to choose different blocks to design/impl their own sulotions based their own needs and business logics. sometime this may be too flexible but is would be still good to keep this as the general design goal. the way Synchor does for his case is a good example.

     

    To Answer JP's two questions: all the queries on the sync adapter are specified by users, i.e. users can based on his or her needs to use very simple ones ( like just do select * from tableName for snapshot sync direction cases since the app doesn't care about incremental changes, etc ) to very complex ones ( such as a big stored procedure to handle all the conflicts, differen cases etc ). in general, the where clause of the query should contain such where clause in order to support bidir sync

    CreationTracking > @__sync_last_received_anchor AND CreationTracking <= @__sync_new_received_anchor AND CreationOrignatorId <> @__sync_originator_id

     

    as we can see, this is NOT in the query you have.

     

    the way you did to solve it can work, but would introduce some performance overhead, since you need to elimited the rows after they are selected on the server. I would suggestion to re-consider ( just my suggestion ).

     

    thanks

    Yunwen

    Friday, May 16, 2008 12:53 AM
    Moderator

All replies

  • Hi  João,

     

    could you send out the command text on the sync adapter for this table , and the table defincation on the server (includes the LastEditDate and the CreationDate ) and the default constraint for them, and the triggers on it ?

     

    thanks

    Yunwen

    Saturday, April 26, 2008 2:12 AM
    Moderator
  • Hi Yunwen,

     

    I would like to, but how can i find the command text of the sync adapter for that table ??

     

     

     

    JP

    Tuesday, April 29, 2008 9:30 PM
  • If you added a dataset to your project and have your local tables in the dataset, open the dataset in the designer.

     

    Each table should show two sections: table name and fields; and [table name]TableAdapter.

     

    If you click on [table name]TableAdapter, the Properties window should show InsertCommand, SelectCommand, UpdateCommand, and so on; expanding any of these will show (among other info) the CommandText, which you can also edit.

     

    I am running into a similar problem of redundancy, where newly created local records are being synced fine, but since the created and/or updated dates are set at the server and a new primary key is also being set on the server, the local record persists even when the correctly saved now-at-the-server record also comes back. Still, HTH.

    Thursday, May 1, 2008 3:09 PM
  • you can add code to your application to print out the commands, e.g. assume you sync adapter for the table is syncAdpTable1.

     

    you can log them out as: syncAdpTable.SelectIncrementInsertCommand.CommandText.

     

    I suspect that the command lack of a logic to filter out the row just inserted by the client so that the row was looped back to the same client and hence you are using Identity as the PK column, hence the extra, redundancy row was inserted to the table.

     

    thanks

    Yunwen

    Sunday, May 4, 2008 6:00 PM
    Moderator
  • I'm using guid id's not identity sorry its my fault i put it wrong in the example. All the commands are right in my table adapters. the strange thing here is that the primary key has the same value of the anomalie

     

     

    id                     anomalieid    solutionid lasteditdate creation date

    AAA-BB1         AAA-BB1        SSAA    ....................................

    AAA-BB2         AAA-BB2        SSAA    ....................................

    AAA-BB3         AAA-BB3        SSAA    ....................................

     

    if lets suppose that i have the anomalie AAA-BB1 associated with another solution which the code could be lets say

    SSBB, i think you can already guess whats gonna happen the sync services try to insert something like this

     

    id                     anomalieid    solutionid lasteditdate creation date

    AAA-BB1         AAA-BB1        SSAA    ....................................

    AAA-BB2         AAA-BB2        SSAA    ....................................

    AAA-BB3         AAA-BB3        SSAA    ....................................

    AAA-BB1        AAA-BB1         SSBB  X --> and guess what happens, it fails duplicated PK !!!

     

     

    Why? None of my code tells the machine to behave like this! All my steps were taken by the book!

     

    Add local database cache

     

    Specify tables to sync

     

    Add them to the local dataset

     

    Change the bidirectional tables ID to GUID's because IDENTITY its not sync friendly

     

    Specify in the sync agent the direccions of the tables you want to sync bidirectionaly trought the sync direccion

    of the table

     

    Very simple steps, very simple code... but still... i'm having the bad luck of being experiencing this ... situation.

     

    I ran out of ideas, i know where is the problem i just dont know why it happens! When i insert manually data into my

    local database everything works fine, it uploads to the server fine, it downloads and everything stops being fine!!! Tongue Tied

    Tuesday, May 6, 2008 2:54 PM
  • Let me start by stating that I too am a newbie at this so my solution might have as of yet unknown problems. I was having the same issue as you and I also use Guids as the primary key. I was leaving the  CreationDate and RowVersion columns (used for Insert tracking and update tracking respectively) as null when doing the insert on the client. When I sync to the server it sets these columns for me and I was assuming it would send this back to me as an update. However, it sends it back to me as an insert. The easy (and I hope correct) solution was to set the ResolveAction to ServerWins. I've only done minimal testing but so far that seems to work.

    Below is how I extended the classes (yourName will be replaced by your prefix for your sync classes). I am not showing all my code for these methods just that germane to this discussion.

     

    Extend the SyncAgent class via the partial method OnInitialize

     

    public partial class yourNameCacheSyncAgent

    {

    partial void OnInitialized()

    {

    yourNameCacheClientProvider client_provider = (yourNameCacheClientProvider)this.LocalProvider;

    client_provider.SyncAutoResolveConflictsClient();

    }

     

    }

     

        public partial class yourNameCacheClientSyncProvider
        {
            public void SyncAutoResolveConflictsClient()
            {

    this.ConflictResolver.ClientInsertServerInsertAction = ResolveAction.ServerWins;

     

    }

    }

    Friday, May 9, 2008 8:23 PM
  • Hi, thanks for pitching in.:-)

     

    However, setting the conflict resolution will hide this problem ( well, maybe ), but I suspect the root cause is that the insert  was sent back to the same client who jsut did the insert on the server, either you are using GUID or Identity column, you will see extra rows on the client.

     

    the reason I want to see the SelectIncrementalInsert command at the sync adapter for this table is that there should be logic to avoid this in this command ( something like: where .... CreateOriginatorID <> @ClientId ). if this condition is missing from the query, you will see this behavior.

     

    thanks

    yunwen

    Tuesday, May 13, 2008 2:09 AM
    Moderator
  • Hi Yunwen,

     

    Two questions:

     

    1. I understand the logic but wasn't that suposed to be like that out of the box? Its obvious that i dont want to insert the records again in the client app that have just finished sending the records.

     

    2. How can i check the client id of the client app to add it as a query parameter??(here is my code for SelectIncrementalInserts)

     

    // HabitacaoElementoSubElementoAnomaliaSolucaoCorrectivaSyncTableSelectIncrementalInsertsCommand command.

    this.SelectIncrementalInsertsCommand = new System.Data.SqlClient.SqlCommand();

    this.SelectIncrementalInsertsCommand.CommandText = @"SELECT [HabitacaoElementoSubElementoAnomaliaSolucaoCorrectivaID], [HabitacaoElementoSubElementoAnomaliaID], [SolucaoCorrectivaID], [LastEditDate], [CreationDate] FROM dbo.HabitacaoElementoSubElementoAnomaliaSolucaoCorrectiva WHERE ([CreationDate] > @sync_last_received_anchor AND [CreationDate] <= @sync_new_received_anchor)";

    this.SelectIncrementalInsertsCommand.CommandType = System.Data.CommandType.Text;

    this.SelectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_last_received_anchor", System.Data.SqlDbType.DateTime));

    this.SelectIncrementalInsertsCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@sync_new_received_anchor", System.Data.SqlDbType.DateTime));

     

    I solved my problem for now doing the following:

     

    I subcribed the event ApplyingChanges of the SyncAgent.LocalProvider

     

    I check when the table Habitacao..SolucaoCorrectiva is going to be submited for changes

    and what i do mainly is to iterate through each of the rows in the Added state and add them

    manually through my table adapter, after that i call the datatable.clean() method so no data is

    synchronized by the ado.net sync services because i already added the data by myself, and it

    works fine.

     

    I know that this is not the best aproach but unfortunatly this were the measures i had to

    take to acomplish the deadline on time! Another reason why i took this measure was

    because its a silly problem, makes no sense, above when i say that i check the datatable

    i really checked all the data inside of it and guess? all the data is absolutely correct

    there is no problem with the selectincrementalinserts command because for what seems

    i have my data correct in the datatable during the applying changes! And my method

    for insert in my table adapter its correct too, because i call it manually and it inserted the

    records fine, so if the sync services are calling my insert method this situation is very

    uncommon (when i call it manually, it works!).

     

    Still, i continue searching for an adequated solution just for curiosity..

     

    Thanks,

     

    JP

     

    Wednesday, May 14, 2008 1:35 AM
  • Since I use the GUID as the primary key, I can't get a duplicated row on the client. While I was developing this app. I initially  trapped  for all conflicts and wrote them out and I would immediately see the PK conflict. Setting the conflict resolution to ServerWins does hide the behavior, but as far as I can tell the behavior is NOT a problem, in fact it is a good thing for me.  All that happens is the server sends back the inserted record with the CreationDate and RowVersion columns properly filled in and it replaces my newly inserted record. I lose no data and I get exactly the result I want, which is an exact copy of the record that the server now has.

     

     

    Wednesday, May 14, 2008 5:36 PM
  • Glad to see both of you come up with different solutions ( workarund ) to address this problem. One of the design goal of the sync service is to only ship the building blocks and let users to choose different blocks to design/impl their own sulotions based their own needs and business logics. sometime this may be too flexible but is would be still good to keep this as the general design goal. the way Synchor does for his case is a good example.

     

    To Answer JP's two questions: all the queries on the sync adapter are specified by users, i.e. users can based on his or her needs to use very simple ones ( like just do select * from tableName for snapshot sync direction cases since the app doesn't care about incremental changes, etc ) to very complex ones ( such as a big stored procedure to handle all the conflicts, differen cases etc ). in general, the where clause of the query should contain such where clause in order to support bidir sync

    CreationTracking > @__sync_last_received_anchor AND CreationTracking <= @__sync_new_received_anchor AND CreationOrignatorId <> @__sync_originator_id

     

    as we can see, this is NOT in the query you have.

     

    the way you did to solve it can work, but would introduce some performance overhead, since you need to elimited the rows after they are selected on the server. I would suggestion to re-consider ( just my suggestion ).

     

    thanks

    Yunwen

    Friday, May 16, 2008 12:53 AM
    Moderator