none
SyncAgent snapshot/truncate FK constraint error during truncation RRS feed

  • Question

  • Hello!

       I am trying to create snapshot. I am using SyncAgent snapshot with TruncateExistingOrCreateNewTable creation option. And it causes FK constraint exception. It is thrown, when it tries to delete some table, where are FK dependencies.

       Please, how can I force SyncAgent to delete/truncate existing client tables in correct order (topologically sorted according to FK dependencies)? If I manually sort the tables, it seems, it is not working still (or something is wrong on my side).

       Thanks.

           Radim

    Thursday, May 9, 2013 8:26 AM

All replies

  • It seems, that I have reversed order of the synced tables/adapters. But if I invert the order, it have still some problems (not all rows are synchronized). It looks (maybe, I'm not sure), like inverted order causes wrong synchronization/snapshot (missing dependent FK rows)...

    I have tested all order combinations which make some sense, but none order gives me correct result (sometimes FK constraint exception is thrown, sometimes not all rows are synced).

    Thursday, May 9, 2013 11:54 AM
  • when you add the tables, try adding the parent table first before the dependent ones (the one with FKs)
    Thursday, May 9, 2013 11:33 PM
    Moderator
  • Hi June!

       Thanks for your advice. I think, I have correct order of the tables. I have defined dependency graph which is topologically sorted, so it's 100% sure, that order is OK (if any unexpected issue occurs). I don't know only, if SyncAdapter[] and SyncTable[] arrays in SyncAgent should have the same order, but I have tried all combinations and it has no effect.

       For sure I show you the code used for SyncAgent instance creation (partially found on the net). Note that BackupTables are tables and columns defined for backup/snapshot and SortedTables are topologically sorted tables for backup/snapshot.

    protected override SyncAdapter[] CreateBackupAdapters(SqlConnection connection)
    {
    SyncAdapter[] adapters = new SyncAdapter[BackupTables.Count];
    int idx = 0;

    for (int i = 0; i < SortedTables.Count; i++)
    {
    string tableName = SortedTables[i];

    if (BackupTables.ContainsKey(tableName))
    {
    string[] columns = BackupTables[tableName];
    SyncAdapter adapter = CreateSyncAdapter(tableName, columns, connection);

    adapters[idx] = adapter;
    idx++;
    }
    }

    return adapters;
    }


    protected override SyncTable[] CreateBackupTables()
    {
    SyncTable[] tables = new SyncTable[BackupTables.Count];
    int idx = 0;

    for (int i = 0; i < SortedTables.Count; i++)
    {
      string tableName = SortedTables[SortedTables.Count - 1 - i]; // reversed order

    if (BackupTables.ContainsKey(tableName))
    {
    SyncTable table = new SyncTable(tableName);
    table.CreationOption = TableCreationOption.TruncateExistingOrCreateNewTable;
    table.SyncDirection = SyncDirection.Snapshot;

    tables[idx] = table;
    idx++;
    }
    }

    return tables;
    }

    protected SqlSyncAdapterBuilder CreateSyncAdapterBuilder(SqlConnection connection)
    {
    SqlSyncAdapterBuilder builder = new SqlSyncAdapterBuilder();

    builder.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
    builder.Connection = connection;
    builder.SyncDirection = SyncDirection.Snapshot;

    builder.CreationOriginatorIdColumn = "insert_orig_id";
    builder.CreationTrackingColumn = "insert_stamp";
    builder.DeletionOriginatorIdColumn = "delete_orig_id";
    builder.DeletionTrackingColumn = "delete_stamp";
    builder.UpdateOriginatorIdColumn = "update_orig_id";
    builder.UpdateTrackingColumn = "update_stamp";

    return builder;
    }

    protected SyncAdapter CreateSyncAdapter(string tableName, string[] columns, SqlConnection connection)
    {
    SqlSyncAdapterBuilder syncAdapterBuilder = CreateSyncAdapterBuilder(connection);

    // synchronized columns
    syncAdapterBuilder.TableName = tableName;
    syncAdapterBuilder.DataColumns.AddRange(columns);

    // tombstone
    syncAdapterBuilder.TombstoneTableName = tableName + "_tombstones";
    syncAdapterBuilder.TombstoneDataColumns.AddRange(columns);

    return syncAdapterBuilder.ToSyncAdapter();
    }

    public SyncAgent CreateBackuper(SqlConnection publisherConnection, string backupConnectionStr)
    {
    // anchor
    SqlCommand anchorCmd = new SqlCommand();
    anchorCmd.CommandType = CommandType.Text;
    anchorCmd.CommandText = "select @" + SyncSession.SyncNewReceivedAnchor + " = change_tracking_current_version()";
    anchorCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.BigInt).Direction = ParameterDirection.Output;
    anchorCmd.Connection = publisherConnection;

    // client ID
    SqlCommand clientIdCmd = new SqlCommand();
    clientIdCmd.CommandType = CommandType.Text;
    clientIdCmd.CommandText = "select @" + SyncSession.SyncOriginatorId + " = 1";
    clientIdCmd.Parameters.Add("@" + SyncSession.SyncOriginatorId, SqlDbType.Int).Direction = ParameterDirection.Output;

    // server
    DbServerSyncProvider serverSyncProvider = new DbServerSyncProvider();
    serverSyncProvider.Connection = publisherConnection;
    serverSyncProvider.SelectNewAnchorCommand = anchorCmd;
    serverSyncProvider.SelectClientIdCommand = clientIdCmd;
    SyncAdapter[] adapters = CreateBackupAdapters(publisherConnection);
    foreach (SyncAdapter adapter in adapters)
    {
    serverSyncProvider.SyncAdapters.Add(adapter);
    }

    // client
    SqlCeClientSyncProvider clientSyncProvider = new SqlCeClientSyncProvider(backupConnectionStr);
    clientSyncProvider.CreateDatabaseIfNotExists = true;

    // agent
    SyncAgent agent = new SyncAgent();
    agent.RemoteProvider = serverSyncProvider;
    agent.LocalProvider = (ClientSyncProvider)clientSyncProvider;
    SyncTable[] tables = CreateBackupTables();
    foreach (SyncTable table in tables)
    {
    agent.Configuration.SyncTables.Add(table);
    }

    return agent;
    }

    Friday, May 10, 2013 7:51 AM
  • can you post the exact error you're getting?

    Friday, May 10, 2013 10:52 AM
    Moderator
  • Now (in some tables order) there is no exact error, but synchronization ends with some count of "failures" (in SyncStatistics) - Not all rows are synchronized. If I lookup synchronized DB (the snapshot), there are missing some rows, which depends on other table(s) by FK(s).

    For example:

    Data rows of source (remote/server) table Box:

    Oid (PK) | BoxEsn | Area (FK) | Firmware   | Modem (FK) | Lock

    1        | 1206   | 1         | (NULL)     | (NULL)     | 1
    2        | 1975   | (NULL)    | 0x00090619 | (NULL)     | 1
    3        | 2000   | 2         | 0x0009061C | 1          | 1

    Data rows of synchronized snapshot (client) table Box (missing rows 1 and 3):

    Oid (PK) | BoxEsn | Area (FK) | Firmware   | Modem (FK) | Lock
    2        | 1975   | NULL      | 0x00090619 | (NULL)     | 1

    There is topological order of used SortedTables:

      [0] "Application"
      [1] "BoxConfig"
      [2] "BoxLog"
      [3] "ConfigureCommand"
      [4] "ConfigVariable"
      [5] "DatabaseUpdateLog"
      [6] "Modem"
      [7] "RemoteChannel"
      [8] "Report"
      [9] "ReportParameters"
      [10] "ReportStyle"
      [11] "ServiceConsole"
      [12] "UpdateLog"
      [13] "UpdateRequest"
      [14] "UserGroup"
      [15] "XPObjectType"
      [16] "AccessRightsAssign"
      [17] "User"
      [18] "Area"
      [19] "ExtendedAccess"
      [20] "BiancoBox"
      [21] "BiancoFolder"
      [22] "Box"
      [23] "MoveCommand"
      [24] "Rack"
      [25] "UserCard"
      [26] "ConfigCard"
      [27] "RestrictedAreas-ConfigCards"
      [28] "RestrictedBoxes-ConfigCards"

      So, SyncTable[] array has Box, Area, Modem tables in this order and SyncAdapter[] array has (now) Modem, Area, Box tables order. Or SyncTable[] array goes from 28 to 0, SyncAdapter[] array goes from 0 to 28.

      But if I invert SyncTable[] order (Modem, Area, Box), I have received following exception (related to different table than in example, of course) - this is reason, why I am using order above...:

    System.Data.SqlServerCe.SqlCeException was unhandled by user code
    Message="The primary key value cannot be deleted because references to this key still exist. [ Foreign key constraint name = FK_AccessRightsAssign_Application ]"
    Source="SQL Server Compact ADO.NET Data Provider"
    HResult=-2147217873
    NativeError=25025
    StackTrace:
    v System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
    v System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor)
    v System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
    v System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
    v Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.CreateSchema(SyncTable syncTable, SyncSchema syncSchema)
    v Microsoft.Synchronization.SyncAgent.InitClientSchema()
    v Microsoft.Synchronization.SyncAgent.DataSynchronize()
    v Phobos.PhBox.PHB005.AreaDatabase.AreaDatabaseNG.Backup(Version version, String comment)
    v Phobos.PhBox.PHB005.AreaConfigNG.Forms.DbBackupForm.backgroundWorkerBackup_DoWork(Object sender, DoWorkEventArgs e) v D:\Projects\PhBox\PHB-005\Sources\Applications\AreaConfigNG\Forms\DbBackupForm.cs:řádek 152
    v System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
    InnerException:

      I think, I need to force SyncAgent to delete/truncate tables in 28..0 order, but fill (sync) in 0..28 order (I think/hope, that my order is OK). Deletion/truncation seems to be OK, but synchronization fails (not all rows are synchronized).

    Friday, May 10, 2013 12:03 PM
  • There is probably mistake in my previous ideas in tables order, for deletion/truncation and filling (synchronization) there should be the same order, but the problem persists. Either I have exception during deletion/truncation or I have incomplete snapshot. Please, help, how can I do complete snapshot with truncation without exception...
    Friday, May 10, 2013 12:38 PM
  • the order of the syncadapters doesnt matter. that simply stores the commands to execute.

    the order in the synctables is the one that matters.

    if you're truncate succeeds and you're not getting all rows, check your table constraints if there's anything that's preventing a row from being inserted.

    try adding an event handler for ApplyChangeFailed event on the client sync provider to see if its raising an error.

    Friday, May 10, 2013 3:43 PM
    Moderator
  • I Have added mentioned handler with following error(s) (there are more errors, but all are similar):

    System.Data.SqlServerCe.SqlCeException:

    {"A foreign key value cannot be inserted because a corresponding primary key value does not exist. [ Foreign key constraint name = FK_Box_Area ]"}

    So, it seems as wrong SyncTable[] order, but if I revert the order, I received exception during truncation, as I said before. I don't understand... Or, I understand this exception, I think that order of SyncTable[] is really wrong now (reversed), but if I use correct order in my opinion (0..28), I have received truncation exception.

    Regardless to this SqlCeException: Box DB table have two FKs: Area nd Modem. So, Area and Modem should be prior to Box. Now it is not true. But if I revert the order, I have received truncation exception...

    I am using more tables now in my example (not only Box, Modem, Area). I can try to simplify my test to use only some tables (e.g. Box, Modem, Area), but I'm not sure, that it helps. So, will try it...


    Monday, May 13, 2013 8:38 AM
  • It becomes a night mare...

    I have minimalized count of DB tables to synchronize. I have tried two cases (different DB tables). None correct...

    Case No. 1 - XPObjectType, Area, Modem, Box (empty client tables):

    With correct order I have incorrect snapshot. It's like "complete", but different (missing some reference from Modem to Box, which is not FK).

    Case No. 2 - XPObjectType, Application, UserGroup, AccessRightsAssign, BoxConfig, Area, Modem, Box, User  (non-empty some client tables):

    With correct order, I have received truncation exception.

    With incorrect order, truncation seems to be OK, but snapshot is incomplete.


    • Edited by Radim Literak Tuesday, May 14, 2013 9:10 AM Specified
    Monday, May 13, 2013 9:20 AM
  • Maybe for sure some additional info about FKs:

    XPObjectType: ---

    Application: ---

    UserGroup: ---

    AccessRightsAssign: Application, UserGroup

    BoxConfig: --- 

    Area: Area, BoxConfig, XPObjectType

    Modem: ---

    Box: Area, Modem

    User: UserGroup


    Monday, May 13, 2013 10:09 AM
  • Issue with Case No. 1 resolved: There was missing some synchronized column of Modem table in SyncTable instance. So, it was my mistake.
    Monday, May 13, 2013 1:49 PM
  • After some experiences with SyncAgent and DB tables with FKs, I will try to do some (my) conclusion:

    It seems, that MS Sync Framework 2.1 doesn't support snapshot with truncation if DB tables contains FKs and at the same time client tables are not empty.

    Why I think it? Follow my previous example. Take into account DB tables XPObjectType, BoxConfig, Area, Modem and Box. If these tables are not empty, they must be truncated in following order (due to FKs dependences): Box, Modem, Area, BoxConfig, XPObjectType (more exactly, there should be more orders, it is one of possible orders). But if the same tables are filled/synchronized, there must be opposite order: XPObjectType, BoxConfig, Area, Modem, Box (of course, more orders are possible).

    E.g if I will try to truncate Modem prior to Box, it fails, because of Box depends on Modem. And I can't add Box first, if it depends on some Modem.

    So, definitely, there are two orders: One order for truncation and one order for filling/synchronization. But SyncAgent accepts only one order of SyncTable[] array. And it looks, that SyncAgent can't invert the order inside it's implementation automatically (should be simple task).

    I can't claim it with 100% sure, but all my experiences confirms my suspicion. I don't know about any settings how to force SyncAgent to truncate DB tables in correct order and simultaneously synchronize the same tables in correct order. I you know it, please tell me it.

    Tuesday, May 14, 2013 6:43 AM
  • easy fix, create an sql ce database with the schema you want pre-created already.

    when you want to get snapshot, create copy of this database and sync with snapshot. 

    when you want to reset and get snapshot again, delete, copy, sync.

    if you have other tables that's not part of what you snapshot, then just do the truncate yourself.

    Wednesday, May 15, 2013 1:31 PM
    Moderator