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.

asked 10 Dec '14, 02:37

OskarEmil's gravatar image

OskarEmil
431141831
accept rate: 50%

edited 12 Dec '14, 03:17

1

What order of operations does the ML server log show?

That being asked, I agree with your expectation, as -tu is declared as following (to cite from the -v12 docs):

When you use -tu, the order of transactions on the remote database is always preserved on the consolidated database.

While the order of operations within one transaction is not necessarily preserved (and several changes on the same row might be coalesced), for your case with two different transactions the order should be preserved - IMHO, that is.

(10 Dec '14, 05:11) Volker Barth

Updated the OP with output from ML server log.

(12 Dec '14, 03:14) OskarEmil
Replies hidden

Hm, that output does not show enough details, I guess it would be necessary to log the invoked scripts and the contents of the uploaded rows (i.e. mlsrv -vins at least...). Of course, that's a kind of post-mortem comment here...

Apparently, you could use a test setup with full logging to clarify whether the -tu option works as expected in your environment.

(12 Dec '14, 03:50) Volker Barth

I'm going to try and reproduce this myself. Could you post the schema of the Foo table, including whatever unique indexes you have on the table?

(12 Dec '14, 13:55) Reg Domaratzki
Replies hidden

I can only reproduce this problem (using my own schema) if -tu is NOT used on the dbmlsync command line. Are you 100% sure that when you ran dbmlsync and it scanned log operations 1036360017 through 1036362683 that you ran with -tu?

I noticed that you posted a part of the ML log. Would you be able to post the ML Log(s) so we can see all of synch <397250>? From there, we should be able to tell whether -tu was used on dbmlsync for this synch.

You can copy the log to location specified by the link below instead of posting it to the forum if you'd like.

https://mdocs.sap.com/mcm/public/v1/open?shr=DQhuFi_VKYtcZSq2syplvzyNPurWtcRJhXDg29sz3Mg

You can also e-mail it to me directly using firstname.lastname@sap.com, or post it to the link in a password protected ZIP file and then e-mail me the password.

Reg

(12 Dec '14, 14:33) Reg Domaratzki

Just a few observations that may help:

1 - You are not getting a primary key error but a unique index error. This may mean we are note talking about the same row at all but an index on (or includes) non-primary key columns. The error for a duplication insert error is -193 error but you are getting a -196 error; the latter can involve different row ids. As such maybe the delete did happen, earlier.

2 - You may not be logging the delete for synchronization. This can be due to use of the Stop Synchronization Delete statement.

3 - There may be other reasons for the delete not getting logged to be synchronized. I would recommend investigating the transaction logs with dbtran -sr and seeing how that delete looks in that output.

and finally 4 - Any of the above could make it appear that the operations are out of order when in fact they may not be. Do we even know that the delete is included in the upload? Running dbmlsync -v+ may show if that is really the case.

Either way Reg and everyone else on this thread is capable of helping here once more information is captured and the evidence is posted.

Best of luck.

(12 Dec '14, 17:09) Nick Elson S...

I guess it should also be noted that reuse of a primary key like this is a bad practice with replication or synchronization.

And ... after further thought ... 5 - Deletes at remotes do not have to translate into deletes at the consolidated RDBMS. That can be desirable if the delete just wants to remove the row from the remote device to help conserve space. This can be accomplished through coding in your upload_delete script and could cause a primary key violation in the scenario you describe.

(12 Dec '14, 17:33) Nick Elson S...
showing 5 of 7 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×266

question asked: 10 Dec '14, 02:37

question was seen: 470 times

last updated: 12 Dec '14, 17:33