Ultralite online replication - upload/updates

[former_SQLA_member1694899]

Hi,

I have an application, written in UWP (C#), running on a Windows 10 (full version) tablet. I am using Ultralite 17 for the remote database and Mobilink for replication and Oracle for the consolidated. The tablets have a cellular connection and we use that to replicate data back to the consolidated database after each order on the tablet is completed.

Occasionally, we are missing data that is in the remote database but won't replicate to the consolidated database. One table, in particular. The software inserts a record into this table when the process starts and updates it 4 time during the process of 'working' the order. The initial insert 'transaction' of the record is sent to the consolidated, but the subsequent updates are not. I have no way of reproducing this because it happens randomly.

All the data is updated in the remote database, but those updates don't make it to the consolidated. There are no errors reported from replication on the tablet or from Mobilink.

All other data that is collected during the processing of the order is successfully sent to the consolidated database. The only difference with the problematic table is the record is updated after it has been inserted. All other tables just have records inserted. I'm using simple update SQL to perform the updates to the record.

Any idea on what could cause this or how to solve it?

Thanks,

Paula

[VolkerBarth]

I have no clue but some questions:

The initial insert 'transaction' of the record is sent to the consolidated, but the subsequent updates are not.

How do the INSERT and the following UPDATEs of the same order happen related to your synchronzisation - may a sync happen between these steps, or do you use a particular order of operations? And have you checked within the (verbose) ML server logs whether the "missing updates" have reached the server or not? And may isolation levels have an influence here?

[former_SQLA_member1694899]

Thanks for the questions. The sequence of events goes like this:

Order open

Record inserted

record updated

record updated

record updated

record updated

Order closed

Database replicated.

I would set the server to verbose logging but I have 30+ tablets in the field and feel like I'd run out of disk space (sarcasm). It would be like finding a needle in a haystack...if you know what I mean!

[regdomaratzki]

Are you saying that the updates don't arrive because you don't see updates in the MobiLink log on the table, or because the data values between the UL database and the Oracle consolidated don't match? The sequence of events that you list will result in UL sending a single insert when you synchronize, whose values would reflect the state of the row after all four updates. MobiLink is not replication, which will send each operation, it is synchronization, which will send the net change on the row between synchronizations.

Do any of the "record updates" modify the primary key of the row in question?

Reg

[regdomaratzki]

I disagree that it would be like finding a needle in a haystack. If at some point you determine that the row in table "A" with primary key "X" does not match between UL and Oracle, and you've been keeping verbose logs in the MobiLink server, you should be able to search through the log looking for that primary key value.

[former_SQLA_member1694899]

The data values between the UL database and the Oracle database do not match. All the values have been updated in the UL database but not Oracle.

I'd never update the primary key in the UL database and I'm pretty sure I'd get an error if I tried.

I'm realizing that this does not work like I thought it did. I thought it would send an insert transaction and then an update for subsequent updates. Thanks for the clarification.

[former_SQLA_member1694899]

Thanks for the though provoking questions/suggestions. The upload update script is currently ml-ignore which highlights my previous statement about not really knowing how the replication works under the hood.

At least I don't have where clause like you described.

[regdomaratzki]

Grr. Accidentally deleted my comment when I tried to upgrade it to an answer.

[regdomaratzki]

If the upload_update script for a given table is --{ml_ignore}, you're ignoring updates on this table. Given this works "most of the time", I'd suggest that things only fall apart when your sequence of events is :

insert
update #1
synch
update #2
update #3
synch

With the uplaod_update script doing nothing, update #2 and update #3 will synchronize (i.e. UltraLite will send the changes), but you've told the MobiLink Server to ignore the updates, so the values in the consolidated Oracle server will not change.

Write yourself a proper upload_update script, and I suspect your mystery will be solved.

Reg

[former_SQLA_member1694899]

We have partial verbose logging on -vns. If the remote sent a record for update and Mobilink is set to ignore it, wouldn't that be in the log? There are some updates that we ignore on purpose and I see those as ignored in the log.

I. 2019-06-20 13:10:36. <17104> # rows ignored in table REP_GPS : 0

I've created the proper script and now I'll just wait and see if it happens again. If it happens again, I plan to pull the remote database, manually change a value in the record that didn't replicate and replicate it again to see if we get the update.

Thanks

[regdomaratzki]

If rows are being ignored because of the --{ml_ignore} script, I would expect to see that in the ML Log.

I. 2019-06-26 12:51:39. <2> # rows ignored in table Admin : 5

Are you sure you were using and/or changed the correct script version in the consolidated database?

Reg