SynchronizeAsync throw exception on unique index duplicate key value

[safaairaq100]

Hello, After making some changes on rows for testing the unique index duplicate key value behavior, it syncs if make provider UseBulkOperations = false , and it's ok but the var s1 = await agent.SynchronizeAsync(Dotmim.Sync.Enumerations.SyncType.Normal, progress); throw exception:

 ─  ─ Cannot insert a duplicate key row in object 'dbo.Tb_Doctors' with unique index 'Tb_Doctors_DoctorCode'. The duplicate key value is (222222).
The statement has been terminated.
Command Text:[dbo].[Tb_Doctors_update]
Command Type:UpdateRows
  └  ─  ─  ─ Cannot insert duplicate key row in object 'dbo.Tb_Doctors' with unique index 'Tb_Doctors_DoctorCode'. The duplicate key value is (222222).
The statement has been terminated.

and the synchronize stopped, can make it continue synchronizing but ignore the conflict (separate it in the fail table for example) or make the error event handle the error because it's not fire

  agent.LocalOrchestrator.OnApplyChangesErrorOccured(args =>
  {
      args.Resolution = ErrorResolution.ContinueOnError;
  });
  serverOrchestrator.OnApplyChangesErrorOccured(args =>
  {
      args.Resolution = ErrorResolution.ContinueOnError;
  });

I make 3 changes on different rows two are normal changes and one has a unique duplicate value, then make a sync to them the normal changes are synced successfully but there's no message telling me how many changes are applied just SynchronizeAsync throw an exception as above. so I think the behavior needs more enhancement to handle this type of errors. I just want to continue synchronizing the normal rows and separate the failed sync row in a table (new table) to let the end-user know there's an error with this row to resolve the duplicated values.

[Mimetis]

Is Tb_Doctors_DoctorCode an index you have created ?

If it's the case, that could be probably your error.

Are you able to create a small sample to reproduce it ? will help to debug / help you

[safaairaq100]

the Tb_Doctors_DoctorCode is a unique index and I know the exception will happen but the problem is SynchronizeAsync throws an exception without telling the success changes because I made 3 changes as I described in the previous comment.

[Mimetis]

I'm sorry, I don't get it

[safaairaq100]

Create database ex: CustomerDb for the client, and CustomerDb2 for the server

Client database CustomerDb:

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'CustomersDb')
BEGIN
    CREATE DATABASE CustomersDb;
END
GO
Use CustomersDb
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customers]') AND type in (N'U'))
BEGIN
    CREATE TABLE Customers (
        CustomerId UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        CustomerName NVARCHAR(255) NOT NULL,
        CustomerCode NVARCHAR(255) NOT NULL UNIQUE
    );
END
GO
IF NOT EXISTS (SELECT * FROM Customers)
BEGIN
    INSERT INTO Customers (CustomerId,CustomerName, CustomerCode)
    VALUES 
    ('C01788F3-3C6F-4A9D-8B9D-58FCC574B0BC','Alice Johnson', 'CUST001'),
    ('F1C68CB9-FB3C-41E6-A9A7-9996F961A07E','Bob Smith', 'CUST002'),
    ('D517F23A-8FC1-4833-8894-F821A1C5F69A','Charlie Brown', 'CUST003');
END
GO
IF NOT EXISTS (SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('CustomersDb'))
BEGIN
   ALTER DATABASE CustomersDb
   SET CHANGE_TRACKING = ON 
   (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
END

Server Database CustomerDb2:

 IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'CustomersDb2')
BEGIN
    CREATE DATABASE CustomersDb2;
END
GO
Use CustomersDb2
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customers]') AND type in (N'U'))
BEGIN
    CREATE TABLE Customers (
        CustomerId UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
        CustomerName NVARCHAR(255) NOT NULL,
        CustomerCode NVARCHAR(255) NOT NULL UNIQUE
    );
