SQL Anywhere Mobile - How do I get MobiLink to filter the download stream?

Author

How do I get MobiLink to filter the download stream?

Breck Carter [Team iAnywhere]

2006-04-07, 11:31 am

Perhaps someone can point out what I'm doin' wrong here: All the
uploaded changes are being downloaded again in the same
synchronization, nothing is being "filtered out" as described in the
Help.

The test involved 82 DELETE operations on the remote database,
followed by 80 INSERT operations using the same primary keys and 2
INSERT operations with new primary keys. This (correctly) turned into
80 uploaded UPDATEs, 2 uploaded DELETEs and 2 uploaded INSERTs.

ONLY this one table was touched in this test, ONLY on the remote, no
other changes anywhere.

What I don't understand is why they all came back down again... what
stupid thing have I done to turn off filtering?

Breck

===== What the Help says...
MobiLink Administration Guide
Synchronization Basics
The synchronization process
How the upload stream is processed
....
Filtering download rows The most common technique for determining
rows to download is to download rows that have been modified since the
previous download. When synchronizing, the upload precedes the
download. Any rows inserted or updated during the upload will be rows
that have been modified since the previous download.

It would be difficult to write a download_cursor script that omits
from the download stream rows that were sent as part of the upload.
For this reason, the MobiLink synchronization server automatically
removes these rows from the download stream. When a row is being added
to the download stream, the MobiLink synchronization server locates
the row in the upload stream and omits the row from the download
stream when it is found to be the same.
=====

===== dbmlsrv9 console log...
I. 04/07 10:32:05. Adaptive Server Anywhere MobiLink Version
9.0.2.3267
....
I. 04/07 10:32:17. <1.5> [1]: # rows uploaded into table
rate_set_detail : 84
I. 04/07 10:32:17. <1.5> [1]: # rows inserted into table
rate_set_detail : 2
I. 04/07 10:32:17. <1.5> [1]: # rows deleted in table rate_set_detail
: 2
I. 04/07 10:32:17. <1.5> [1]: # rows updated into table
rate_set_detail : 80
I. 04/07 10:32:17. <1.5> [1]: # rows conflicted in table
rate_set_detail : 0
I. 04/07 10:32:17. <1.5> [1]: # rows ignored in table rate_set_detail
: 0
....
I. 04/07 10:32:18. <1.5> [1]: # rows downloaded for delete from table
rate_set_detail : 2
I. 04/07 10:32:18. <1.5> [1]: # rows downloaded for insert/update from
table rate_set_detail : 82
I. 04/07 10:32:18. <1.5> [1]: # rows filtered for download from table
rate_set_detail : 0
I. 04/07 10:32:18. <1.5> [1]: # rows downloaded to remote from table
rate_set_detail : 84
=====

===== dbmlsync console log...
I. 04/07 10:32:15. Adaptive Server Anywhere MobiLink Synchronization
Version 9.0.2.3267
....
I. 04/07 10:32:16. # rows inserted in table rate_set_detail : 2
I. 04/07 10:32:16. # rows deleted in table rate_set_detail : 2
I. 04/07 10:32:16. # rows updated in table rate_set_detail : 80
....
I. 04/07 10:32:20. Downloading into table: rate_set_detail
I. 04/07 10:32:20. # rows inserted/updated into table rate_set_detail
: 82
I. 04/07 10:32:20. # rows deleted in table rate_set_detail : 0
I. 04/07 10:32:20. 2 delete operations downloaded for table
"rate_set_detail" were for rows not found in the remote database.
=====

