Invalid object name exception

[mrtncls]

We sync from a cloud db towards an edge db in one way both are using the SqlServerChangeTracking provider. We're using nuget 0.6.1 Both db's have changetracking enabled:

ALTER DATABASE CURRENT SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 14 DAYS, AUTO_CLEANUP = ON)

We're not able to reproduce but it happened 3 times on different systems. The sync was stable for weeks before it happened. It started when we added some records in the cloud db.

We want to understand what is causing this and how we can prevent this. Could this be related to the changetracking retention?

Investigations:

If we restart our application, the same exception will occur.

We can recover from it by removing the edge database and deprovision the sync (StoredProc, Triggers and TrackingTables).

Stacktrace from the sync command:

Dotmim.Sync.SyncException: Invalid object name 'TABLE2_tracking'.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'TABLE2_tracking'.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
at Dotmim.Sync.BaseOrchestrator.InternalUpdateMetadatasAsync(SyncContext context, DbSyncAdapter syncAdapter, SyncRow row, Nullable`1 senderScopeId, Boolean forceWrite, DbConnection connection, DbTransaction transaction)
at Dotmim.Sync.BaseOrchestrator.HandleConflictAsync(Guid localScopeId, Guid senderScopeId, DbSyncAdapter syncAdapter, SyncContext context, SyncRow conflictRow, SyncTable schemaChangesTable, ConflictResolutionPolicy policy, Nullable`1 lastTimestamp, DbConnection connection, DbTransaction transaction)
at Dotmim.Sync.BaseOrchestrator.InternalApplyChangesBatchAsync(SyncContext context, Boolean useBulkOperation, SyncTable changesTable, MessageApplyChanges message, DataRowState applyType, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken)
at Dotmim.Sync.BaseOrchestrator.InternalApplyTableChangesAsync(SyncContext context, SyncTable schemaTable, MessageApplyChanges message, DbConnection connection, DbTransaction transaction, DataRowState applyType, DatabaseChangesApplied changesApplied, CancellationToken cancellationToken, IProgress`1 progress)
at Dotmim.Sync.BaseOrchestrator.InternalApplyChangesAsync(SyncContext context, MessageApplyChanges message, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken, IProgress`1 progress)
at Dotmim.Sync.LocalOrchestrator.<>c__DisplayClass7_0.<<ApplyChangesAsync>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Dotmim.Sync.BaseOrchestrator.RunInTransactionAsync[T](SyncStage stage, Func`4 actionTask, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken)
ClientConnectionId:38d00dc8-c0bc-4c57-8a60-f3fa0c122c1f
Error Number:208,State:1,Class:16
--- End of inner exception stack trace ---
at Dotmim.Sync.BaseOrchestrator.RaiseError(Exception exception)
at Dotmim.Sync.BaseOrchestrator.RunInTransactionAsync[T](SyncStage stage, Func`4 actionTask, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken)
at Dotmim.Sync.BaseOrchestrator.RunInTransactionAsync[T](SyncStage stage, Func`4 actionTask, DbConnection connection, DbTransaction transaction, CancellationToken cancellationToken)
at Dotmim.Sync.SyncAgent.SynchronizeAsync(SyncType syncType, CancellationToken cancellationToken, IProgress`1 progress)

Sql profiler trace on edge database:

ALTER TABLE [TABLE1] NOCHECK CONSTRAINT ALL
go
ALTER TABLE [TABLE2] NOCHECK CONSTRAINT ALL
go
declare @p3 dbo.TABLE2_BulkType
insert into @p3 values(2238,NULL,NULL)
insert into @p3 values(2826,NULL,NULL)
insert into @p3 values(2830,NULL,NULL)
insert into @p3 values(2831,NULL,NULL)
insert into @p3 values(2842,NULL,NULL)
insert into @p3 values(2843,NULL,NULL)
insert into @p3 values(2844,NULL,NULL)
insert into @p3 values(2858,NULL,NULL)
insert into @p3 values(2871,NULL,NULL)
insert into @p3 values(2872,NULL,NULL)
insert into @p3 values(2874,NULL,NULL)
insert into @p3 values(2953,NULL,NULL)
insert into @p3 values(2968,NULL,NULL)

exec [dbo].[TABLE2_bulkdelete] @sync_min_timestamp=76,@sync_scope_id='00000000-0000-0000-0000-000000000000',@changeTable=@p3
go
declare @p5 int
set @p5=0
exec [dbo].[TABLE2_delete] @Id=2826,@sync_force_write=1,@sync_min_timestamp=76,@sync_scope_id='00000000-0000-0000-0000-000000000000',@sync_row_count=@p5 output
select @p5
go
exec sp_executesql N'UPDATE [side] SET
[update_scope_id] = @sync_scope_id,
[sync_row_is_tombstone] = @sync_row_is_tombstone,
[last_change_datetime] = GETUTCDATE()
FROM [TABLE2_tracking] [side]
WHERE [side].[Id] = @Id;