END
GO
IF NOT EXISTS (SELECT * FROM Customers)
BEGIN
    INSERT INTO Customers (CustomerId,CustomerName, CustomerCode)
    VALUES 
    ('C01788F3-3C6F-4A9D-8B9D-58FCC574B0BC','Alice Johnson', 'CUST001'),
    ('F1C68CB9-FB3C-41E6-A9A7-9996F961A07E','Bob Smith', 'CUST002'),
    ('D517F23A-8FC1-4833-8894-F821A1C5F69A','Charlie Brown', 'CUST003');
END
GO
GO
IF NOT EXISTS (SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('CustomersDb2'))
BEGIN
   ALTER DATABASE CustomersDb2
   SET CHANGE_TRACKING = ON 
   (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
END

--Make some changes to the client database (valid changes)
Update [CustomersDb].[dbo].[Customers] set CustomerName='Charlie William' where CustomerId='C01788F3-3C6F-4A9D-8B9D-58FCC574B0BC'
Update [CustomersDb].[dbo].[Customers] set CustomerName='George Reece' where CustomerId='F1C68CB9-FB3C-41E6-A9A7-9996F961A07E'

--Insert new customer with code CUST004 in the server database
INSERT INTO [CustomersDb2].[dbo].[Customers] (CustomerId,CustomerName, CustomerCode)
VALUES 
(NEWID(),'Jack Connor', 'CUST004')
--insert the same code to make a unique index exception
--Insert a new customer with code CUST004 in the client database (invalid)
INSERT INTO [CustomersDb].[dbo].[Customers] (CustomerId,CustomerName, CustomerCode)
VALUES 
(NEWID(),'Sara Oliver', 'CUST004')

--After making the sync the valid changes in the update operation must be applied on the server
--The sync results: no changes are applied all operations are canceled and SynchronizeAsync throws an exception

SELECT TOP (1000) [CustomerId]
      ,[CustomerName]
      ,[CustomerCode]
  FROM [CustomersDb].[dbo].[Customers] order by CustomerCode
  SELECT TOP (1000) [CustomerId]
      ,[CustomerName]
      ,[CustomerCode]
  FROM [CustomersDb2].[dbo].[Customers] order by CustomerCode

Client:

namespace HelloWebSyncClient
{
    class Program
    {
        private static string _clientConnectionString;
        static async Task Main(string[] args)
        {
            _clientConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            ExecuteScript.CreateDatabase(_clientConnectionString);

            Console.WriteLine("Be sure the web api has started. Then click enter..");
            Console.ReadLine();
            await SynchronizeAsync();
        }

        private static async Task SynchronizeAsync()
        {

            var serverOrchestrator = new WebRemoteOrchestrator("https://localhost:44342/api/sync");

            var clientProvider = new SqlSyncChangeTrackingProvider(_clientConnectionString){UseBulkOperations = false};

            var options = new SyncOptions
            {
                BatchSize = 1000,
                ConflictResolutionPolicy = ConflictResolutionPolicy.ServerWins,

            };
            var agent = new SyncAgent(clientProvider, serverOrchestrator, options);

            agent.LocalOrchestrator.OnApplyChangesErrorOccured(args =>
            {
                args.Resolution = ErrorResolution.ContinueOnError;
            });
            serverOrchestrator.OnApplyChangesErrorOccured(args =>
            {
                args.Resolution = ErrorResolution.ContinueOnError;
            });
            serverOrchestrator.OnApplyChangesConflictOccured(args =>
            {
                args.Resolution = ConflictResolution.ServerWins;
            }); 

            do
            {
                try
                {
                    var progress = new SynchronousProgress<ProgressArgs>(args => Console.WriteLine($"{args.ProgressPercentage:p}:\t{args.Message}"));

                    // Launch the sync process
                    var s1 = await agent.SynchronizeAsync(Dotmim.Sync.Enumerations.SyncType.Normal, progress);
                    // Write results
                    Console.WriteLine(s1);

                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }

            } while (Console.ReadKey().Key != ConsoleKey.Escape);

            Console.WriteLine("End");
        }
    }

Server:

namespace HelloWebSyncServer
{
    public class Startup
    {
        public Startup(IConfiguration configuration) => Configuration = configuration;

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers();

            services.AddDistributedMemoryCache();
            services.AddSession(options => options.IdleTimeout = TimeSpan.FromMinutes(30));

            var connectionString = Configuration.GetSection("ConnectionStrings")["SqlConnection"];

            ExecuteScript.CreateDatabase(connectionString);

            var options = new SyncOptions
            {
                SnapshotsDirectory = "C:\\Tmp\\Snapshots",
                BatchSize = 2000,

            };

            var tables = new SyncSetup
            {
                Tables = new SetupTables(new []{"Customers"}),
            };

            var provider = new SqlSyncChangeTrackingProvider(connectionString) {UseBulkOperations = false};
            services.AddSyncServer(provider, tables, options);
        }

        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
                app.UseDeveloperExceptionPage();

            app.UseHttpsRedirection();
            app.UseRouting();
            app.UseAuthorization();
            app.UseSession();
            app.UseEndpoints(endpoints => endpoints.MapControllers());
        }
    }
}

