cancel
Showing results for 
Search instead for 
Did you mean: 

MobiLink timestamp-based download resends uploaded changes?

Former Member
0 Kudos

I've implemented a standard timestamp-based download in a MobiLink setting with SQL Anywhere 16 as consolidated and remote.

  • The table has a timestamp column called last_modified_ts
  • There is a shadow table with the primary key columns, and a deleted timestamp column (also called last_modified_ts)
  • There is a delete trigger that inserts into the shadow table
  • There is an insert trigger that deletes from the shadow table
  • The download_delete_cursor looks like this:

          SELECT t.PkCol1, t.PkCol2

          FROM ShadowTable t

          WHERE t.Last_Modified_Ts >= {ml s.last_table_download}

What I've observed is that when the remote deletes a row, this sequence occurs:

  • Upload delete removes the row on consolidated
  • Delete trigger fires, inserting a shadow table row using current timestamp
  • Since current timestamp is > last_table_download, download_delete_cursor returns the row
  • Remote attempts to delete the row, finds it isn't there

This happens with all uploaded actions. It's true this does no harm (delete has no row to delete, insert and update are both processed as updates, but with identical data to the row on the remote). But it is wasteful of bandwidth and synchronization time, especially when the remote makes a lot of changes.

Is there any method that prevents these redundant actions?

-Eric Murchie-Beyma

Accepted Solutions (1)

Accepted Solutions (1)

former_member194571
Active Participant
0 Kudos

Hi Eric,

I'm not aware of any option or switch to control this. If you want to eliminate these harmless (except for bandwidth) downloads, I suggest the following approach:

- set up a session table (global temporary or local temporary) with "on commit preserve rows" or "not transactional" matching the shadow table for PKs of deleted rows.

- delete all rows from the session table in the begin_upload event (session or table) - just to be sure.

- record all uploaded deletes from your session in the session table, in the trigger or in the event handler.

- filter all rows in the session table from the download_delete_cursor (EXCEPT / WHERE ... NOT IN (SELECT ...))

- delete all rows from the session table in the end_download event (session or table).

HTH

Volker

Answers (2)

Answers (2)

regdomaratzki
Advisor
Advisor
0 Kudos

The MobiLink Server does not filter rows from the download_delete_cursor, only the download_cursor.

If you want to filter rows from the download_delete_cursor, Volker's suggestion is a good one.

Reg

former_member194571
Active Participant
0 Kudos

Knowing Breck's statement, this was my guess. Thank you for clarifying.

Former Member
0 Kudos

Yeah, further testing confirmed that it's only the deletes. Sorry for the confusion.

former_member188493
Contributor
0 Kudos

Egad! Are you saying my book is wrong? : )

"The download stream is automatically filtered to remove any rows that were just uploaded. This makes it easier to write the download scripts because you don’t have to worry about excluding those rows to reduce unnecessary network traffic — it’s done for you." - Section 7.2 How MobiLink Works

Seriously, the method you are using may be fooling MobiLink. You may need to add code to your trigger(s) to prevent them from doing [whatever it is they are doing] when they are fired by the MobiLink server; checking the user id is one method.

https://cdn.meme.am/instances/500x/37685995.jpg

Former Member
0 Kudos

Yes, my mistake. My inserts and updates do get filtered out on the download, with an "Ignoring redundant row" message. It's only the deletes that get resent.

Former Member
0 Kudos

Funny thing is, I DID check your book for exactly this statement, but I started at section 7.6.

former_member188493
Contributor
0 Kudos

Ha, ha, that's where I started looking too, that's where it SHOULD have been : )