on 2014 Dec 10 2:37 AM
Using Sql Anywere 12 and Mobilink 12.0.1.3942
A kind of follow-up from this thread, really: MobiLink problem with deletes after inserts
I supposed -tu argument when running dbmlsync should process uploads to the Consolidated database in the same order as added to the Remote database ?
However, today I had this problem where a MobiLink client was trying to upload a row with the same unique index as an existing row in the consolidated database.
Given these tables where FooPrimaryKey is a global autoincrement primary key and FooUniqueIndex is an unique index
Foo (Consolidated) ------------------------------ FooPrimaryKey | FooUniqueIndex --------------|--------------- 1 | 'Bar' Foo (Remote, after INSERT and DELETE) ------------------------------ FooPrimaryKey | FooUniqueIndex --------------|--------------- 2 | 'Bar'
This is the transaction log of the remote database with the pending changes to upload to the consolidated database;
BEGIN TRANSACTION go --DELETE-1007-01036360017 DELETE FROM dba.Foo WHERE FooPrimaryKey=1 go --COMMIT-1007-01036361170 COMMIT WORK go
And further down:
--BEGIN TRANSACTION-1007-01036362680 BEGIN TRANSACTION go --INSERT-1007-01036362683 INSERT INTO dba.Foo(FooUniqueIndex) VALUES ('Bar') go --COMMIT-1007-01036362937 COMMIT WORK go
When uploading to the consolidated database with -tu option set, shouldn't MobiLink delete Foo(1) before inserting Foo(2) ? In my case I get a duplicate index error, Foo(2) is inserted before Foo(1) is deleted.
This is the output I got from the MobiLink server log:
E. 2014-12-09 13:04:12. <397250> [-10002] Consolidated database server or ODBC error: ODBC: [Sybase][ODBC Driver][SQL Anywhere]Index 'ix_Foo_id' for table 'Foo' would not be unique (ODBC State = 23000, Native error code = -196) E. 2014-12-09 13:04:12. <397250> [-10072] Unable to insert into table 'Foo' using upload_insert I. 2014-12-09 13:04:12. <397250> Error Context: I. 2014-12-09 13:04:12. <397250> Remote ID: d1551025-d8d6-4634-885f-0018196f84d5 I. 2014-12-09 13:04:12. <397250> User Name: 307 I. 2014-12-09 13:04:12. <397250> Modified User Name: 307 I. 2014-12-09 13:04:12. <397250> Transaction: upload I. 2014-12-09 13:04:12. <397250> Table Name: Foo I. 2014-12-09 13:04:12. <397250> Insert Row:
Since this was from a system in production I had to fix it manually by deleting the row in the consolidated database myself, so I am not able to reproduce any more detailed logs atm.
User | Count |
---|---|
69 | |
9 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.