MobiLink - updating primary key in remote database

[Former Member]

We are using SQL Anywhere v12.01.3554.

What impact does changing primary key on a record have when done on remote side?

Here is our situation: 1. Record inserted into table.

INSERT INTO DBA.pi_count(company,brand,size,ticket,date_entered, time_entered,user_name,computed_cases) VALUES (30,0,0,176468,'2014-09-01 00:00','19:48:29.0','Dustyg',0)

The values for brand, size, computed cases are not known at time of insert. Primary key in this example is company, brand, size, ticket.

1. Brand is then updated.

UPDATE DBA.pi_count SET brand=489 WHERE company=30 AND brand=0 AND size=0 AND ticket=176468

1. Size, computed cases is then updated. UPDATE DBA.pi_count SET size=346, computed_cases = 100 WHERE company=30 AND brand=489 AND size=0 AND ticket=176468

2. Data is then synchronized to consolidated database. What we end up with in consolidated is the record prior to update of 3 above.

This is not what I was expecting. Can anyone provide an explantion as to what is going on here?

I am guessing that my issue is updating the primary key and this is frowned upon in a MobiLink setup so I will likely need to re-design my table to avoid doing this in the future. This is the only table in our setup where we update the primary key after initial setup.

Thanks for any help.

Brian

[Former Member]

One thing that is confusing for me is why does consolidated have inserted row with brand updated (combination of initial insert statement and brand update). All updates after that are not synched.

[Breck_Carter]

Caveat: I have not explored what actually happens when a remote primary key value is changed.

[guesswork] The MobiLink synchronization process uses the primary key to identify each row uniquely. The upload process may interpret a new primary key value as an insert when determining what to upload, hence the new row on the consolidated. You may end up with an old unchanging row and a new row subject to future changes coming from the remote, on the consolidated. I will put this on my research to do list, right after "Achieve World Peace" 🙂 [/guesswork]

[VolkerBarth]

Your expectation is correct, just have a look at these generell MobiLink development tips in the docs

MobiLink and primary keys

In a synchronization system, the primary key is the only way to identify the same row in different databases (remote and consolidated) and the only way to detect conflicts. Therefore, MobiLink applications must adhere to the following rules:
1. Every table that is to be synchronized must have a primary key.
2. Never update the values of primary keys in synchronized tables.
3. Primary keys in synchronized tables must be unique across all synchronized databases.

[Breck_Carter]

...it doesn't even mention "unique constraint" which is OK for a foreign key, it's gotta be a primary key. MobiLink is so strict...

[Former Member]

...but it's for your own good, even if it doesn't feel like it at the time. 😉

[VolkerBarth]

Just to add:

I am guessing that my issue is updating the primary key and this is frowned upon in a MobiLink setup so I will likely need to re-design my table to avoid doing this in the future. This is the only table in our setup where we update the primary key after initial setup.

From MobiLink's point of view, a surrogate PK with no real meaning would certainly help to avoid the need to update a PK lateron. However, you state:

"Primary key in this example is company, brand, size, ticket."

So if there's a need that these values are unique (even when not used as PK and possibly not declared as UNIQUE KEYS) and will be filled lateron - how do you make sure no two remotes can set the same unique values for different rows?

I'm just hinting at the fact that modifying PKs in a ML setup is no good but guaranteeing non-PK UNIQUE constraints in a distributed system is not that easy, either... - it might require some kind of conflict resolution, methinks.

[Breck_Carter]

> guaranteeing non-PK UNIQUE constraints in a distributed system is not that easy, either

Indeed... it is the kind of thing that can cause an upload to fail, completely disabling further synchronization for the offending remote database. One workaround is to omit the UNIQUE constraint from the consolidated database. The real solution is to sit down and think carefully about the database design, away from distractions... it's not the job for a committee, the committee isn't there at 4AM when MobiLink stops working.