Deletes on Client DB after an Initial sync

[ajscilingo]

We’re seeing what we think is kind of strange behavior after the initial sync. Our environment is as such, we are running two Postgres databases, one of them is the “server” and the other is the “client”. We have two instances of Dotmim.Sync running, one on the same machine as the server database and the other on the same machine as the client database – at the moment we’re not using the WebRemoteOrchestrator but rather RemoteOrchestrator and LocalOrchestrator. Both copies of our code running DotMim.Sync have the “RemoteOrchestrator” pointing at what we’re calling the Server Database and the “LocalOrchestrator” pointing at what we’re calling the Client Database. The initial sync is being initiated from the DotMim.Sync instance that is running on the same machine as the Server Database. After an initial sync, which provisions both databases, we see that the “Client” database has tracking tables with data in it, but the “Server” database has empty tracking tables. Is this the expected behavior?

If this is the expected behavior, then what we’re seeing is after the initial sync, any data that is deleted from the client database but has been present on the server database during the initial sync does not get deleted after the client database has deleted it and another sync is performed. Instead we see a RemoteIsDeletedLocalNotExist conflict raised by the RemoteOrchestrator (pointing at the Server Database), showing that the remote row (Client Database) is in the client database tracking table marked as deleted, but there is no corresponding Local row (Server Database) in the server database tracking table. We see this conflict raised regardless of whether we execute the subsequent sync (the sync after the initial sync) on the copy of code using DotMim.Sync running on the same machine as the server database or the same machine as the client database.

As a work-around, we’ve found that copying the client database’s tracking table rows where sync_row_is_tombstone = 1 and then adding them to the corresponding server database’s tracking table and setting sync_row_is_tombstone = 0 allows for the deletes to be synced over after executing the subsequent sync.

[Mimetis]

Thanks for your detailed explanation.
If I understand well, you have:

• A Server database with some rows
• An empty Client database
• Running DMS from your client database will create everything on Server and Client and will download all the server data to client
• Then you are trying to delete a row on the client, that is not delete on the server side

Is it correct ?

[ajscilingo]

A Server database with some rows - YES
An empty Client database - YES
Running DMS from your client database will create everything on Server and Client and will download all the server data to client - NO

We're running DMS on the Server machine first to seed the client Database with the rows from the Server Database, then on the Client Side we delete a row and then we initiate another sync from DMS running on the Client machine.

Then you are trying to delete a row on the client, that is not delete on the server side - We're trying to delete a row on the client that IS on the server side, however it's not in the tracking table on the server side.

[Mimetis]

I'm a little bit confused.

Running on the server machine or on the client machine should not change anything.
The code is exactly the same, right ?
I mean, the server database is always the RemoteOrchestrator (and client is always LocalOrchestrator), wherever you are executing your code, right ?

If you can create a little sample reproduction code I can use to reproduce your error, it will be easier for me to understand and help you

[ajscilingo]

that's correct, we see the same result regardless of where the code is ran, RemoteOrchestrator is always pointed to the server db and local orchestrator is always pointed to the client db. I'm just noting this in case it's of any significance.

[Mimetis]

Thanks, I found the error.
It's unusual to delete a row just after a first sync.
But it's working with the SQL Server provider, and it's a bug in the PostgreSQL sync provider.
I've made a fix, and the corresponding test.
I need to ensure everything is fixed, and I did not introduce any edge bug by doing a small change in the generated SQL.
Once all tests are green, I'll publish a new beta.

Thanks for finding a bug !!

(I'm transferring this conversation to the Issues)

[Mimetis]

See #1080

Conversation locked