[Mimetis]

I used options.ErrorResolutionPolicy = ErrorResolution.ContinueOnError; (on both side, server and client)

And I get this result:

100,00 %:       [Cust] SessionEndArgs: Session Ends. 
Synchronization done.
        Total changes  uploaded: 1
        Total changes  downloaded: 3
        Total changes  applied on client: 2
        Total changes  applied on server: 0
        Total changes  failed to apply on client: 1
        Total changes  failed to apply on server: 1
        Total resolved conflicts: 0
        Total duration :00.00:12.246
-------------------- 

I've made the same test without this option, but using the interceptor (as you did)

options.DisableConstraintsOnApplyChanges = true;

var progress = new SynchronousProgress<ProgressArgs>(s =>
    Console.WriteLine($"{s.ProgressPercentage:p}:  " +
    $"\t[{s?.Source?[..Math.Min(4, s.Source.Length)]}] {s.TypeName}: {s.Message}"));

//options.ErrorResolutionPolicy = ErrorResolution.ContinueOnError;
var agent = new SyncAgent(clientProvider, serverProvider, options);

agent.LocalOrchestrator.OnApplyChangesErrorOccured(args =>
{
    Console.WriteLine("error on client for row " + args.ErrorRow);
    args.Resolution = ErrorResolution.ContinueOnError;
});

agent.RemoteOrchestrator.OnApplyChangesErrorOccured(args =>
{
    Console.WriteLine("error on server for row " + args.ErrorRow);
    args.Resolution = ErrorResolution.ContinueOnError;
});
do
{
    try
    {
        Console.ForegroundColor = ConsoleColor.Green;
        var s = await agent.SynchronizeAsync(scopeName, setup, SyncType.Normal, default, default, progress);
        Console.WriteLine(s);
    }
    catch (SyncException e)
    {
        Console.ResetColor();
        Console.WriteLine(e.Message);
    }
    catch (Exception e)
    {
        Console.ResetColor();
        Console.WriteLine("UNKNOW EXCEPTION : " + e.Message);
    }
    Console.WriteLine("--------------------");
} while (Console.ReadKey().Key != ConsoleKey.Escape);

and the result is:

