SqliteSyncProvider always reports a conflict when applying changes where no tracking row exists

[kmgallahan]

As per this discussion: #1372, it was discovered that the SqliteSyncProvider will always report conflicts when:

• A row exists in a client SQLite DB
• The row is updated elsewhere and synced to the client
• There is no _tracking entry for the row

With much digging and AI assistance the faulty code was identified:

Dotmim.Sync/Projects/Dotmim.Sync.Sqlite/Builders/SQLiteObjectNames.cs

Lines 358 to 361 in f4378ba

	stringBuilder.AppendLine($"WHERE ([side].[timestamp] < @sync_min_timestamp OR [side].[update_scope_id] = @sync_scope_id) ");
	stringBuilder.Append($"OR ({SqliteManagementUtils.WhereColumnIsNull(this.TableDescription.PrimaryKeys, "[base]")} ");
	stringBuilder.AppendLine($"AND ([side].[timestamp] < @sync_min_timestamp OR [side].[timestamp] IS NULL)) ");
	stringBuilder.Append($"OR @sync_force_write = 1");

This creates a WHERE clause:

LEFT JOIN [Row_tracking] AS [side] ON [side].[Id] = @Id
LEFT JOIN [Row] AS [base] ON [base].[Id] = @Id
WHERE ([side].[timestamp] <= @sync_min_timestamp OR [side].[update_scope_id] = @sync_scope_id) 
OR ([base].[Id] IS NULL  AND ([side].[timestamp] <= @sync_min_timestamp OR [side].[timestamp] IS NULL)) 

Which has the following issue:

Scenario A: The Row DOES NOT exist (New Record)
If [base].[Id] is NULL (it's a new insert):

[base].[Id] IS NULL is TRUE.
Inside the inner parentheses:
[side].[timestamp] <= ... is FALSE.
[side].[timestamp] IS NULL is TRUE (Because there is no tracking row).
The logic becomes: TRUE AND (FALSE OR TRUE) -> TRUE.
Outcome: The row IS selected. The sync proceeds to insert the new row.

Scenario B: The Row ALREADY exists
If [base].[Id] is NOT NULL (the row is there):

[base].[Id] IS NULL is FALSE.
The AND condition immediately fails.
Outcome: The row IS NOT selected. The sync skips this update (unless the @sync_force_write flag from the wider query was set to 1).

This is resolved by modifying the SQL builder to handle cases correctly:

// 1. Standard Update: Only allow if row exists (NOT NULL) AND Tracking conditions are met
// We verify [base] is NOT NULL to ensure we don't implicitly resurrect a tombstoned row here.
stringBuilder.Append($"WHERE (NOT ({SqliteManagementUtils.WhereColumnIsNull(this.TableDescription.PrimaryKeys, "[base]")}) ");
stringBuilder.AppendLine($"AND ([side].[timestamp] <= @sync_min_timestamp OR [side].[update_scope_id] = @sync_scope_id)) ");

// 2. Missing Tracking: Allow write if Tracking row is missing (New Inserts or Data Update)
stringBuilder.AppendLine($"OR ({SqliteManagementUtils.WhereColumnIsNull(this.TableDescription.PrimaryKeys, "[side]")}) ");

// 3. Force Write
stringBuilder.Append($"OR @sync_force_write = 1");

[Mimetis]

The problem is not the WHERE clause, but more

"Why do we have a row without change tracking row associated"

On first sync, client download all rows from the server side, and all rows go through the INSERT trigger that creates a row in the tracking changes table

In your sample, and I still don't know why, we have a situation where the row is NOT created in the tracking table, which eventually generates a conflict

I'm investigating (but don't have too much time) this specific issue

[Mimetis]

That's weird, most of the time, I don't have the issue
In fact, I can't reproduce it any more....

Did you tried with the last main branch ?

[kmgallahan]

As mentioned in the repro steps, you need to wait for the _tracking table entry to get deleted in client B before the conflict occurs.

I just assumed the tracking table entries were deleted as a part of planned cleanup since they always get deleted after 2 syncs.

According to AI:

The SQLite provider handles tracking table cleanup through the following mechanisms:

When

The cleanup process occurs when the DeleteMetadatasAsync method is called. This can happen in two ways:

1. Manually: You can explicitly call localOrchestrator.DeleteMetadatasAsync() in your code.
2. Automatically: The LocalOrchestrator logic often determines a safe timestamp (based on the oldest client synchronization) and triggers this cleanup to prevent the tracking tables from growing indefinitely.

Where

The SQL command generation logic resides in the SqliteObjectNames class, specifically in the CreateDeleteMetadataCommandText method.

• File Path: Projects/Dotmim.Sync.Sqlite/Builders/SQLiteObjectNames.cs

How

