cancel
Showing results for 
Search instead for 
Did you mean: 

LAST_DOWNLOAD_TIME is not changing after successful sync

Former Member
3,157

Hello, I am using SQLAnyWhere 12.0.1.3152 and am connecting an Oracle 10g database to an UltraLite db on a handscanner that runs Windows CE. I have had no issues with my synchronization until recently. I was setting up a new scanner, and after running my initial synchronization I noticed that the number of records being downloaded where not changing. I am using timestamp based downloads that is utilizing shadow tables. I noticed that in the ML_SUBSCRIPTION the LAST_DOWNLOAD_TIME is not being updated for the user. However the LAST_UPLOAD_TIME is being updated correctly. I have no custom coding that would keep setting the ml s.last_download_time to a fixed value.

I have tried looking through the forums but have not found anything that resembled my current issue. If anybody has any suggestions as to what I can try, I would appreciate it very much. Thank you in advance.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

The issue is a long-running transaction against the consolidated database. In order for sync to download the rows that have changed, it can only look at completed transactions. If there is a long-running transaction, the last-download time (LDT) cannot advance until it completes. Why? Because that transaction may have last_modified (or equivalent) logic that has dates set to the starting time of the transaction. To maintain robust sync, there is no choice but to defer the advancement of the LDT.

I suspect the reason why it worked when you shut down ML server is that a long-running sync was in progress, or hung. Or at the same time the long-running transaction finished.

When the LDT fails to advance, please use your Oracle (or RDBMS-specific) admin tools to look for long-running (eg. batch) transactions. Even if the transactions do not affect sync tables, by default they will defer the advancement of the LDT.

  • Russ
Former Member
0 Kudos

Russ will you please look at my comment that I posted shortly after you put your answer up. Thank you

Former Member

For more background on why long-running transactions in Oracle can cause the same rows to be downloaded again, see this old tech note. Note that since version 9.0.2.3021, MobiLink defaults to using the earliest start time of open Oracle transactions to set the LDT. So as long as an Oracle transaction is uncommitted, the LDT cannot advance.

You could check by running the following query against your Oracle consolidated to see if it matched the unchanging LDT value:

SELECT MIN( TO_DATE( START_TIME, 'mm/dd/rr hh24:mi:ss' ) ) FROM GV$TRANSACTION;
Former Member
0 Kudos

Graham, I ran that query and it returned a null result. I ran it as a SELECT * and I had a record come back, but it was for today at 8:31 AM, not the 1/28/13 9:41:39 AM that I consistently getting from sync process. Thank you for the suggestion

Former Member

Did you connect as the same Oracle user as MobiLink server?

Former Member
0 Kudos

Initially I did not connect as the MobiLink Oracle user. I just did and re-ran the query. I did not get a null answer this time, but the time I did get is 1/29/13 8:46:43 AM which is still greater than the 1/28/13 9:41:39 AM I get when I sync.

I really do appreciate your help Russ. Is there anyway to force the LDT to a different value?

Former Member

That query would return null if there were no transactions with uncommitted changes, so your long running transaction must have committed or rolled back.

FYI, this is the query that the MobiLink server runs (for Oracle 9 and above) to determine the next LDT:

SELECT TO_CHAR(NVL( MIN( TO_TIMESTAMP( START_TIME, 'mm/dd/rr hh24:mi:ss' ) ), SYSTIMESTAMP ),'YYYY-MM-DD HH24:MI:SS.FF' ) FROM GV$TRANSACTION"

The result of that query is given in this line of your log output, proving that at 2013-01-28 14:37:26 there was a still an open transaction started at 2013-01-28 09:41:39:

 1564: I. 2013-01-28 14:37:26. <1> Next last download timestamp fetched from the consolidated database is "2013-01-28 09:41:39.000000"
Former Member

Just to update, my sync process is now working correctly. I believe everyone was right about there being an outstanding transaction. Thank you so much for your quick and helpful responses. You have helped me get past a sticking point in my project.

Former Member
0 Kudos

You should determine what is causing the long transactions, since apparently you've got another (that started at 08:46:43 today).

Answers (1)

Answers (1)

Former Member

i had exactly the same issue. Once the 'killed' Oracle session was gone, my 'last downloaded' was updating correctly again.

Thanx a lot only because of these posts i could figure out what was going on.

martin