WatermelonDB: Sync error handling

@Skullcan I managed to fix it! Hopefully it stays that way 🤣

Here’s how I fixed it in case anyone else needs help with it:

• Timestamps being set to 0.0 I’m not sure why it only does it for some records and not others but my timestamps were being sent back as strings so I had to parse all my timestamps to integers in the knex setup. This comment on the knex forums is what ultimately helped me: https://github.com/knex/knex/issues/3071#issuecomment-509429039
• [Diagnostic error: [Sync] Server wants client to create record table_name#id1234567890123, but it already exists locally. This may suggest last sync partially executed, and then failed; or it could be a serious bug. Will update existing record instead.] Upon creation & update of the record on the server, I set the timestamps to the timestamps of the local database record instead of letting postgres automatically update the timestamps.
• ...Will delete local record and recreate it instead I had to separate the creation and update sync from the deletion sync because of the foreign key constraints. The order of the former and latter had to be reversed. I couldn’t use cascade deletions because I needed to add my deleted records to my deleted records table
• I later ran into an issue where the deletes were still not syncing but it was because of a type conversion issue

sendCreatedAsUpdated worked like a charm, thanks so much for all the help you’ve been a lifesaver!

I’m glad I could help!

sendCreatedAsUpdated worked like a charm, thanks so much for all the help you’ve been a lifesaver!

When I had this problem I solved using the SyncId method as I mentioned before, but then again, my project was expected to not be big and the extra overhead was not a problem.

Maybe you could try using the ‘sendCreatedAsUpdated’ flag and see if it helps. There’s a discussion about something similar to your problem here #649

@Skullcan Ok I’ll test that out next week and let you know if it works, thanks so much for all the help!

Your timestamp fields in postgres can be stored the way you are doing, but the watermelon sync function expect the Unix Timestamp in the changes.

You will have to convert the Human readable timestamp from postgre to Unix timestamp before returning they to the changes. So your "2021-12-23T15:16:10.388Z" have to be changed to 1640278863

Here is a snippet of the tail end of my changes object

[
  {
    "id": "15",
    "description": "xxxx",
    "value": 0.0,
    "created_at": 1640027440305,
    "updated_at": 1640027440305,
    "deleted_at": null
  }
],
  "updated": [],
  "deleted": []
  }
},
"timestamp": 1640279142732

This might be the reason you are having problems with the sync altogether tbh.

So your server timestamps are different from the WatermelonDB timestamps and it works fine? Also what do you mean by trust the server timestamp? I return Date.now() from my pullChanges endpoint on the server as the last. Is that what you mean by trusting the server timestamp?

No, they are not different. I meant I always trust the values in the control fields created_at, updated_at and deleted_at in my database. And you are right in your approach, you have to return the Date.now() in your sync backend as your last.

Because technically the problem with mine is for example, the client runs pullChanges and it grabs the record test1 for the table Test from the server and pushes nothing. Then I create another record called test2 on the client so sync is triggered again where the problem is essentially like this:

pullChanges: nothing pulled & lastPulledAt timestamp is updated pushChanges: pushes test2 & the timestamps on the server record are updated by Postgres pullChanges: Attempts to pull test2 because it was technically created after the last pullChanges & lastPulledAt timestamp is updated

Oh I see, I had this problem in another project… only managed to work around it by creating a sync_id… and in my pullChanges I only return the changes with Ids that are not the current syncId from the ones I sent from the same client. Something like this…

const created = map(changes[table].created, n=> {
  const newReg = omit(n, defaultExcluded);
  if (n.sync_id !== syncId) return newReg;
  return false;
});

This way, even if the created/updated at have a newer timestamp, the syncId prevent the attempt to create a record that was created by the same client. But then again, my need may be different than yours, so I don’t know if this approach works for you.

How do you check for this? I think this is probably what is causing Diagnostic error: [Sync] Concurrent synchronization is not allowed. More than one synchronize() call was running at the same time, and the later one was aborted before committing results to local database even though I check whether or not the promise is resolved

The triggered withChangeForTables from the remote server always have the ‘synced’ status. So I filter then and only call sync when I have at least 1 record that needs to be pushed. Debug it and check if this is your case too.

const watchForChanges = databse.withChangesForTables(['orders']).subscribe({
  next: d => {
      const changedRecords = filter(d, c => c.record.syncStatus !== 'synced');
      if (changedRecords.length) {
        // Sync();
      }
    },
    error: e => logErrors(e)
});

I see you did the basically the same thing in your .pipe() function… well… I tried doing this way… Didn’t work for me… so I did something like the code above. And now it’s working fine. The withChangesForTables only start a new sync if I change something locally.

I’m using the RxJS debounceTime but I think after reading your previous point I believe that it’s not a problem with the debounce but a similar problem as yours where the pullChanges is triggering the the sync again

It is probably the case, yes.

Please take into consideration that the codes are just a mockup and not optimized. I wouldn’t use them in production.