mobilink tries to insert data that already exists on the consolidated database

[Former Member]

Hi,

We just migrated a consolidated database to a new mobilink service.

a lot of users with an old remote database are experiencing synchronization problems. The mobilink log is indicating "upload_inserts" and giving "unique constraint" errors on several tables... So it's trying to insert existing data from a few hours before the migration.

Does anyone with knowledge of the inner workings of mobilink have an idea on how this might be happening?

Our current solution is recreating the remote database and this seems to work, but I would like to know what's going on really..

We probably missed something... Any tips?

[regdomaratzki]

Can you provide details on how you migrated the consolidated database to a new Mobilink Server?

You said "a lot of users with an old remote database", which leads me to believe that some users experienced the problem and others did not. Is this true? If so, what do you mean by "an old remote database"?

[Former Member]

we have a mobile application with a remote database.

What we did is the following.

We have mobilink server A and Consolidated database A First We ask all users to synchronize. Then we block the network connection

We installed "mobilink server" B on Consolidated database B then we moved all data that is not related to mobilink "_ml" tables to Consolidated database B (from A) We executed the same synchronisation script for Consolidated database B as we once did on Consolidate database A.

Then we changed the ip from mobilink server A to mobilink Server B. So now the mobile app thinks that mobilink server B is actually mobilink server A. We open up the network connection

and when starting synchronization I see in the logs that the mobilink user is not known, so mobilink creates this user on Consolidated database B.... So this is good. Synchronization seems to work for people who have not added any data to their remote database on their mobile device...

for the ones that have added data to their mobile device before the migration are experiencing problems... They synchronised before the migration and their new data is on the Consolidated database... so when they are synchronizing now, they get a synchronization error with the message that the primary key already exists because mobilink is trying to insert a record that is already there (I see this on the mobilink server).

people who start out with an empty remote database after the migration are not experiencing any problems.

Thanks

[regdomaratzki]

Are ALL the databases that existed before the migration having the same problem, or only some?

Reg

PS : I believe had you migrated over all the ml_ tables to Consolidated database B, you would not have had this issue.

[Former Member]

From what I see, all databases have this problem if they have inserted data on the mobile side before the migration...

... you may be right about the migration of the ml_ tables... we may have wrongly assumed that mobilink would be smart enough to handle this correctly ... I am currently looking for some documentation or someone with knowledge in this area that can confirm or disprove this or even bring in a new reason for what's happening.....

[VolkerBarth]

I believe had you migrated over all the ml_ tables to Consolidated database B, you would not have had this issue.

It seems the migration has "emptied" the whole state information on the ML server side, so each remote is handled as new, and all its data is uploaded. I (wildly!) guess that the ML client should warn about ambiguous progress/log offsets between client and server, so you may have a chance to force the server to accept the client's point of view - cf. "dbmlsync -ra" BBBUUUTTT handle with extreme care and only if you are absolutely sure you know what you are about to do and have tested this very thoroughly...) No, I do not recommend that option here at all.

[regdomaratzki]

Note : All data from the remote will NOT be uploaded when the MobiLink tables are emptied at the consolidated database. The confirm_sent column in the SYSSYNC table for the user/publication combination will be used as the starting point for the next synchronization.

[VolkerBarth]

OK, then MobiLink is much smarter in this respect than I had expected - shame on me:)

[regdomaratzki · Accepted solution]

Note : I assumed dbmlsync and not UL, although the concepts are the same regardless of ML client.

Executive Overview of progress tracking in MobiLink :

1. At the remote database, the ML Client keeps track of two log offsets. The first is the log offsets that have been sent to the consolidated database. This offset is updated when an upload is sent. The second offset is the log offset that has been confirmed as applied at the consolidated database. This offset is updated when the remote database receives the download stream associated with the upload it just sent.
2. At the consolidated database (in the ml_ tables) the consolidated also keeps track of the log offset it has successfully applied from each remote. This value is updated when the upload stream is successfully committed.
3. It's possible for the two offsets in the remote to be different if an upload was sent but no download was received. In this case, the next time the remote wants to synch, it queries the consolidated database to ask if the last upload was applied and then sends an upload starting at the offset that the consolidated database is expecting.
4. If remote_id (i.e. the value that identifies the remote database) associated with the current synchronization does not exist in the consolidated database (your situation after the migration), then the MobiLink server assumes that the offsets from the remote database are correct.

In your situation I would have expected all the remote databases that existed before the migration to have worked fine, UNLESS the last synchronization before the migration resulted in an upload being sent and successfully applied to the consolidated database, but no download was received by the remote database. In that scenario, the remote will have sent all operations from offset X1 -> X2, these would have been applied at the consolidated, but the remote would only have received a confirmation that operations up to offset X1 had been applied, since no download was received. After the migration, when the remote database synchronized again, it would have realized that it didn't know whether the last upload was applied, since the two offsets would not match. The remote would have asked the consolidated if operations from X1 -> X2 were applied, but since the remote_id had been deleted from the system tables, the ML Server could not say if it had of not. The remote client would assume it was correct, and assumed that the operations from X1 -> X2 had not been applied, since it was never told they were applied. The operations from X1 -> X2 would be sent again, resulting in pkey violation.

Is it possible that ALL the "last" synchs from the remote databases resulted in no download stream arriving at the remote database?

Reg

[Former Member · Accepted solution]

Thank you for your extensive explanation. That may certainly be possible. Is it correct to assume that if there is nothing to download that the download is not executed ?

[regdomaratzki · Accepted solution]

I'm not exactly sure what you mean by "not executed". The SQL to generate the download is always executed. If there is no data to download (because the download scripts executed and returned no data), a confirmation that the upload was applied is still sent to the remote client.