MobiLink overwrites new Values

[ArthurHCH]

Hi

I have the problem that MobiLink overwrites new values with old ones. Story:

1. User 1 and User 2 do a synchronization.
2. User 1 changes the value of column 1 and synchronizes.
3. User 2 changes the value of column 2 and syncrhonizes.
4. On the server column 1 and column 2 have now the values from User 2, the changes of User 1 are gone.

In the logs attached (User 2) you can see that "internalremark" and "changed" are updated. All the other columns are not touched.

On the server MobiLinks makes an update with all values of the record. My expectation is that only values changed by User 2 are sent and updated.

What's wrong here?

Arthur

Log below as I don't have enough points for attachments

/*********

Log entries from Client 2

******/

--CONNECT-1024-0034899608-DBA-2012-11-19 05:50
--BEGIN TRANSACTION-1024-0034899619
BEGIN TRANSACTION
go
--UPDATE-1024-0034899642
UPDATE DBA.orders
   SET internalremark='Bemerkungen Test',
       changed='2012-11-19 00:00'
 WHERE orderid=540000002
go
--COMMIT-1024-0034899740
COMMIT WORK
go
--CONNECT-1031-0034899806-DBA-2012-11-19 05:50
--APPLICATION CONNECTION REGISTRATION STAMP:-1031-0034899817
--  app_Name = sybase.asa.dbmlsync
--  app_info_str = uid=DBA;
--  conn_label = Main
--  status = 0x3
--INTERNAL STORED PROCEDURE-1031-0034899859
call sa_sync_sub( 1, 'dba', 'SET LOG_SENT', '34899799' )
go
--COMMIT-1031-0034899893
COMMIT WORK
go
--INTERNAL STORED PROCEDURE-1031-0034899896
call sa_sync_sub( 1, 'dba', 'SET LASTUPLOADTIME', '2012-11-19 05:50:31.146000' )
go
--INTERNAL STORED PROCEDURE-1031-0034899954
call sa_sync_sub( 1, 'dba', 'SET GENERATION_NUMBER', '1' )
go
--COMMIT-1031-0034899990
COMMIT WORK
go
--INTERNAL STORED PROCEDURE-1031-0034899993
call sa_sync_sub( 1, 'dba', 'SET PROGRESS', '34899799' )
go
--COMMIT-1031-0034900027
COMMIT WORK
go
--APPLICATION CONNECTION REGISTRATION STAMP:-1031-0034900030
--  app_Name = sybase.asa.dbmlsync
--  app_info_str = uid=DBA;
--  conn_label = Main
--  status = 0x1000003
--INTERNAL STORED PROCEDURE-1031-0034900072
call sa_sync_sub( 1, 'dba', 'SET LASTDOWNLOADTIME', '2012-11-19 05:50:31.150000' )
go
--COMMIT-1031-0034900132
COMMIT WORK
go
--CHECKPOINT-0000-0034900194-2012-11-19 05:53

/*********

Log entries from MobiLink Server

******/

I. 2012-11-19 05:50:31. <29> (,dba) Translated SQL:
                        UPDATE "DBA"."orders"
                        SET "maintenancecontractid" =  ?,
                            "currencyid" =  ?,
                            "ordernumber" =  ?,
                            "offernumber" =  ?,
                            "offertypeid" =  ?,
                            "stateid" =  ?,
                            "orderdate" =  ?,
                            "orderedby" =  ?,
                            "departmentid" =  ?,
                            "ordertypeid" =  ?,
                            "iqnumber" =  ?,
                            "siteid" =  ?,
                            "placeofaction" =  ?,
                            "description" =  ?,
                            "preferreddate" =  ?,
                            "plannedfrom" =  ?,
                            "plannedto" =  ?,
                            "accessinformation" =  ?,
                            "internalremark" =  ?,
                            "externalremark" =  ?,
                            "offerremark" =  ?,
                            "offertotal" =  ?,
                            "materialcosts" =  ?,
                            "materialinvoiced" =  ?,
                            "servicecosts" =  ?,
                            "serviceinvoiced" =  ?,
                            "totalinvoiced" =  ?,
                            "profit" =  ?,
                            "profitpercentage" =  ?,
                            "created" =  ?,
                            "creatorid" =  ?,
                            "changed" =  ?,
                            "changerid" =  ?,
                            "url" =  ?,
                            "contractyear" =  ?,
                            "userid" =  ?,
                            "leadfinder" =  ?,
                            "orderactivityid" =  ?
                        WHERE "orderid" =  ?

