Timestamp erased when copying a Mobilink synchronization setup?

[Former Member]

Testing a synchronization setup for a customer.

• I've copied a snapshot of the consolidated and remote databases.
• Setup an identical synchronization environment on a test server with the exceptions:
  • Since several MobiLink servers are running on the testserver I changed port to 65000 (where this test server is listening) on the clients.
  • Database and MobiLink services were recreated rather than copied
• Consolidated to Remote download is governed by synchronization scripts and a timestamp to differ old rows from updated ones.

On the first synchronization I notice that everything, including old data, is downloaded to the remote database. It is as if the timestamp has been reset to the client thinks that everything on the consolidated database is updated 😞 As an example I now get a syncrhonization error from a row last updated 2010-09-30 11:27:49.476000.

When connecting to the Mobilink server I see that each remote database is registered with 15 subscriptions for the same publication and user. Probably there has been created a new one for each time the synchronization is redeployed (that has been done now and then). Is it possible to delete the unused subscriptions ? (The list in DBA.ml_subscription)

EDIT: Came across something in the documentation http://dcx.sybase.com/index.html#1201/en/mlserver/ml-synchtech-s-3939388.html:

The MobiLink server sends this TIMESTAMP value as part of the download, and the client stores it.

This might be my problem. As I copied a setup from another server I might not have copied the client timestamp. Docs state that it is stored in the Sys.SYSSYNC view, but this view is empty on my remote database. That brings me to the question; Where does the Mobilink client store its timestamp and how can I add it to my test setup ?

[VolkerBarth]

Just as a hint: If the client has never synchronized, then a default value is used, as documented here:

The last download timestamp is provided as a parameter to many MobiLink events. The last download timestamp is the value obtained from the consolidated database during the last successful synchronization immediately before the download phase. If the current MobiLink user has never synchronized, or has never synchronized successfully, this value is set to 1900-01-01.

The following page in the docs has some hints to fix the last download timestamp.

[Former Member]

I see. So now Mobilink tries to sync a lot of data as updates from the consolidated database when they are already present at the remote database. I don't want this to happen. So I might do something like this then ?

CREATE PROCEDURE ModifyDownloadTimestamp(
    @download_timestamp DATETIME OUTPUT,
    @user_name    VARCHAR( 128 )) AS
    BEGIN
        SELECT @download_timestamp = Now();
    END

My intentions are to ignore everything present in the consolidated database. The Consolidated and Remote database are in sync, its just my timestamp that is not.

Then, the manual tells me to call a system procedure on the consolidated database. Here, I cannot see how this will affect a peticular Mobilink user:

CALL ml_add_connection_script(
    'my_version',
    'modify_last_download_timestamp',
    '{CALL ModifyDownloadTimestamp({ml s.last_download}, {ml s.username})}')

The manual ends here, with no explanation of what happens next.

• Will this trigger every time ? ... or only when the first Mobilink client synchronises ?
• Will this affect all users ? ... or only the first synchronizing client ? ... or if the script is permanent, then what ? I need to change it to some default then ?
• How can i ensure that this will apply for one user only ?
• Should I halt other synchronization clients to stop them from interrupting with my manual tweaking ?

I'm sorry to say this but here, as with many other places the documentation is lacking content and has the potential to ruin someones day. What's the point of having documentation when after reading the manual you have to resort to internet forums to get the completing part of the story ? More than ofen things are explained as "If that's your problem, you should to THIS. But I won't tell you what THIS does, you just have to be happy if it solved your problem"

[jeff_albion]

• Will this trigger every time ?

Yes, the "modify_last_download_timestamp" event will fire for all clients that are requesting the script version 'my_version'.

• Will this affect all users ? ... or only the first synchronizing client ?

It will affect all users, for every synchronization request.

• How can i ensure that this will apply for one user only ?

You should add/change logic inside your ModifyDownloadTimestamp procedure to look at the IN parameters, to either decide to do something for a specific user:

 BEGIN
      IF @user_name = 'jeffalbion' THEN
         ...
      END IF;
 END;

Or if you're looking for 'first synchronizations' only, you could use the knowledge of the default timestamp parameter:

 BEGIN
      IF @download_timestamp = '1900-01-01 00:00:00.000' THEN
         ...
      END IF;
 END;

• Should I halt other synchronization clients to stop them from interrupting with my manual tweaking ?

