Sync Local and Remote Database

[vitorserrao]

Hi, i'm having trouble when trying to Sync a local and remote database.
Normally the remote database is used while online, with the local used while offline.
I have a code that uses the remote as server, local as client, only download with client wins, so the client can work as a backup in case the internet goes down.
The problem is, if someone else updates the online database with new row while i update the offline with new roles, when the internet comes back and the sync happens, not all updates from the online database comes, only the ones made after the last offline update.
How can i fix this issue?
Thanks for the attention

[Mimetis]

i dont understand
Need a repro sample to reproduce and understand

[vitorserrao]

public async void SyncData()
{
            try
            {
                var serverProvider = new NpgsqlSyncProvider("remote");
                var clientProvider = new NpgsqlSyncProvider("local");

                var options = new SyncOptions { ConflictResolutionPolicy = ConflictResolutionPolicy.ClientWins };

                var setup = new SyncSetup("dbo.audiogram", "dbo.frequency", "dbo.patients", "dbo.session", "dbo.users");
                setup.Tables["dbo.audiogram"].SyncDirection = SyncDirection.DownloadOnly;
                setup.Tables["dbo.frequency"].SyncDirection = SyncDirection.DownloadOnly;
                setup.Tables["dbo.patients"].SyncDirection = SyncDirection.DownloadOnly;
                setup.Tables["dbo.session"].SyncDirection = SyncDirection.DownloadOnly;
                setup.Tables["dbo.users"].SyncDirection = SyncDirection.DownloadOnly;

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

                var result = await agent.SynchronizeAsync(setup);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
}

This is the configuration code. The client server is a local server that you access while offline. The server is a remote server that you access while online.
More than one person have access to the online server, so multiple people can alter the online server, but each person only has access to its own local server.
If someone's internet falls, he will start updating the offline server, and when he goes back online the data will be sync between the online and offline server.
I want the offline server to be fully in sync with the online database without any loss of data. So i made download only with client wins. But the client doesnt get all the online data, only online data that is newer that the last offline data.
Like the image bellow shows.

[vitorserrao]

1st Image - After sync, Kelly Swift was downloaded from remote to local, but not the rest.
2nd Image - The local server before sync.
3rd Image - The remote server.

[matevzcesar]

Something I noticed is that you have the conflict resolution policy set to "ClientWins", but all your tables are "DownloadOnly". So there never will be any conflicts, since no data from the local database will be uploaded.

As for the differences in data, did you insert new data or edit the details of existing records? If you inserted new ones, what happened to the old ones? Were they deleted?

[vitorserrao]

I used "ClientWins" with download only because on my first try i went with the standard settings (both ways with "ServerWins") and the local DB would lose data.
If the internet went down for 2 people and they added both added new data, if they went back online:

• The one that came back online first would update its data to the online database
• The online database would be in a newer version than the second offline database
• It will win over the second offline database
• The second database will have some of its data deleted, the data that is older than the last update inside the online database

So i thought since most of the time there will be no internet loss, the online database can just send updates to the offline database to keep it updated. The issue comes when someone goes offline and there is someone updating the online server, because if the offline data update was done after the online, since the "Client Wins" the offline server will not be updated and will lose this data as shown on the pictures above. (By lost data here i don't mean it was deleted but it was lost because the offline database did not downloaded that data.)

The project is like a Hospital database, it needs to work both online and offline, and both databases should be synced without loss of data. Ex: a patient that was added in Hospital1 offline, the Hospital2 online needs to get that information when the Hospital1 becomes online again. The patient can't be deleted. And vice versa. A change that the online Hospital2 did, the offline database from Hospital1 needs to receive when it goes online.

Sorry if it feels confusing.

[Mimetis]

I'm sorry, can't help you here.
If you can create a sample, and host it in a github repository, with a clear guidance to reproduce your bug, I will be able to help you otherwise it's nearly impossible to guess here.