Can't make a query on primary keys since number of primary keys columns in criteria is not matching the number of primary keys columns in this table

[gabitavanatorul]

Can't make a query on primary keys since number of primary keys columns in criteria is not matching the number of primary keys columns in this table

[workgroupengineering]

I found out what caused my issue, but so far I have not been able to solve it. So basically, on the server side (PostgreSQL) I have a table that has a foreign key to itself and so when the SyncronizeAsync() encounters this table, it tries to add a row that has a foreign key to another row that doesn't exist (hasn't been added on the client yet). Is there a way to prevent this?

Ttry set DisableConstraintsOnApplyChanges to true in SyncOptions. see this.

[Mimetis]

Can you make a small repro sample, that i can use to reproduce ?

[Mimetis]

not enough to reproduce. Need your database schema and some data, that will allow me to reproduce the issue.
and please use correct markdown to format your code

(Be SURE that your sample will reproduce the issue)

[Mimetis]

Your dump is containing only one schema, and your code is not reproducing the error.
Please create a Minimum Viable Sample and the steps required to reproduce the error

[gabitavanatorul]

Server:
Startup.cs:
private void DotminSyncSetup(IServiceCollection services)
{
var con = Configuration.GetConnectionString("Database");
services.AddDistributedMemoryCache();
services.AddSession(options => options.IdleTimeout = TimeSpan.FromMinutes(30));
var setup1 = GetSetup("Schema1");
var setup2 = GetSetup("Schema2");
services.AddSyncServer(con, "Schema1", setup1);
services.AddSyncServer(con, "Schema2", setup2);
}
private SyncSetup GetSetup(string schema)
{
var tablesString = Configuration.GetSection("Tables")[schema];

        var tables = tablesString.Split(',');

        var setup = new SyncSetup(tables);

        foreach (var table in tables)
        {
            setup.Tables[table].SchemaName = schema;
        }
        return setup;
    }

ConfigureServices (IServiceCollection services){
//...
DotminSyncSetup(services);
//...
}

Sync handler:
public class SyncController : ControllerBase
{
private IEnumerable _webserverAgents;

    // Injected thanks to Dependency Injection
    public SyncController(IEnumerable<WebServerAgent> webServerAgents)
    {
        _webserverAgents = webServerAgents;
    }
    [HttpPost]
    public async Task Sync()
    {
        var scopeName = HttpContext.GetScopeName();

        var webserverAgent = _webserverAgents.FirstOrDefault(
                c => c.ScopeName == scopeName);

        await webserverAgent.HandleRequestAsync(HttpContext).ConfigureAwait(false);
    }
}

Client:
[HttpGet]
public async Task GetSync()
{
var clientConnectionString = "connection string";

        var clientProvider = new SqliteSyncProvider(clientConnectionString);

        var serverOrchestrator = new WebRemoteOrchestrator
        ("route to sync handler");

        var agent = new SyncAgent(clientProvider, serverOrchestrator);

        var s1 = await agent.SynchronizeAsync("Schema2");
        return Ok(s1);

    }

[gabitavanatorul]

here you go
dotmim_dump.zip

[gabitavanatorul]

that is weird because for me it was enough data to reproduce the issue. Here is another db_dump that you can try. One more thing, make sure that in appsettings you have this:

  "Tables": {
    "Schema2": "table1"
	}

database.zip

[gabitavanatorul]

hi, any update here?

[Mimetis]

I used your dump and it's working fine.

Server side:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();
builder.Services.AddDistributedMemoryCache();
builder.Services.AddSession(options => options.IdleTimeout = TimeSpan.FromMinutes(30));
var connectionString = builder.Configuration.GetSection("ConnectionStrings")["Postgres"];
var setup = new SyncSetup("Schema2.table1");
builder.Services.AddSyncServer<NpgsqlSyncProvider>(connectionString, setup);
var app = builder.Build();
app.UseHttpsRedirection();
app.UseAuthorization();
app.UseSession();
app.MapControllers();
app.Run();

Controller:

public class SyncController : ControllerBase
{
    private WebServerAgent webServerAgent;
    private readonly IWebHostEnvironment env;

    // Injected thanks to Dependency Injection
    public SyncController(WebServerAgent webServerAgent, IWebHostEnvironment env)
    {
        this.webServerAgent = webServerAgent;
        this.env = env;
    }

    /// <summary>
    /// This POST handler is mandatory to handle all the sync process
    /// </summary>
    /// <returns></returns>
    [HttpPost]
    public Task Post()
        => webServerAgent.HandleRequestAsync(this.HttpContext);
}

Client Side:

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

// Second provider is using plain old Sql Server provider, relying on triggers and tracking tables to create the sync environment
//var clientProvider = new SqlSyncProvider(clientConnectionString);
var clientProvider = new SqliteSyncProvider("adv.db");

