|
Hi, I have a local database(sql anywhere 12) and a consolidated database (Oracle) I use mobilink to synchronize data. We have a strange situation where 1 local database keeps getting some records deleted after inserting them (with mobilink synchronization) (the records should not be deleted since they are available on the consolidated database) I know the records are deleted since I check the transaction log of the local database and I see them getting inserted and a litle later they are deleted.... Now, I have 2 questions... Q1: Has anyone seen anything like this before??? Q2: After all the inserts there is a internal sored procedure called => and right after this procedure, a few delete statements are called and some of the inserted records are deleted. Now, I am wondering if the deletes are part of the synchronization or are they performed After the synchronization? (since I would assume that the sa_sync_sub procedure is called after everything is synchronized... or am I completely wrong about this?)... Thank you ! |
|
Do these rows violate referential integrity on the remote? If so, the MobiLink client may be silently removing them. You are right, thanks you for the answer. I tried to execute the insert of the record that was deleted (in table A) and indeed sql anywhere immediately reports that it cannot insert because of a non-existing reference. Unfortunately this leads me to another record (in table B) from a different publication that also has been misteriously deleted after insert. And this record (in Table B) has only 1 foreign key => self referencing to a parent record (recursive reference).... and in this case, when I try to execute the insert statement it just performs perfectly. Some notes: - When I start out with a new remote database all data is downloaded fine. - However (I do not know if this is important), but there is a warning when I synchronize and it says the following: "Cycle of foreign key reference found. Cannot guarantee referential integrity during upload. Tables involved in cycle: Table B"
(06 Mar '15, 09:12)
vdcey
Replies hidden
2
Whenever you see the "Cycle of foreign key reference found" warning, you should to provide an explicit table ordering (TOR) extended option for all of your tables in this publication due to the cycle of foreign keys. Currently, MobiLink can't figure out which tables are parents and which are children, which could result in a foreign key violation when uploading.
(06 Mar '15, 11:32)
Jeff Albion
Thanks for your reply Jeff. Can this also have an effect on the Download of data in to the remote database or is it unrelated?
(06 Mar '15, 16:43)
vdcey
1
Yes, the TOR extended option will have an effect on the download - to cite from the page Jeff has linked to:
As both the upload and download are done in one transaction each, a cyclic reference should be no problem within the download as the RI checking is done at the end on the SA remote site (with option wait_for_commit). IIRC, for the upload, it will depend on the consolidated whether a RI conflict will appear here or not (in case a row is uploaded with a cyclic FK which refers to a row that has not already been uploaded). For example, with a MS SQL Server consolidated, we had to store such conflicting rows (or the conflicting FK value) temporarily out side the "real table" and then apply them to the real table afterwards, i.e. after the whole upload has been applied. In contrast, with a SA consolidated I guess option "wait_for_commit" would prevent the requirement of such a temporary storage.
(07 Mar '15, 14:43)
Volker Barth
Replies hidden
Hi Volker, Thanks for your reply and willingness in sharing your experience. In my case, the publication only really downloads data to the remote and the record that has been deleted did not have a parent (foreign key = null). Would the reasoning not only apply on child records? Note: I do notice though that indeed the order of inserting data in "table B" does not take in account the self-reference. But unfortunately, this is a parent record I'm talking about and I would think this reasoning does not apply :( Any advice is welcome !
(09 Mar '15, 05:24)
vdcey
1
I share your view that a record without a FK to a different record cannot be deleted automatically by ML to prevent RI violations, simply as there cannot be a RI conflict without a child record. What does the ML client log (dbmlsync -o -vr...) show here - does it contain the row in question?
(09 Mar '15, 06:11)
Volker Barth
Hi Volker, to make things more complex.....I cannot reproduce the problem. this happens occasionally on the field. I still have one of the remote databases though and here the record is currently ignored when synchronizing (because of the last-update-time), but I'm pretty sure that If I change this date, the record will be downloaded (since I'm able to insert it manually myself). I am currently thinking of a workaround where I setup a webservice that checks the amount of record right after synchronization... PS. If you think you can figure out what's going on and you are willing to do consultancy, let me know. I've send you an email.
(09 Mar '15, 09:30)
vdcey
1
That's a nice offer but I'm just an inhouse-developer so that's nothing I could provide. There are others here in this forum with much more insight in MobiLink and consulting activities, say Breck himself.
(09 Mar '15, 09:49)
Volker Barth
|
