How do I stop rows from going missing during MobiLink 12 upload?

[Breck_Carter]

Symptom: Some inserted rows that are uploaded by dbmlysnc.exe 12.0.0.2601 are not inserted by mlsrv12.exe on SQL Anywhere 12.0.1.3152; they appear in the row counts displayed by dbmlsync (for example the checkina table)...

I.  11/6/2014   4:28:55 # rows inserted in table checkina : 565 
I.  11/6/2014   4:28:55 # rows deleted in table checkina : 0    
I.  11/6/2014   4:28:55 # rows updated in table checkina : 0    

but not mlsrv12...

I.  11/6/2014   4:28:52 <3787>  (,2681) # rows uploaded into table checkina : 0
I.  11/6/2014   4:28:52 <3787>  (,2681) # rows inserted into table checkina : 0
I.  11/6/2014   4:28:52 <3787>  (,2681) # rows deleted in table checkina : 0
I.  11/6/2014   4:28:52 <3787>  (,2681) # rows updated into table checkina : 0
I.  11/6/2014   4:28:52 <3787>  (,2681) # rows conflicted in table checkina : 0
I.  11/6/2014   4:28:52 <3787>  (,2681) # rows ignored in table checkina : 0

Rows in one table DO get uploaded successfully...

I.  11/6/2014   4:28:55 # rows inserted in table ebcs_temp_changes : 1  
I.  11/6/2014   4:28:55 # rows deleted in table ebcs_temp_changes : 7   
I.  11/6/2014   4:28:55 # rows updated in table ebcs_temp_changes : 0

I.  11/6/2014   4:28:52 <3787>  (,2681) # rows uploaded into table ebcs_temp_changes : 8
I.  11/6/2014   4:28:52 <3787>  (,2681) # rows inserted into table ebcs_temp_changes : 1
I.  11/6/2014   4:28:52 <3787>  (,2681) # rows deleted in table ebcs_temp_changes : 7
I.  11/6/2014   4:28:52 <3787>  (,2681) # rows updated into table ebcs_temp_changes : 0
I.  11/6/2014   4:28:52 <3787>  (,2681) # rows conflicted in table ebcs_temp_changes : 0
I.  11/6/2014   4:28:52 <3787>  (,2681) # rows ignored in table ebcs_temp_changes : 0

Caveat: The diagnostic logs have been through a conversion to Excel and back to text that has inserted some extra tabs, but AFAIK no other changes were made...

dbmlsync_v_diagnostic_log.txt

mlsync12_v_diagnostic_log.txt

The missing rows are the ONLY symptom; there are no warning or error messages, and all synchronizations are otherwise "successful".

The symptom occurs periodically, but not all the time.

When it does occur, it affect all the remote databases that are using a new version of the MobiLink scripts.

new_mobilink_scripts.txt

An examination of the new and previous script files show no differences except (a) version and (b) the addition of one row.

previous_mobilink_scripts.txt

A check of the actual ml* tables on the consolidated database indicate the scripts actually exist.

Here are the mlsrv12 command line options...

-c "dsn=ml_xxxx_master_12"
-cn 151
-wu 10
-x tls(tls_type=rsa;identity=D:\\XXXX\\Mobilink\\xxxx.id;identity_password=*****;port=2439)
-zp
-cm 100M
-cmax 500M
-o D:\\XXXX\\Mobilink\\logs\\mlserver.mls
-vcfhnpstuU
-dl
-zu+
-os 10M
-zf

[regdomaratzki]

How reproducible is the issue? I'm trying to decide how to attack the issue, as I can either get you/customer to reproduce again with different verbosity options or try to bring the issue in house to try and reproduce.

Do you have multiple publications at the remote?

You said "When it does occur, it affect all the remote databases that are using a new version of the MobiLink scripts". Is there a way to fix the issue once it happens, such as stopping and starting the ML Server?

[Former Member]

Reg,

I am a consultant for the customer in question here, so I'll try and answer your questions.

The issue is seemingly random. Although the connection parameters above don't show all the verbosity options, we set maximum verbosity for the affected users, and there are still no warnings or errors.

The remotes all have one publication which syncs all columns in selected tables.

There is no way to fix the issue once it occurs. The remote sees these rows as processed (since there is no notification from the server that anything went wrong) and the changes are then lost.

[regdomaratzki]

Still confused about Breck's comment on it affecting all remote databases that are using a new verison of the ML Scripts.

Are you saying that as soon as ONE remote database using script version v12_3 experiences the problem that ALL remote databases using this script version start losing rows?

When a remote database has the problem, the next time they synchronize it's clear the rows from the previous synch won't upload again, but do all changes made since the last synch upload successfully?

[Former Member]

Are the remote and consolidated clocks 3 seconds apart?

The dbmlsync snippet you provide is from 4:28:55, the mlsrv snippet you provide is from 3 seconds earlier: 4:28:52.

[Former Member]

He means that its affecting only the users using that version of the scripts (v12_3), and none of the others using other versions (e.g. 12_1 posted above). But either they all fail in this way, or they all are fine.

When a remote starts syncing successfully again, they do upload all of the changes since last sync.

[Former Member]

This does seem to be the case, but it seems negligible. All comparison to last_download and last_upload times are done by the server, so all time is relative to the server. And the syncs are scheduled on a nightly basis, so I would think a few seconds wouldn't be an issue.

If the remote was in a different time-zone than the server, the logs could potentially be off by hours, right? And that shouldn't cause an issue.

[regdomaratzki]

I believe Graham only asked about the time difference to ensure that we were really looking at the correct synchronization in the MobiLink Log to correspond to the synchronization we see in the dbmlsync log, not as a possible root cause of the issue.

[regdomaratzki]

Would you be able to provide us with the output from a "dbunload -n" (i.e. schema only) from the consolidated database, a remote database that synchronizes with the v12_3 script version and a remote database that synchronizes with the v12_1 script version?

[regdomaratzki]

He means that its affecting only the users using that version of the scripts (v12_3), and none of the others using other versions (e.g. 12_1 posted above).

Got it.

But either they all fail in this way, or they all are fine. When a remote starts syncing successfully again, they do upload all of the changes since last sync.

Still confused.

Let's assume we only have a single remote database using script version v12_3. At time "X" the remote I'll call "v12_3.one" synchronizes, but rows are missed for some unknown reason. Which of the following statements is true:

1) The next time "v12_3.one" synchronizes at time "Y" it uploads all changes since the last synchronization (but clearly, the rows that were lost in the "bad" synchronization are lost for good).

2) Remote "v12_3.one" will synchronize a number of times showing the same symptoms (i.e. some rows continue to go missing), but eventually, at time "Y" it starts to successfully upload rows again, although rows that were missed in previous synchronizations are lost.

I'll have a follow-up question to this once I know this answer.

[Former Member]

That's right Reg. From the times, it appeared that the two snippets might have been from different synchronizations.

[Former Member]

It's option 2. It seems to fail for a few days before it rights itself and starts working again. I will work on getting you those schemas.

[Former Member]

Added the 3 schemas to my google drive:

https://drive.google.com/folderview?id=0B12BMB3qBnOaaEFRelpyX09IOGc&usp=sharing

[regdomaratzki]

Option #2. Got it.

So remote database v12_3.one is having issues between time "X" and "Y". In between time "X" and "Y" are all the remote databases that use the v12_3 also having the same issue?

[Former Member]

That's correct.

Another piece of info that we found out today that might be of some use is that when the database/mobilink services are stopped and restarted, the problem goes away. But we still need to figure out what is causing it and why it's not producing an error.

[Former Member]

Just checking in again as it's been a few days. Any ideas?