var options = new SyncOptions
{
    BatchSize = 1000
};

// Creating an agent that will handle all the process
var agent = new SyncAgent(clientProvider, serverOrchestrator, options);

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(progress);
        // Write results
        Console.WriteLine(s1);

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

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

Console.WriteLine("End");

First Sync:

Resolving a conflict:

[VagueGit]

Using SQLite on the client and SQL Server via web api. I've struck this same issue using DMS 0.9.8. This issue does not arise in DMS 0.9.4.

The error first arose when our first customer installed the version of our app that uses DMS 0.9.8. The error first arose when a new install on a client device was syncing from the Orders table. 17,000 orders were synced before the exception was thrown. This suggests to me the error is not related to schema and the error message is incorrect.

After a reinstall on the client, all orders synced successfully, but then the exception was thrown syncing the Jobs table, after syncing 15,000 jobs.

The successful sync of the orders table suggests to me the issue is not related to data.

If it is not the schema and it is not data, then it is difficult to provide a repo.

[VagueGit]

This issue occurs initialising a new client from an existing server database that has previously synced other clients. The issue only arises on the first sync, though at what point seems indeterminate.

The table that is syncing when this exception is thrown will sync successfully on the second attempt. The issue has not been observed on subsequent syncs.

[gabitavanatorul]

I found out what caused my issue, but so far I have not been able to solve it. So basically, on the server side (PostgreSQL) I have a table that has a foreign key to itself and so when the SyncronizeAsync() encounters this table, it tries to add a row that has a foreign key to another row that doesn't exist (hasn't been added on the client yet). Is there a way to prevent this?

[gabitavanatorul]

I found out what caused my issue, but so far I have not been able to solve it. So basically, on the server side (PostgreSQL) I have a table that has a foreign key to itself and so when the SyncronizeAsync() encounters this table, it tries to add a row that has a foreign key to another row that doesn't exist (hasn't been added on the client yet). Is there a way to prevent this?

Ttry set DisableConstraintsOnApplyChanges to true in SyncOptions. see this.

issue is still happening

[Mimetis]

DisableConstraintsOnApplyChanges does not work with PostgreSQL when we are facing a foreign key constraint failure on the same table.

What you can try is to set the Error behavior to retry one more time:

options.ErrorResolutionPolicy = ErrorResolution.RetryOneMoreTimeAndThrowOnError 
// or RetryOneMoreTimeAndContinueOnError

That options will let DMS retries one more time a failed applied row.
Since the foreign key should be inserted after the error, the retry one more time should work

[VagueGit]

I'm afraid this doesn't resolve the issue for us. Using DMS 0.9.9-beta-1473 we are still getting the same exception with syncs with multiple tables >10,0000 rows. Repeating the sync resolves the issue.

There must be something different between ErrorResolutionPolicy = ErrorResolution.RetryOneMoreTime and syncing again.

[Mimetis]

If you are able to reproduce the error, I guess I would be able to work on it, otherwise, it will be complicated

[sojan1]

i get the same error with AdventureWorks database(SQL Server ) and SQLite on Maui(Windows).

i have not made any changes to the codes but have only changed the api link MauiAppClient.
MauiWebServer is same code with only changing its ConnectionString in appsettings.json and runs from local IIS for the client app to use it.

[Dotmim.Sync.SyncException: '[InternalApplyChangesAsync]..[InternalApplyChangesAsync]..[InternalApplyTableChangesAsync]..Specified argument was out of the range of valid values. (Parameter 'Can't make a query on primary keys since number of primary keys columns in criteria is not matching the number of primary keys columns in this table')']

[VagueGit]

As I mentioned earlier, we did not observe this issue in DMS 0.9.4. We first observed it in DMS 0.9.8. We have not observed it in DMS 1.0.0-beta-1492

So it might be fixed.

[sojan1]

We have downloaded the latest version available on git 2 or 3 days ago.

[VagueGit]

@sojan1 the latest release version of DMS or the latest beta? The latest release is 0.9.8. It's not fixed in 0.9.8. It does appear to be fixed in the latest beta DMS 1.0.0-beta-1492

[Mimetis]

Yes, could be a good idea to test with last beta version 1.0.0
Be sure you have the same version on server and client when you are making the tests

[VagueGit]

We are seeing this again using DMS 1.0.0-beta-1515. We probably didn't observe this issue with DMS 1.0.0-beta-1492 as we only had a limited number of users on that version of DMS.

[Mimetis]

Can you reproduce it ?

[VagueGit]

Haven't been able to reproduce it on demand. It appears 'randomly' in the databases we host for our customers. Usually with syncs with multiple tables >10,0000 rows. Repeating the sync resolves the issue.

Ours is a UWP app. I wonder if that is relevant given @sojan1 is seeing this in a MAUI app? I imagine UWP and MAUI share some weirdness.

