mobilink event upload_update has unexpected results

[former_SQLA_member1694913]

We use time stamp based synchronization and a table that is in both a consolidated and remote db. This table synchronizes updates from the consolidated to the remote. We don't want the remote updating this table so we have triggers that prevent this on the remote. Then, on the consolidated we have an upload_update event script that looks like this:

update owner.table set last_modified_ts = now(*) where table-pk= {ml r.table-pk}

So, if someone at the remote is determined to update that table the consolidated will not update but then will send the consolidated value back to the remote in the download_cursor based on the last_modified_ts being updated.

This has been working. But in testing yesterday I saw this:

I updated a value at the remote. The column I updated had a data type of varchar(7). Consolidated and remote match with this data type.

I updated the last character in the column like this at the remote: original value: testing updated value: testina

Then synchronized. The consolidated was not updated as expected. But the consolidated did not send its value back to the remote. I saw this in the mobilink server logfile:

I. 2019-03-28 09:33:38. <4> upload_update table
I. 2019-03-28 09:33:38. <4>  UPDATE owner.table SET Last_Modified_ts = now() WHERE VALUE = {ml r.VALUE};
I. 2019-03-28 09:33:38. <4> Translated SQL:
I. 2019-03-28 09:33:38. <4>  UPDATE owner.table SET Last_Modified_ts = now() WHERE VALUE =  ?;
I. 2019-03-28 09:33:38. <4> Update row (new remote values) [table]:
I. 2019-03-28 09:33:38. <4> 

I. 2019-03-28 09:33:38. <4>   testina
I. 2019-03-28 09:33:39. <4> download_cursor table
I. 2019-03-28 09:33:39. <4>  SELECT VALUE, DATA1
I. 2019-03-28 09:33:39. <4>                   FROM owner.table t
I. 2019-03-28 09:33:39. <4>                   WHERE t.last_modified_ts >= {ml s.last_table_download}
I. 2019-03-28 09:33:39. <4> Translated SQL:
I. 2019-03-28 09:33:39. <4>  SELECT VALUE, DATA1
I. 2019-03-28 09:33:39. <4>                   FROM owner.table t
I. 2019-03-28 09:33:39. <4>                   WHERE t.last_modified_ts >=  ?
I. 2019-03-28 09:33:39. <4> System variable last_table_download value: 2019-03-28 09:32:46.802000
I. 2019-03-28 09:33:39. <4> Insert/Update row [table]:
I. 2019-03-28 09:33:39. <4> 

I. 2019-03-28 09:33:39. <4>   testing
I. 2019-03-28 09:33:39. <4> Ignoring redundant row 

Does "Ignoring redundant row" mean it's not sending the consolidated value back to the remote? Which is what I'm assuming.

I did some further testing and the redundant row only seems to come up when the character length of the value isn't updated but a character in the value is updated. If I update the value to "test" changing the length, then the consolidated sends the update to the remote. But if I update the value to "testina", still 7 characters, it will not send the update.

What constitutes an "update" for the download_cursor to send an update to the remote? From what I understand, the upload is compared to the download and if they match then you get the redundant row information line. Is this correct?

Appreciate the help.

[Breck_Carter]

What version and build are you using?

If the posted text has been edited, please show us the original... even the slightest error in editing has been known to obscure the cause of problems.

[Breck_Carter]

It is also sad that this Google search

"ignoring redundant row" "Mobilink"

only yields 3 hits, and two of those appear to have been deleted by The Great SAP Doc Purge.

The third hit is an ancient Palm Ultralite how-to which shows the message but doesn't explain it:

I. 10/21 21:36:23. [ULLDemo1]: download_cursor ULLDemo1 (open for read):
SELECT id, hello FROM ULLDemo1
I. 10/21 21:36:23. [ULLDemo1]: Translated SQL:
SELECT id, hello FROM ULLDemo1
I. 10/21 21:36:23. [ULLDemo1]: Insert/Update row:
I. 10/21 21:36:23. [ULLDemo1]:   1
I. 10/21 21:36:23. [ULLDemo1]:   hello world
I. 10/21 21:36:23. [ULLDemo1]: Ignoring redundant row
I. 10/21 21:36:23. [ULLDemo1]: download_cursor ULLDemo1 (close)

If memory serves, the message is produced by the feature described in How the upload is processed: "It would be difficult to write a download_cursor script that omits from the download rows that were sent as part of the upload. For this reason, the MobiLink server automatically removes these rows from the download."

...apparently, "difficult to write" is an understatement, even for the engineers.

Perhaps your trigger and other logic somehow magically interferes with the method whereby "the MobiLink server automatically removes these rows"... perhaps some adjustment to the way your code works can bypass the problem and restore correct behavior.

...it won't be the first time code had to be changed to avoid a bug.

[VolkerBarth]

Another idea would be to handle that as some kind of update conflict, which it is basically, when the cons does not accept the remote's change. And conflicting rows are downloaded back to the remote they came from...

[former_SQLA_member1694913 · Accepted solution]

Thank you Breck and Volker. What we had is a table with a funky primary key ( a blank ' ') and a reserved word used as the value in the varchar(7) column ('unknown'). We decided to change the 'unknown' to 'UNK' and that did give the expected behavior.