0,00 %:         [Cust] SessionBeginArgs: Session Begins. Id:07901f16-4e72-4f81-b91e-b909e96516a2. Scope name:DefaultScope.
0,00 %:         [Cust] SessionBeginArgs: Session Begins. Id:07901f16-4e72-4f81-b91e-b909e96516a2. Scope name:DefaultScope.
10,00 %:        [Cust] TableChangesSelectedArgs: [Customers] [Total] Upserts:1. Deletes:0. Total:1.
10,00 %:        [Cust] BatchChangesCreatedArgs: [Customers] [Modified] Batch Customers_0000_UPSERTS_a5ihfnqg_dwh.json (1) Created.
30,00 %:        [Cust] DatabaseChangesSelectedArgs: [Total] Upserts:1. Deletes:0. Total:1. [CustomersDb2_LOCAL_GETCHANGES_2024_06_16_09ojiv5f5bwmf]
30,00 %:        [Cust] BatchChangesAppliedArgs: [Customers] [Modified] Batch Customers_0000_UPSERTS_a5ihfnqg_dwh.json (1/1) Applied.
error on server for row [Sync state]:Modified, [CustomerId]:c07247f1-9303-49e3-9a42-aab0ec2c4b6d, [CustomerName]:Jack Connor, [CustomerCode]:CUST004
55,00 %:        [Cust] BatchChangesCreatedArgs: [Customers] [Modified] Batch Customers_0000_UPSERTS_uwhz5tlg_ggz.json (1) Created.
55,00 %:        [Cust] TableChangesSelectedArgs: [Customers] [Total] Upserts:3. Deletes:0. Total:3.
75,00 %:        [Cust] DatabaseChangesSelectedArgs: [Total] Upserts:3. Deletes:0. Total:3. [CustomersDb_REMOTE_GETCHANGES_2024_06_16_231bfbm4wylnn]
75,00 %:        [Cust] BatchChangesAppliedArgs: [Customers] [Modified] Batch Customers_0000_UPSERTS_uwhz5tlg_ggz.json (1/1) Applied.
error on client for row [Sync state]:Modified, [CustomerId]:e2da2508-24c2-44c6-bcd7-bd64cdd98d9c, [CustomerName]:Sara Oliver, [CustomerCode]:CUST004
91,67 %:        [Cust] TableChangesAppliedArgs: [Customers] Changes Modified Applied:2. Resolved Conflicts:0.
91,67 %:        [Cust] DatabaseChangesAppliedArgs: [Total] Applied:2. Conflicts:0.
100,00 %:       [Cust] SessionEndArgs: Session Ends. Id:07901f16-4e72-4f81-b91e-b909e96516a2. Scope name:DefaultScope.
100,00 %:       [Cust] SessionEndArgs: Session Ends. Id:07901f16-4e72-4f81-b91e-b909e96516a2. Scope name:DefaultScope.
Synchronization done.
        Total changes  uploaded: 1
        Total changes  downloaded: 3
        Total changes  applied on client: 2
        Total changes  applied on server: 0
        Total changes  failed to apply on client: 1
        Total changes  failed to apply on server: 1
        Total resolved conflicts: 0
        Total duration :00.00:15.042 

I did not used HTTP connection, but a simple TCP example (without the web orchestrator) Can you try this example ?

[Mimetis]

I think you missed the error handling on server side. You can use the SyncOptions on server side:

var options = new SyncOptions { ErrorResolutionPolicy = ContinueOnError };
services.AddSyncServer(provider, setup, options);

[safaairaq100]

thank you, after adding ErrorResolutionPolicy = ErrorResolution.ContinueOnError on the server side, I got results successfully as expected but the agent.LocalOrchestrator.OnApplyChangesErrorOccured and agent.RemoteOrchestrator.OnApplyChangesErrorOccured events do not fire in the client

 agent.LocalOrchestrator.OnApplyChangesErrorOccured(args =>
 {
     Console.WriteLine("error on client for row " + args.ErrorRow);
     args.Resolution = ErrorResolution.ContinueOnError;
 });

 agent.RemoteOrchestrator.OnApplyChangesErrorOccured(args =>
 {
     Console.WriteLine("error on server for row " + args.ErrorRow);
     args.Resolution = ErrorResolution.ContinueOnError;
 });

[safaairaq100]

