Is Mobilink (dbmlsync) Case sensitive?

[Baron]

I have such a table with bidirectional synchronisation (using shadow tables) (MLSRV17.exe & DBMLSYNC.exe)

create or replace table mytable(
col1 varchar(10),
col2 int,
primary key ("col1")
);

I have problem when executing such a statement on Cons.:

update mytable set col1 = 'value1', col2 = 10 where col1 = 'Value1';

The row was already existing in both databases (with col1 = 'Value1', the above transaction will be sent to the cons. in this way

insert into mytable on existing update values ('value1', 10);

which will in turn be executed in this way:

update mytable set col1 = 'value1', col2 = 10 where col1 = 'Value1';

At the end I get the following error: SQL statement failed: (-780) Cannot update primary key for table 'mytable' involved in a publication

The question is, how can I enforce dbmlsync to work case insensitive?

[VolkerBarth]

Is your database itself case insensitive?

IMHO, that error is probably a general protection to prevent PK updates in a MobiLink setup as PKs are obviously needed to identify rows in a MobiLink setup.

If the database is case-insensitive, you might try the "prevent_article_pkey_update" option to allow PK updates as long as you "just" change the case without changing the "identity". However, that should be tested very carefully in a test setup...

(Using character data as PKs has its drawbacks.)

[Baron]

The database itself is case insensitive.

resetting the option "prevent_article_pkey_update" was the only way for me to find the error (in my test setup), I cant risk and reset it in the prod.

Given the facts in your answer I can imagine that the only solution to overcome such situations is to have an "before update trigger" which supresses the "just changing the case" of the primary key, so in my case it will prevent updating col1 from Value1 to value1

[Baron]

BTW, it is not related to Mobilink or dbmlsync.exe! I tried to execute the update statement in the Remote database (from dbisql), and got the same error message:

In other words the protection mechanism of "prevent_article_pkey_update" is case sensitive, which fires only on the remote side but not on the cons. side (since the publication is only on the client side)

[VolkerBarth]

Well, if it fires on a table that is part of an published article, I'd think it IS related to MobiLink/SQL Remote as only those setups have published articles...

---

FWIW, that won't help you but keeping primary keys unmodified IS a strict MobiLink requirement, see here:

To quote:

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:

• Every table that is to be synchronized must have a primary key.
• Never update the values of primary keys in synchronized tables.
• Primary keys in synchronized tables must be unique across all synchronized databases.

Funfact: The last sentence is missing in the current documentation, I took that from the 17.0.4 docs...

Therefore I guess your idea to correct such changes on the consolidated database via a before update trigger that would restore the original case could be a workaorund.

[VolkerBarth]

@Reg: I will stop posting here now;)

[chris_keating]

Any attempt to update a primary key of a table, on a remote,  that is in a publication will report the error cited.  You did not see this on the consolidated as it does not have a publication with that table. And note that primary key will be updated on the consolidated as as long as the consolidated DBMS permits it, 

The issue you are tripping over has nothing to do with case sensitivity in MobiLink but rather the behaviour of char type values in SQL Anywhere. SQL Anywhere stores the character string supplied regardless of case sensitivity. So, if you supply value, it stores value. If you then update to VALUE, the column is changed to VALUE. 

In your example, the update sets col1 to 'value1' resulting in a change in the primary key value from 'Value1' to 'value1'.  That change must go to the remote to be applied in order to keep the databases in sync but it cannot be applied because the column is a primary key.

If this was an accidental UPDATE that included the the primary key column, you could perform another update the returns the primary key column to the same value as on the remote.  This will result in the update as not affecting the primary key.  Otherwise, I would want to look for a way to prevent such updates.   

I have a couple of best practices:

• Avoid using char types as a primary key.  
• Avoid updating primary keys i.e., never set a PK column in a UPDATE, even outside of a MobiLink context. 
• Never update a primary key for any table that is involved in synchronization.

[timcheshire]

Have you tried:

update mytable set col1 = 'value1', col2 = 10 where col1 = 'Value1' and lcase(col1) <> lcase('Value1');

[Baron]

I can't do this:

1- On the Cons. it is the user who executes the update statement, and the database allows him to do so, I can only avoid this from happening via a trigger.

2- On the Remote it is the dbmlsync.exe who executes the update statement, so I can do nothing.