INSERT INTO [TABLE2_tracking] (
[Id]
,[update_scope_id], [sync_row_is_tombstone],[last_change_datetime] )
SELECT [i].[Id]
, i.sync_scope_id, i.sync_row_is_tombstone, i.UtcDate
FROM (
SELECT @Id as [Id]
,@sync_scope_id as sync_scope_id, @sync_row_is_tombstone as sync_row_is_tombstone, GETUTCDATE() as UtcDate) as i
LEFT JOIN [TABLE2_tracking] [side] ON [side].[Id] = [i].[Id]
WHERE [side].[Id] IS NULL;
',N'@Id int,@sync_scope_id uniqueidentifier,@sync_row_is_tombstone bit',@Id=2826,@sync_scope_id='00000000-0000-0000-0000-000000000000',@sync_row_is_tombstone=1
go

[Mimetis]

Regarding at your exception, seems it's occuring during a conflict resolution:

Dotmim.Sync.SyncException: Invalid object name 'TABLE2_tracking'.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'TABLE2_tracking'

at Dotmim.Sync.BaseOrchestrator.InternalUpdateMetadatasAsync()
at Dotmim.Sync.BaseOrchestrator.HandleConflictAsync()
at Dotmim.Sync.BaseOrchestrator.InternalApplyChangesBatchAsync()

The problem is that .. you don't have any tracking table since you are using change tracking.... Do you have any way to reproduce the error ?

[Mimetis]

I think I found the issue Seems it's happening when

• A client uses SqlSyncChangeTrackingProvider provider
• The conflict resolution is set to ConflictResolutionPolicy.ClientWins
• Server deletes a row and client deletes the same row
• A conflict is occuring, that is handled by client
• Exception is raised here

Can you confirm this setup ?

[Mimetis]

The bug will be resolved in the next release. As you did not upgraded yet, one solution you can use to prevent this bug is to intercept the conflict, assuming you are in the situation described in the last comment (SqlSyncChangeTrackingProvider on client and resolution set to ConflictResolutionPolicy.ClientWins)

This code is to put on the server side, using the RemoteOrchestrator:

agent.RemoteOrchestrator.OnApplyChangesFailed(args =>
{
    if (args.Conflict.Type == ConflictType.RemoteIsDeletedLocalIsDeleted 
    && args.Resolution == ConflictResolution.ClientWins)
    {
        args.Resolution = ConflictResolution.ServerWins;
    }
});

That way, you should not have the issue anymore

Let me know

[mrtncls]

In our application, we use the SyncAgent which is initialized like:

        public SyncAgent CreateSyncAgent()
        {
            var source = CreateSourceProvider(
                databasesConfig.DataSync.RemoteDbProvider,
                databasesConfig.DataSync.RemoteDbConnectionString);

            var target = CreateTargetProvider(
                databasesConfig.RulesDbProvider,
                databasesConfig.RulesDbConnectionString);

            return new SyncAgent(target, source, new SyncOptions(), GetSetup());
        }

        private SyncSetup GetSetup()
        {
            var dbStructure = dbStructureClient.GetTableAndColumnNames();

            var syncSetup = new SyncSetup(dbStructure.Select(x => x.Key));
            foreach (var table in syncSetup.Tables)
            {
                table.SyncDirection = SyncDirection.DownloadOnly;
                table.Columns.AddRange(dbStructure[table.TableName]);
            }

            return syncSetup;
        }

Is the conflict resolution ClientWins by default? Otherwise it should be ServerWins as we don't set it.

SyncDirection is DownloadOnly for all tables. Is it possible to have conflicts with DownloadOnly?

Our application is not writing to this DB. Is connects but has a readonly dbcontext.

The RemoteOrchestrator is only used for deprovisioning when we need to re-init the edge db (after schema changes).

        private async Task DeprovisionRemoteDatabase()
        {
            var serverScope = await remoteOchestrator.GetServerScopeAsync();

            await remoteOchestrator.DeprovisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers | SyncProvision.TrackingTable);

            serverScope.Setup = null;
            serverScope.Schema = null;

            await remoteOchestrator.SaveServerScopeAsync(serverScope);
        }

[Mimetis]

Does the failure happens on the server database or the client database ? (regarding the trace u get )

[radulacatus]

Hello, I am a colleague of Maarten's. We get the error on the client database.

[Mimetis]

Ok, it should work with this code

agent.LocalOrchestrator.OnApplyChangesFailed(args =>
{
    if (args.Conflict.Type == ConflictType.RemoteIsDeletedLocalIsDeleted
    && args.Resolution == ConflictResolution.ClientWins)
    {
        args.Resolution = ConflictResolution.ServerWins;
    }
})

I know it's not making sense when we read the code, but usually we are not resolving conflict on client side Anyway let me know if it's resolving your issue, until I release a new version

[mrtncls]

Ok, thanks for the code. We'll integrate it and monitor it for a few weeks and report back.

[Abdelrahmandev1984]

Hi

Is this issue solved now and released?

[Mimetis]

The issue is resolved and in the master branch, but not yet released You can grab the master branch and use it, if you are facing this issue

Next release is planned for July or August

[Abdelrahmandev1984]

Great, Thanks