===== MobiLink scripts...
CALL ml_add_table_script ( 'v1', 'rate_set_detail', 'upload_insert', '
INSERT xxx.rate_set_detail (
rs_id,
rsd_id,
rsd_from_date,
rsd_thru_date,
rt_id )
VALUES ( ?, ?, ?, ?, ? )
' );

CALL ml_add_table_script ( 'v1', 'rate_set_detail', 'upload_update', '
UPDATE xxx.rate_set_detail SET
rsd_from_date = ?,
rsd_thru_date = ?,
rt_id = ?
WHERE rs_id = ?
AND rsd_id = ?
' );

CALL ml_add_table_script ( 'v1', 'rate_set_detail', 'upload_delete', '
DELETE xxx.rate_set_detail
WHERE rs_id = ?
AND rsd_id = ?
' );

CALL ml_add_table_script ( 'v1', 'rate_set_detail',
'download_delete_cur
sor', '
SELECT DELETED_rate_set_det
ail.rs_id,
DELETED_rate_set_det
ail.rsd_id
FROM xxx. DELETED_rate_set_det
ail
WHERE DELETED_rate_set_det
ail.DELETED_ON > ? -- last_download
timestamp parameter

' );

CALL ml_add_table_script ( 'v1', 'rate_set_detail', 'download_cursor',
'
SELECT rate_set_detail.rs_id,
rate_set_detail.rsd_id,
rate_set_detail.rsd_from_date,
rate_set_detail.rsd_thru_date,
rate_set_detail.rt_id
FROM xxx.rate_set_detail
WHERE rate_set_detail. last_updated_datetim
e > ? -- last_download
' );
=====

===== Publication...
CREATE PUBLICATION xxx.basic (
....
TABLE xxx.rate_set_detail ( rs_id,
rsd_id,
rsd_from_date,
rsd_thru_date,
rt_id ),
=====

===== Schema...
-- xxx.rate_set_detail (table_id 459) in xxx - Apr 7 2006 10:08:58AM -
Foxhound © 2005 RisingRoad

CREATE TABLE xxx.rate_set_detail ( -- 22,565 rows, 2.2M total = 704K
table + 144K ext + 1.3M index
rs_id /* PK FK */ INTEGER NOT NULL,
rsd_id /* PK */ INTEGER NOT NULL,
rsd_from_date DATE NOT NULL,
rsd_thru_date DATE NOT NULL,
rt_id /* FK */ INTEGER NOT NULL,
last_updated_datetim
e TIMESTAMP NOT NULL DEFAULT
timestamp,
CONSTRAINT ASA88 PRIMARY KEY ( -- 656K
rs_id,
rsd_id ) );

-- Parents of xxx.rate_set_detail --
-- xxx.Rate_Set
-- xxx.rate_table
-- Children --
-- none --

ALTER TABLE xxx.rate_set_detail ADD CONSTRAINT "rs_id_rsd_to rate_set"
NOT NULL FOREIGN KEY ( -- 336K
rs_id )
REFERENCES xxx.Rate_Set (
rs_id )
ON UPDATE RESTRICT ON DELETE CASCADE;

ALTER TABLE xxx.rate_set_detail ADD CONSTRAINT rt_id_rsd_to_rate_ta
ble
NOT NULL FOREIGN KEY ( -- 372K
rt_id )
REFERENCES xxx.rate_table (
rt_id )
ON UPDATE RESTRICT ON DELETE CASCADE;
=====

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/ SQL_Anyw...pers_Guide.html
breck.carter@risingroad.com

Greg Fenton

2006-04-07, 11:31 am

Breck Carter [Team iAnywhere] wrote:
> What I don't understand is why they all came back down again... what
> stupid thing have I done to turn off filtering?

Version, build number? ;-)

Is it possible you have a timestamp precision mismatch or something similar?

I don't think you can turn off filtering. Essentially, ML maintains the
upload stream and during the construction of the download stream it
filters out any rows that *exactly* match (value for value) any row in
the upload.

So *something* is different between the uploaded rows and the downloaded
rows. Turn on full verbosity and compare row values?

greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Breck Carter [Team iAnywhere]

2006-04-07, 1:31 pm

I am even more puzzled, please see responses below...

On 7 Apr 2006 10:31:04 -0800, Greg Fenton
<greg. fenton_NOSPAM_@ianyw
here.com> wrote:

>Version, build number? ;-)

Just like it said in my message (snap! :)...

===== dbmlsrv9 console log...
I. 04/07 10:32:05. Adaptive Server Anywhere MobiLink Version
9.0.2.3267

>Is it possible you have a timestamp precision mismatch or something similar?

Same version of ASA on both sides, remote was created by copying the
consolidated, no timestamp columns being synchronizied, so I don't
think so.

>I don't think you can turn off filtering. Essentially, ML maintains the
>upload stream and during the construction of the download stream it
>filters out any rows that *exactly* match (value for value) any row in
>the upload.
>
>So *something* is different between the uploaded rows and the downloaded
>rows. Turn on full verbosity and compare row values?

Here are some excerpts showing what happens with a single row; can you
see any differences?

FWIW I tried creating a simple reproducible but it does NOT
demonstrate the problem, only this production setup does; can you
think of anywhere else for me to look?

There is a shadow table delete trigger, but it does nothing to *this*
table, and it isn't fired for the update shown below.

Breck

===== dbmlsrv9 console log...
I. 04/07 13:30:32. Adaptive Server Anywhere MobiLink Version
9.0.2.3267
....
I. 04/07 13:30:43. <1.5> [1]: upload_update rate_set_detail (open for
write):
UPDATE rai.rate_set_detail SET
rsd_from_date = ?, rsd_thru_date = ?,
rt_id = ? WHERE rs_id = ?
AND rsd_id = ?
I. 04/07 13:30:43. <1.5> [1]: Translated SQL:
UPDATE rai.rate_set_detail SET
rsd_from_date = ?, rsd_thru_date = ?,
rt_id = ? WHERE rs_id = ?
AND rsd_id = ?
I. 04/07 13:30:43. <1.5> [1]: Updated row (new values):
I. 04/07 13:30:43. <1.5> [1]: 714
I. 04/07 13:30:43. <1.5> [1]: 3652
I. 04/07 13:30:43. <1.5> [1]: 2004-11-25
I. 04/07 13:30:43. <1.5> [1]: 2004-12-15
I. 04/07 13:30:43. <1.5> [1]: 4236
....
I. 04/07 13:30:44. <1.5> [1]: download_cursor rate_set_detail (open
for read):
SELECT rate_set_detail.rs_id,
rate_set_detail.rsd_id,
rate_set_detail.rsd_from_date,
rate_set_detail.rsd_thru_date,
rate_set_detail.rt_id FROM rai.rate_set_detail
WHERE rate_set_detail. last_updated_datetim
e > ? -- last_download
I. 04/07 13:30:44. <1.5> [1]: Translated SQL:
SELECT rate_set_detail.rs_id,
rate_set_detail.rsd_id,
rate_set_detail.rsd_from_date,
rate_set_detail.rsd_thru_date,
rate_set_detail.rt_id FROM rai.rate_set_detail
WHERE rate_set_detail. last_updated_datetim
e > ? -- last_download
....
I. 04/07 13:30:44. <1.5> [1]: Insert/Update row:
I. 04/07 13:30:44. <1.5> [1]: 714
I. 04/07 13:30:44. <1.5> [1]: 3652
I. 04/07 13:30:44. <1.5> [1]: 2004-11-25
I. 04/07 13:30:44. <1.5> [1]: 2004-12-15
I. 04/07 13:30:44. <1.5> [1]: 4236
....
I. 04/07 13:30:44. <1.5> [1]: # rows downloaded for delete from table
rate_set_detail : 2
I. 04/07 13:30:44. <1.5> [1]: # rows downloaded for insert/update from
table rate_set_detail : 74
I. 04/07 13:30:44. <1.5> [1]: # rows filtered for download from table
rate_set_detail : 0
I. 04/07 13:30:44. <1.5> [1]: # rows downloaded to remote from table
rate_set_detail : 76
=====

===== dbmlsync console log...
I. 04/07 13:30:42. Adaptive Server Anywhere MobiLink Synchronization
Version 9.0.2.3267
....
I. 04/07 13:30:43. Upload operations on table 'rate_set_detail'
....
I. 04/07 13:30:43. Update row:
I. 04/07 13:30:43. Preimage:
I. 04/07 13:30:43. <rs_id>: 714
I. 04/07 13:30:43. <rsd_id>: 3652
I. 04/07 13:30:43. <rsd_from_date>: 2004-11-25
I. 04/07 13:30:43. <rsd_thru_date>: 2004-12-15
I. 04/07 13:30:43. <rt_id>: 4236
I. 04/07 13:30:43. Postimage:
I. 04/07 13:30:43. <rs_id>: 714
I. 04/07 13:30:43. <rsd_id>: 3652
I. 04/07 13:30:43. <rsd_from_date>: 2004-11-25
I. 04/07 13:30:43. <rsd_thru_date>: 2004-12-15
I. 04/07 13:30:43. <rt_id>: 4236
....
I. 04/07 13:30:43. # rows inserted in table rate_set_detail : 2
I. 04/07 13:30:43. # rows deleted in table rate_set_detail : 2
I. 04/07 13:30:43. # rows updated in table rate_set_detail : 72
....
I. 04/07 13:30:46. Downloading into table: rate_set_detail
....
I. 04/07 13:30:46. Insert/Update row:
I. 04/07 13:30:46. <rs_id>: 714
I. 04/07 13:30:46. <rsd_id>: 3652
I. 04/07 13:30:46. <rsd_from_date>: 2004-11-25
I. 04/07 13:30:46. <rsd_thru_date>: 2004-12-15
I. 04/07 13:30:46. <rt_id>: 4236
....
I. 04/07 13:30:46. # rows inserted/updated into table rate_set_detail
: 74
I. 04/07 13:30:46. # rows deleted in table rate_set_detail : 0
I. 04/07 13:30:46. 2 delete operations downloaded for table
"rate_set_detail" were for rows not found in the remote database.
=====

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/ SQL_Anyw...pers_Guide.html
breck.carter@risingroad.com

Breck Carter [Team iAnywhere]

2006-04-07, 8:26 pm

The existence of these foreign key relationships seems to make the
difference. If these relationships are present, a rate_set_detail
UPDATE that is uploaded is NOT filtered out of the download. If these
relationships are omitted, dbmlsrv9 behaves as expected and filters
the rows out of the download.

Note that rows in the parent tables are NOT being modified in any way,
just the child table rate_set_detail.

Breck Starting To Smell A Stinky "Feature" :)

ALTER TABLE rai.rate_set_detail ADD CONSTRAINT "rs_id_rsd_to rate_set"
NOT NULL FOREIGN KEY ( -- 336K
rs_id )
REFERENCES rai.Rate_Set (
rs_id )
ON UPDATE RESTRICT ON DELETE CASCADE;

ALTER TABLE rai.rate_set_detail ADD CONSTRAINT rt_id_rsd_to_rate_ta
ble
NOT NULL FOREIGN KEY ( -- 372K
rt_id )
REFERENCES rai.rate_table (
rt_id )
ON UPDATE RESTRICT ON DELETE CASCADE;

On 7 Apr 2006 10:31:04 -0800, Greg Fenton
<greg. fenton_NOSPAM_@ianyw
here.com> wrote:

>Breck Carter [Team iAnywhere] wrote:
>
>Version, build number? ;-)
>
>Is it possible you have a timestamp precision mismatch or something similar?
>
>I don't think you can turn off filtering. Essentially, ML maintains the
>upload stream and during the construction of the download stream it
>filters out any rows that *exactly* match (value for value) any row in
>the upload.
>
>So *something* is different between the uploaded rows and the downloaded
>rows. Turn on full verbosity and compare row values?
>
>greg.fenton

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/ SQL_Anyw...pers_Guide.html
breck.carter@risingroad.com

Sponsored Links