[dnovaprove]

I'm able to reproduce it on demand.
The problem occurs when I try to delete and re-insert the same record, having different id, but with the same unique index value. I noticed that the sync process applies both changes in the following order: first insert and then delete. However, the unique index violation exception is thrown in the first step. Instead, if I delete the unique index from the table, the synchronization is successful.

Follow the sync log without unique index:
06/09/2023 12:51:18 Client version: 0.9.8
06/09/2023 12:51:18 Compression enabled
06/09/2023 12:51:19 Filters enabled
06/09/2023 12:51:25 0.000%: Session Begins. Id:d8210b14-52bc-4a46-bb99-b8f40a966c0f. Scope name:DefaultScope.
06/09/2023 12:51:26 0.000%: Session Begins. Id:d8210b14-52bc-4a46-bb99-b8f40a966c0f. Scope name:DefaultScope.
06/09/2023 12:51:26 65.000%: Downloaded Batch Changes. (1/2). Rows:(1/2).
06/09/2023 12:51:26 75.000%: Downloaded Batch Changes. (2/2). Rows:(1/2).
06/09/2023 12:51:26 75.000%: Downloaded batches count: 2. Total Rows: 2.
06/09/2023 12:51:26 75.000%: [Articoli] [Modified] Batch Articoli_0001_UPSERTS_mpfpvqdr_vzn.json (2/2) Applied.
06/09/2023 12:51:26 87.500%: [Articoli] Changes Modified Applied:1. Resolved Conflicts:0.
06/09/2023 12:51:26 87.500%: [Articoli] [Deleted] Batch Articoli_0000_DELETED_bsxrtp2u_jql.json (1/2) Applied.
06/09/2023 12:51:26 100.000%: [Articoli] Changes Deleted Applied:1. Resolved Conflicts:0.
06/09/2023 12:51:26 100.000%: [Total] Applied:2. Conflicts:0.
06/09/2023 12:51:26 100.000%: Session Ends. Id:d8210b14-52bc-4a46-bb99-b8f40a966c0f. Scope name:DefaultScope.
06/09/2023 12:51:26 100.000%: Session Ends. Id:d8210b14-52bc-4a46-bb99-b8f40a966c0f. Scope name:DefaultScope.
06/09/2023 12:51:27 Synchronization done.

I wonder why the delete operation is not done before the insert, as it would be safer.

[Mimetis]

More info here : #453