The agent.LocalOrchestrator.OnApplyChangesErrorOccured is fired if apply downloaded rows in the client failed, but when the uploaded row fails to apply to the server the agent.RemoteOrchestrator.OnApplyChangesErrorOccured does not fire.

[Mimetis]

When you are on a client (using an http connection) the agent.RemoteOrchestrator.OnApplyChangesErrorOccured does nothing, as everything is happening on the server side.

By default, since your client does not have access to what happened on the server side (obviously !!) almost everything from agent.RemoteOrchestrator, from the client side, will never raise anything

If you want to have something happening on the server side, you need to implement the code on the server side.

So, two solutions (choose the one you prefer), on the server side:

1) Using a SyncOptions with a global behavior, implemented in your ConfigureServices method (in Startup.cs file):

var options = new SyncOptions { ErrorResolutionPolicy = ContinueOnError };
services.AddSyncServer(provider, setup, options);

2) Or Implementing the logic in your controller:

private WebServerAgent webServerAgent;

// Injected thanks to Dependency Injection
public SyncController(WebServerAgent webServerAgent) => this.webServerAgent = webServerAgent;

[HttpPost]
public async Task Post()
{
    webServerAgent.RemoteOrchestrator.OnApplyChangesErrorOccured(args =>
    {
        args.Resolution = ErrorResolution.ContinueOnError;
    });

    await webServerAgent.HandleRequestAsync(this.HttpContext);
}

[safaairaq100]

As a client, how do I know that the record failed to be applied to the server? Could the server return the records and the exceptions that failed to be applied to the server in response to let the client know that this record was not synchronized?

[Mimetis]

Just made the test using an HTTP configuration, and I have the failed changed on the server (as a result on the client side):

[Mimetis]

Oh wait you are asking about the "records", not the count of failed records. I need to see what you can do to get this info, but it's not "by design" in DMS

[safaairaq100]

Thank you. I took a lot of your time, if there was no ready-made way, I can make end-point in the server and ask about the records that failed from the client. Thank you again 🌹🌹🌹

[Mimetis]

You can do something using the AdditionalProperties properties from SyncContext. This property is not used internally, it's exposed and will transit on every request / response:

Server Side:

webServerAgent.RemoteOrchestrator.OnApplyChangesErrorOccured(args =>
{
    // not mandatory, if you have set the ErrorResolutionPolicy in the options
    args.Resolution = ErrorResolution.ContinueOnError;

    // add the error to the AdditionalProperties dictionary:
    if (args.Context.AdditionalProperties == null)
        args.Context.AdditionalProperties = new Dictionary<string, string>();

    args.Context.AdditionalProperties.Add(args.ErrorRow.ToString(), args.Exception.Message);

});

Client Side:

var agent = new SyncAgent(clientProvider, remoteOrchestrator, options);

agent.LocalOrchestrator.OnSessionEnd(args =>
{
    if (args.Context.AdditionalProperties != null && args.Context.AdditionalProperties.Count > 0)
    {
        Console.WriteLine("Errors on server side");
        foreach (var kvp in args.Context.AdditionalProperties)
            Console.WriteLine($"Row {kvp.Key} \n Error:{kvp.Value}");
    }
});

// make a synchronization to get all rows between backup and now
var s = await agent.SynchronizeAsync(progress: localProgress);

For now, you need to use the OnSessionEnd interceptor to get the SyncContextobject, but in the next version, the SyncContextwill be directly exposed in the SyncResult. Something like that, I guess:

var agent = new SyncAgent(clientProvider, remoteOrchestrator, options);

// make a synchronization to get all rows between backup and now
var s = await agent.SynchronizeAsync(progress: localProgress);

 if (s.Context.AdditionalProperties != null && s.Context.AdditionalProperties.Count > 0)
    {
        Console.WriteLine("Errors on server side");
        foreach (var kvp in s.Context.AdditionalProperties)
            Console.WriteLine($"Row {kvp.Key} \n Error:{kvp.Value}");
    }