I. 2012-11-19 05:50:31. <29> (,dba) Update row (new remote values) [orders]:
I. 2012-11-19 05:50:31. <29> (,dba)   540000002
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba)   1
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba)   [11 Characters]
I. 2012-11-19 05:50:31. <29> (,dba)   0x41444D3230313230303031
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba)   3
I. 2012-11-19 05:50:31. <29> (,dba)   2012-11-16 00:00:00.000000
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba)   1
I. 2012-11-19 05:50:31. <29> (,dba)   10000002
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba)   540015720
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba)   [16 Characters]
I. 2012-11-19 05:50:31. <29> (,dba)   0x42656D65726B756E67656E2054657374
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba)   2012-11-16
I. 2012-11-19 05:50:31. <29> (,dba)   2
I. 2012-11-19 05:50:31. <29> (,dba)   2012-11-19
I. 2012-11-19 05:50:31. <29> (,dba)   2
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) NULL
I. 2012-11-19 05:50:31. <29> (,dba) end_upload_rows orders (no script)

[VolkerBarth]

It seems that you don't use any of the "conflict detection" scripts. As such, the conflict seems to get unnoticed, and the complete contents of the last synchronized row becomes the new consolidated contents.

[ArthurHCH]

This means that MobiLink is by default working on a per row base overwriting either non or all values. So it's not possible to send updates of single columns without customer script handler. I will have to write a custom resolve script for all my tables in the synchronization (more than 100)? (Inserting in a temptable on conflict resolution makes sense when it comes to overwrites on the same column)

[VolkerBarth]

AFAIK, yes - but I'm no ML expert, so you better wait for a clarification...

---

Aside: AFAIK, that is a difference to SQL Remote where the actual UPDATE statement is replicated whereas ML sents whole rows with "before and after" images):

With SQL Remote, if you just modify one column, only that one column is "sent" along with the PK - and the "verify_all_columns" option would tell if the other columns would be used for conflict detection, too.)

[regdomaratzki]

The behaviour you are seeing is the default behaviour when no conflict detection scripts exist. Last one in wins. However, it sounds like you want to use column level conflict detection.

From the documentation :

The upload_fetch_column_conflict event is the same as upload_fetch, except that with it the MobiLink server only detects a conflict for a row when the same column was updated on the remote database and the consolidated database since the last synchronization. Different users can update the same row without generating a conflict, as long as they don't update the same column. The upload_fetch_column_conflict event can only be applied to synchronization tables that have no BLOBs.

When using an upload_fetch_column_conflict script and no conflict is detected, the row values passed to your upload_update script come from either the remote database's upload or the current consolidated values from your upload_fetch_column_conflict script. The remote database's value is used for columns that were updated on the remote database, otherwise the current consolidated value is used. In other words, only the columns that were updated on the remote database are updated in the consolidated.

Simple repro : Repro

[Former Member]

Reg's approach would be best to minimize conflicts, but conflicts may still occur, eg. if both users update the same row. The out-of-box behaviour of "last in wins" may be good enough for you, but it might not. Your application scripts will need to either ignore one of the updates, blend them, or log the conflict somewhere.

[regdomaratzki]

Attached a simple repro. I don't actually do any conflict resolution (you can if it's detected and you want to) in the scripts, but the sample shows that the existence of the upload_fetch_column_conflict event causes MobiLink to only insert columns that have changed from the remote into the upload_update event, and other parameters are from the values fetched from the consolidated.

[regdomaratzki]

Russ makes a good point.

I was answering (and provided a sample) on the assumption that since you're not currently doing conflict resolution, that would be true going forward. You know what happens when you assume....

[Former Member]

Note that if you use MobiLink part of Sybase Central to create a synchronization model, you can choose the type of conflict detection and resolution, and the generated synchronizations scripts will reflect your choices. You can use the Events tab to see the generated synchronization scripts.

[Former Member]

FYI, using upload_fetch or upload_fetch_column_conflict scripts to detect conflicts is effective but has the performance drawback of fetching the existing row for each uploaded update row.

For version 12 and earlier, the synchronization model generated SQL for detecting conflicts used that technique.

A better performing technique is do the conflict detection and resolution within your upload_update script. FYI, for the next major version, currently in beta testing, the synchronization model generated SQL has been changed to use this technique.

[Former Member]

Better link for beta announcement.

[ArthurHCH]

I did some test and upload_fetch would be the way to go as long varchar in SQL Anywhere seams to be treated as blob