@dnovaprove : With this configuration, do you have this specific error ? (Parameter 'Can't make a query on primary keys since number of primary keys columns in criteria is not matching the number of primary keys columns in this table')

If yes, can you share a simple table with some rows that fails and I can reuse to test ?

[dnovaprove]

Yes, the error is the same. Here is the detailed exception:
Type: Dotmim.Sync.SyncException. Number: 0. HResult: -2146233088. SyncStage: ChangesApplying. Message: [InternalApplyChangesAsync]..[InternalApplyChangesAsync]..[InternalApplyTableChangesAsync]..Specified argument was out of the range of valid values.
Parameter name: Can't make a query on primary keys since number of primary keys columns in criteria is not matching the number of primary keys columns in this table;

As I previously specified, the problem happens on any table that has a unique index. If I remove and reinsert the same record and then resync, the exception is thrown.

A script of the table on which the exception is thrown in my case follows:

CREATE TABLE [dbo].[Articoli](
[Id] [uniqueidentifier] NOT NULL,
[CodArt] nvarchar NOT NULL,
[Descrizione] nvarchar NOT NULL,
[UM] nvarchar NULL
CONSTRAINT [PK_Articoli] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Articoli] ADD DEFAULT (newid()) FOR [Id]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_CodArt] ON [dbo].[Articoli]
(
[CodArt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

[VagueGit]

This correlates with our experience. The tables that throw this exception do have one or more unique indexes apart from the primary key.

[Mimetis]

I agree on that. It's completely logical that you can't insert a row with a unique index if you have already a row with this unique id.
And the fact that deleting this row is happening after the insert is triggering the error.

However, I'm able to reproduce the error, however I don't have the same error raised by the provider:

If using SqlSyncProvider, error raised is:

'Cannot insert duplicate key row in object 'dbo.ProductCategory' with unique index 'IX_CodArt'. The duplicate key value is (TT).
The statement has been terminated.

If i'm using SqliteSyncProvider, the error is:

SQLite Error 19: 'UNIQUE constraint failed: ProductCategory.Code

Completely different from your error.

Can you add more explanation on How To Reach The Same Error A You ?

[symbiogenesis]

I am seeing this now in my logs, but my retry policy or whatever is making these errors silent with respect to the user. Just clutters up the logs.

[symbiogenesis]

This is a client-side error.

Microsoft.Data.Sqlite provides a concept of an ExecutionStrategy that can allow retry logic.

What is the best way to integrate that with DMS?

[symbiogenesis]

@VagueGit is right about older versions being unaffected. The newest version that doesn't have this issue is 0.9.6

Which means it was introduced in 0.9.7, and thus sometime between September 8 - September 19 last year. A fairly small 11 day window.

[Mimetis]

I've made more tests on Unique Indexes.

One way to prevent DELETE / INSERT the same unique index (and then get the error) is to disable the unique index when databases changes are going to happen (using OnDatabaseChangesApplying) and then enable it again once everything is done (using OnDatabaseChangesApplied)

Here is an example I've created:

I'm using this script for creating a simple ProductCategory table with a unique index on the Code column:

USE [master]
GO 

-- Server database
if (exists (select * from sys.databases where name = 'AdventureWorks'))
Begin
	ALTER DATABASE [AdventureWorks] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [AdventureWorks]
End
Create database [AdventureWorks]
Go
-- Client database. No need to create the schema, Dotmim.Sync will do
if (exists (select * from sys.databases where name = 'Client'))
Begin
	ALTER DATABASE [Client] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [Client]
End
Create database [Client]
Go

Use AdventureWorks;
CREATE TABLE [dbo].[ProductCategory](
	[ProductCategoryID] [uniqueidentifier] NOT NULL PRIMARY KEY CLUSTERED DEFAULT(NEWID()),
	[ParentProductCategoryID] [uniqueidentifier] NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NULL,
	[ModifiedDate] [datetime] NULL,
	[IsActive] [bit] NOT NULL DEFAULT(1))

CREATE UNIQUE NONCLUSTERED INDEX [IX_CodArt] ON [dbo].[ProductCategory] ([Code] ASC);

Use Client;

 CREATE TABLE [dbo].[ProductCategory](
	[ProductCategoryID] [uniqueidentifier] NOT NULL PRIMARY KEY CLUSTERED DEFAULT(NEWID()),
	[ParentProductCategoryID] [uniqueidentifier] NULL,
	[Code] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NULL,
	[ModifiedDate] [datetime] NULL,
	[IsActive] [bit] NOT NULL DEFAULT(1))

CREATE UNIQUE NONCLUSTERED INDEX [IX_CodArt] ON [dbo].[ProductCategory] ([Code] ASC);

USe AdventureWorks;

INSERT [dbo].[ProductCategory] ([ProductCategoryID], [ParentProductCategoryID], 
[Code], [Name], [rowguid], [ModifiedDate], [IsActive]) 
VALUES ('cfbda25c-df71-47a7-b81b-64ee161aa37c', NULL, 
N'BKS', N'Bikes', newid(), CAST(N'2002-06-01T00:00:00.000' AS DateTime), 1)

Then here is the code I'm using to reproduce the error (and solve it with the two interceptors):

var serverProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(serverDbName));
var clientProvider = new SqlSyncProvider(DBHelper.GetDatabaseConnectionString(clientDbName));
var setup = new SyncSetup("ProductCategory");
var options = new SyncOptions { DisableConstraintsOnApplyChanges = true };

SyncAgent agent = new SyncAgent(clientProvider, serverProvider, options);

agent.LocalOrchestrator.OnDatabaseChangesApplying(args =>
{
    var cmdText = $"ALTER INDEX IX_CodArt ON ProductCategory DISABLE;";
    var cmd = args.Connection.CreateCommand();
    cmd.CommandText = cmdText;
    cmd.Transaction = args.Transaction;
    cmd.ExecuteNonQuery();
});
agent.LocalOrchestrator.OnDatabaseChangesApplied(args =>
{
    var cmdText = $"ALTER INDEX IX_CodArt ON ProductCategory REBUILD;";
    var cmd = args.Connection.CreateCommand();
    cmd.CommandText = cmdText;
    cmd.Transaction = args.Transaction;
    cmd.ExecuteNonQuery();

});

// first sync to initialize everything
var result = await agent.SynchronizeAsync(setup);

// create a new product category
var name = Path.GetRandomFileName().Replace(".", "").ToLowerInvariant();
var code = name.ToUpperInvariant();

// Add this new product to server and then sync it to client
await AddProductCategoryRowWithUniqueIndexAsync(serverProvider, name, code);

// the new product is sent to the client side
result = await agent.SynchronizeAsync(setup);

// Reproducing the unique index failure here
// -----------------------------------------
// delete the product on the server
await DeleteProductCategoryRowAsync(serverProvider, name: name);

// then add it again with the same unique index
await AddProductCategoryRowWithUniqueIndexAsync(serverProvider, name, code);

// Here we have to sent 2 modifications to the client
// - First one is the new row order, but the unique row is already existing on the client,
//   causing the failure.
// - Then the delete order.

// HERE the error is raised if we don't use the interceptors.
result = await agent.SynchronizeAsync(setup);
Console.WriteLine(result);