Are you doing this in test or production...? (Hopefully test?)

The consolidated stored procedure definition in SQL Anywhere will change immediately with the execution of the CREATE PROCEDURE.

By default on MobiLink server 12 though, the synchronization scripts are only read during server start-up, so your changes to the event script will only be detected upon server restart or if you have started the MobiLink server with the "mlsrv12 -zf" switch. - then it will change immediately as per the next synchronization that arrives at the server.

• many other places the documentation is lacking content and has the potential to ruin someones day

We value this feedback and certainly want to improve the documentation where we can. If there are areas that are unclear or if you have additional questions that aren't answered on the page, please feel free to login and make a comment at our http://dcx.sybase.com/ site for us to review and consider in future versions of the documentation.

That also being said, I encourage you to also look through the SQL Anywhere samples / tutorials that are included with the product to help you get the "hands-on" experience you need - many of the documentation features become clearer once you have a working solution in-hand.

• you have to resort to internet forums to get the completing part of the story

While the documentation intends to be complete as it can, it unfortunately cannot be a complete replacement for trained individuals who are more knowledgable on underlying implementation details and background information on the product/history/problem you're working on, who can answer your specific questions. For a long time now, we have had the NNTP Newsgroups for this purpose to help customers in this manner - this website is the newest version of that same information exchange.

Beyond this (free!) help, you are always welcome to call up technical support and ask whichever question(s) you like and we will strive to respond with as technically accurate and detailed information as we can.

[Former Member · Accepted solution]

Well, I must say that these internet forums are one of the things I praise Sybase for when discussing database engines with other people 😃

So if I understood this correctly (I want to alter the timestamp only once and only for a given user), lets say I have a mobilink user names mluser and on 13 October 12:00 I knew that the databases were in sync;

Then I can make my ModifyDownloadTimestamp script to do something like this ?

CREATE PROCEDURE ModifyDownloadTimestamp( 
    @download_timestamp DATETIME OUTPUT, 
    @user_name VARCHAR( 128 )) AS 
BEGIN 
    IF  (@download_timestamp < '2012-10-03 12:00') AND
        (@user_name = 'mluser')
    THEN
        SELECT @download_timestamp = '2012-10-03 12:00';
        RETURN;
    END IF;
    --Default handling goes here
    SELECT @download_timestamp = @download_timestamp;
END

[jeff_albion · Accepted solution]

Then I can make my ModifyDownloadTimestamp script to do something like this ?

You can code anything you want to really -- which is the great part about MobiLink! -- and yes, this logic looks to be closer to what you were looking for originally.

This way you'll only be going back a few days in your initial data sync rather than looking for all historic data. Once the user has initially caught up, they can then go back to the normal timestamp increment behaviour.

[VolkerBarth]

Another (though obviously undocumented) method might be to simply adjust the values in the ml_subscription ML system table in the consolidated database. From ML's point of view, that's a system table, but from a DBA' point of view it's just another user-defined table.

That's what stored procedures like ml_delete_sync_state_before do, as well, as Russ has pointed towards. However, whereas they generally drop/reset state information, you apparently would need to "correct" values here like the "progress" counter to make them fit those of the remote.

A BIG CAVEAT: Unless you are really aware of the meaning of those values, modifying them is expected to lead to problems.

---

Note: I've never done that with v12 but had to do so with older versions (v8 in particular).

[Former Member]

That seems to be the "easy offsett" solution I was looking for in the first place.

Do I have to adjust "progress" if I alter "last_upload_time" or "last_download_time" ?

[VolkerBarth]

I don't know, and my BIG CAVEAT still goes.

FWIW, in v8, there are no "last_upload_time" or "last_download_time" columns in the ml_subscription table so "progress" was all that was to modify, generally to make it fit to the remote's values in system view syssync (or vice versa). - If consolidated and remote have different values for the offset value, then the sync process by default gives the consolidated's value a higher priority unless you use switches like -ra or -rb.

As stated, these are just some details from older versions. This is by no means an invitation to do so. I guess newer versions are smarter when handling differences with progress offsets and the like. USE AT YOUR OWN RISK.

[Former Member]

OK, sounds like a last resort option only 😃

[Former Member]

If you know the date after which only new remotes have synchronized, ie. before this date only old, unused remote IDs and subscriptions were used, see the ml_delete_sync_state_before stored procedure. The doc is here.

• Russ