I'm using Sql Anywhere 16, with MobiLink. Both consolidated and remote are SQL Anywhere. I'm using a timestamp-based download technique.
1. Users want to change the case of text in Primary Key columns
2. prevent_article_pkey_update setting won't allow it
3. So users delete, then reinsert with changed case
4. MobiLink cannot handle this scenario
- Change is uploaded as a delete and an insert
- Upload_Insert is processed before Upload_Delete
- Insert fails with PK violation
- Delete succeeds
- Delete is downloaded
- Both copies of row are now gone
If the user does the insert with identical case in the primary key column, the delete/insert get uploaded as an update. Since no data actually changes, there is no error. But if the primary key column is changed from lowercase to uppercase, the above problem occurs.
I know that the best practice is to use global autoincrement primary keys, but short of changing my table structure in a production database, what can I do to get around this problem?
Is the case sensitivity of the consolidated and remote database the same? I suspect not, and I think that might be the real source of your problem. If you delete pkey "X" and then insert pkey "X" at the remote and then run dbmlsync, it should be sent as an update. The fact that it is sent as an insert and delete tells me that the remote database is case sensitive. The fact that the insert fails with a PK violation at the consolidated database tells me that the consolidated database is NOT case sensitive.