The provider executes a straightforward SQL DELETE statement against the specific tracking table. It relies on a timestamp comparison to ensure it only removes metadata that is no longer needed for synchronization (i.e., metadata older than the oldest active client's last sync).

The generated SQL command follows this pattern:

DELETE FROM [YourTable_tracking] WHERE [timestamp] <= @sync_row_timestamp;

This ensures that any metadata row created or modified before the cutoff timestamp (@sync_row_timestamp) is permanently removed from the tracking table.

[Mimetis]

Indeed, when metadatas are deleted, we eventually have no rows in SQLite tracking table, good catch !!

And this is where the bug occurs

Once the update from the server is coming down to SQLite, here is the sqlite commande executed (I replace the parameters with real value for simplicity)

WITH CHANGESET as (SELECT [c].[Id], [c].[FirstName], [c].[LastName] FROM (SELECT 1 as [Id], 'John' as [FirstName], 'Doe12' as [LastName]) as [c]
LEFT JOIN [Customer_tracking] AS [side] ON [side].[Id] = @Id
LEFT JOIN [Customer] AS [base] ON [base].[Id] = @Id
WHERE ([side].[timestamp] < @sync_min_timestamp OR [side].[update_scope_id] = @sync_scope_id) 
OR ([base].[Id] IS NULL  AND ([side].[timestamp] < @sync_min_timestamp OR [side].[timestamp] IS NULL)) 
OR @sync_force_write = 1)
INSERT INTO [Customer]
([Id]
, [FirstName]
, [LastName])
 SELECT * from CHANGESET WHERE TRUE
 ON CONFLICT ([Id]) DO UPDATE SET 
[FirstName]=excluded.[FirstName]
, [LastName]=excluded.[LastName];

UPDATE OR IGNORE [Customer_tracking] SET 
[update_scope_id] = '00000-0000-0000-0000',
[sync_row_is_tombstone] = 0,
[timestamp] = replace(strftime('%Y%m%d%H%M%f', 'now'), '.', ''),
[last_change_datetime] = datetime('now')
WHERE [Id] = 1
 AND (select changes()) > 0;

What happens here:

• the INSERT / ON CONFLICT UPDATE is executed
• INSERT trigger is inserting a row in tracking table
• the UPDATE OR IGNORE statement is executed to update the tracking table with correct values

It seems that this flow is not correctly executed, as we don't have any row in the tracking table (and this is why we have a conflict occuring)

[kmgallahan]

Edited:

It seems that this flow is not correctly executed, as we don't have any row in the tracking table (and this is why we have a conflict occuring)

No, the problem is that the where clause is not allowing the _tracking row to be null, so nothing gets inserted via INSERT INTO [Customer], so no tracking row gets created. Instead this whole query returns 0 as the number of rows updated, which is then interpreted as a conflict.

By modifying the WHERE clause as I've proposed the missing tracking rows are handled correctly - the data does get inserted, and so the query returns a value > 0, which means no conflicts are reported. If a _tracking row doesn't exist then we know the row is up-to-date on the client, so the server data just gets updated on the client without issue/conflicts.

[Mimetis]

Indeed, this request returns nothing

WITH CHANGESET as (
SELECT [c].[Id], [c].[FirstName], [c].[LastName] , side.*, base.*
  FROM (SELECT 1 as [Id], 'John' as [FirstName], 'Doe12' as [LastName]) as [c]
  LEFT JOIN [Customer_tracking] AS [side] ON [side].[Id] = @Id
  LEFT JOIN [Customer] AS [base] ON [base].[Id] = 1
  WHERE ([side].[timestamp] < 111111111 OR [side].[update_scope_id] = '00000-0000-0000-0000') 
  OR ([base].[Id] IS NULL  AND ([side].[timestamp] < 111111111 OR [side].[timestamp] IS NULL)))

SELECT * from CHANGESET WHERE TRUE

Progressing (but need to go)

[kmgallahan]

Please review my proposed changes to the WHERE clause. I have already built and tested the SQLite provider and am running my own version with the changes until the this bug is resolved in the main. As mentioned above:

Scenario A: The Row DOES NOT exist (New Record)
If [base].[Id] is NULL (it's a new insert):

[base].[Id] IS NULL is TRUE.
Inside the inner parentheses:
[side].[timestamp] <= ... is FALSE.
[side].[timestamp] IS NULL is TRUE (Because there is no tracking row).
The logic becomes: TRUE AND (FALSE OR TRUE) -> TRUE.
Outcome: The row IS selected. The sync proceeds to insert the new row.

Scenario B: The Row ALREADY exists
If [base].[Id] is NOT NULL (the row is there):

[base].[Id] IS NULL is FALSE.
The AND condition immediately fails.
Outcome: The row IS NOT selected. The sync skips this update (unless the @sync_force_write flag from the wider query was set to 1).

So simply changing the SQL generation code to this works as the comments note:

// 1. Standard Update: Only allow if row exists (NOT NULL) AND Tracking conditions are met
// We verify [base] is NOT NULL to ensure we don't implicitly resurrect a tombstoned row here.
stringBuilder.Append($"WHERE (NOT ({SqliteManagementUtils.WhereColumnIsNull(this.TableDescription.PrimaryKeys, "[base]")}) ");
stringBuilder.AppendLine($"AND ([side].[timestamp] <= @sync_min_timestamp OR [side].[update_scope_id] = @sync_scope_id)) ");

// 2. Missing Tracking: Allow write if Tracking row is missing (New Inserts or Data Update)
stringBuilder.AppendLine($"OR ({SqliteManagementUtils.WhereColumnIsNull(this.TableDescription.PrimaryKeys, "[side]")}) ");

// 3. Force Write
stringBuilder.Append($"OR @sync_force_write = 1");

[Mimetis]

I've found an easier syntax
Can you review my comment on your PR ?
And let me know what you think
I guess we can achieve the same thing with less joins (basically removing the JOIN on the inner table)