Mobilink: 1 row(s) were ignored in uploading data into table SYNC_OPTIONS

[Former Member]

Hello,

I have a consolidated database - Oracle/Mobilink Server and remote database - SQL Anywhere/Mobilink Client running on two different machines. When I check the Mobilink logs, it says the below:

I. 2017-02-21 07:47:25. <8115> Request from "Dbmlsync 16.0.2043" for: remote ID: 69d3ac9f-a59d-4dad-8256-61e1d56ecdf5                                                                                           , user name: 0101, version: ML_R1
I. 2017-02-21 07:47:25. <8115> The current synchronization is using a connection with connection ID 'SID 18, SERIAL# 739'
I. 2017-02-21 07:47:25. <8115> COMMIT Transaction: Begin synchronization
I. 2017-02-21 07:47:25. <8115> 1 row(s) were ignored in uploading data into table SYNC_OPTIONS
I. 2017-02-21 07:47:25. <8115> COMMIT Transaction: Upload
I. 2017-02-21 07:47:25. <8115> COMMIT Transaction: Prepare for download
I. 2017-02-21 07:47:25. <8115> Sending the download to the remote database
I. 2017-02-21 07:47:25. <8115> COMMIT Transaction: Download
I. 2017-02-21 07:47:25. <8115> COMMIT Transaction: End synchronization
I. 2017-02-21 07:47:26. <8115> Synchronization complete

I have 3 questions:

1. Why I am getting the message '1 row(s) were ignored in uploading data into table SYNC_OPTIONS'
2. I have manually inserted a record in one of the tables in consolidated database but it is not reflecting in my remote database. What do I need to check?
3. SYNC_OPTIONS in remote database has around 25rows but in consolidated database it is blank.

I am doing a POC on SAP TARS and have just started learning mobilink and sql anywhere.

Please help.

[Former Member]

1 - Why I am getting the message '1 row(s) were ignored in uploading data into table SYNC_OPTIONS'

This is part of Conflict Detection. The error indicates your pre-image does not match the version of the row(s) existing in the consolidated. The ignored part of that is related to not having conflict resolution scripts for this table. If it was not already clear, this is occurring during the upload phase.

2 - I have manually inserted a record in one of the tables in consolidated database but it
    is not reflecting in my remote database. What do I need to check?

This is likely unrelated to the first item. You could just be looking at an uncommitted operation or the row does not yet exist in the current 'snapshot' of the rows. MobiLink uses snapshots isolation with Oracle and will only download the view of the data from the oldest open transaction/snapshot 'timeframe'. Over time this should self correct.

3 - SYNC_OPTIONS in remote database has around 25 rows but in consolidated database it is blank.

One or more of those rows are coming up as [an] update(s). The fact you have no such row(s) already at the consolidated database will represent the conflict already mentioned. {You won't get conflicts with upload_insert based changes.} The fact that you are getting updates to rows that don't already exists at the consolidated is a break of the default MobiLink model and normal behavior. You can probably get past this by [if only temporarily] changing your UPLOAD_UPDATE script for the "SYNC_OPTIONS" table to use either a MERGE statement or an INSERT ON EXISTING UPDATE statement ... BUT that is just a suggestion, NOT a recommendation. Understanding how you intended this work in your first design will be important to understand how you will actually want to deal with this.

[Former Member]

Thanks Nick.

I checked the package body for SYNC_OPTIONS_SYNC in Oracle and it only has download_cursor and download_delete_cursor.

upload_update exists for many other tables but not for this one.

Regarding the snapshot isolation, I committed the data in both consolidated and remote databases. And its over 5.5 hrs now, isn't it odd that the data has not been corrected yet.

[Former Member]

You may have committed those changes (at the consolidated) but only changes earlier than the earliest "START_TIME" from the "GV$TRANSACTION" 'global' system view will be downloaded.

This can sometimes cause a long delay and hold up a lot of data if you have long lived snapshots or long-running transactions. I have encounters some cases much longer than 8 hours.

IFF all of your tables involved with MobiLink synchronizations belong to just the current database you could try using the -dt switch. This will cause the MobiLink server to only consider the START_TIMEs related transactions affecting just the current database.

[Former Member]

Thanks once again!

I was looking into SYS. dba_hist_snapshot table instead of this GV$transaction. START_TIME shows '02/21/17 07:13:06' and I made the change around 02/21/17 6:00am. So the change should have reflected. Strange part is except the conflict error message I have got no other error in the logs.

And I am also not sure about the coflicting error because as I said there's no upload_update script for SYNC_OPTIONS table.

[Former Member]

Strictly speaking this is neither an error (E. ) nor a warning (W. )! You will notice the entry type is an "I." for Informational.

Because of that I suspect you should ignore my earlier answer.

You can get this if you have a --{ml ignore} definied for the upload_* scripts for this table &&AND&& if you have a row changed at the remote.

[regdomaratzki]

You may have committed those changes (at the consolidated) but only changes earlier than the earliest "START_TIME" from the "GV$TRANSACTION" 'global' system view will be downloaded.

This statement is incorrect.

Any committed transaction in the Oracle consolidated database should be downloaded on the next synchronization. When MobiLink detects that snapshot isolation is being used on the consolidated database, it is possible that a committed transaction will be downloaded mutliple times across multiple synchronizations. This can happen if the last_download column being used to track if the row has changed has a timestamp value "X", and "X" is a time between the earliest "START_TIME" from the "GV$TRANSACTION" 'global' system view and the time the synchronization occurs. There should NOT be a delay in downloading the row because of snapshot isolation and uncommitted transactions in the consolidated database, unless the uncommitted operation in question is the one row you are expecting to download. Only committed transactions are downloaded.

[Breck_Carter]

> This is part of Conflict Detection. The error indicates your pre-image does not match the version of the row(s) existing in the consolidated. The ignored part of that is related to not having conflict resolution scripts for this table.

If there are no conflict resolution scripts, the default action is "last update wins".

The message "1 row(s) were ignored in uploading data into table" is described in the Help here.

[regdomaratzki]

The answers to all your questions will be sorted out by understanding the synchronization scripts that have been written for the SYNC_OPTIONS table at the consolidated database.

Do you know how to look at the synchronization scripts in the consoldiated database using either Sybase Central or by querying the MobiLink system tables in the Oracle consolidated database?

Reg

[Former Member]

Thanks Reg.

I am not very pro about Mobilink system tables but from the scripts it looks like only download_cursor and download_delete_cursor have been written for SYNC_OPTIONS table.

I looked into ML_SUBSCRIPTION table, it says the last upload time '21-FEB-17 11.05.28.893000000 PM' and the last_download_time '21-FEB-17 07.13.06.000000000 AM' (the current time in my machine is 21-FEB-17 11:13 PM).

What tables do I need to query?

[regdomaratzki]

If you are querying the consolidated database directly, connect to the consolidated database using the same user that the MobiLink Server uses to connect, and execute the following SQL :

select * from ml_table_scripts where table_name = 'SYNC_OPTIONS'

You will likely see a number of scripts defined :

• upload_insert
• upload_update
• upload_delete
• download_cursor
• download_delete_cursor

The first three determine the SQL that will be executed against the consolidated if an operation (insert/update/delete) from the remote is sent up. The last two determine the SQL statements that will generate result sets that are the data (or data to delete) that will be downloaded to the reote database.

It's probably going to be difficult to answer your question if you don't know whether data on this particular table is supposed to synchronize up and/or down.