SQL Anywhere Feedback - MobiLink workaround for foreign key cycles

Author

MobiLink workaround for foreign key cycles

Breck Carter [TeamSybase]

2005-07-07, 9:23 am

Please provide a mechanism whereby the correct order of applying
uploaded inserts, updates and deletes involving a foreign key cycle
can be specified.

Current the MobiLink *client* gives a warning message (I can't imagine
a worse place to put a warning about a problem affecting the
*server*):

I. 04/14 15:44:08. Cycle of foreign key references found. Cannot
guarantee referential integrity during upload.
I. 04/14 15:44:08. Tables involved in cycle:

Foreign key cycles may be rare but they are VALID and IMPORTANT, and a
correct order of applying changes ALWAYS EXISTS even if MobiLink is
unable to determine it. That's OK... we (the grunts) know what the
order is, and we are happy to provide the information. BUT WE CANNOT.

Previous experience indicates that TableOrder is REALLY not up to the
task (see earlier rant below).

THIS posting is about yet another production environment, that has had
a single simple cycle for a year that is just NOW starting to cause
problems after an upgrade from 9.0.0 to 9.0.2.

(Have *you* tried to convince an Oracle DBA to drop a constraint
lately? Well, let's just say the committee meetings and discussions
and memos take longer than writing *this* posting.)

Breck Has Never Exaggerated In His Entire Life :)

===== earlier posting, Mar 10 in MobiLink newsgroup...

AFAIK it is the existence of several complex foreign key cycles in
this 400+ table database that is causing MobiLink trouble, not CHECK
ON COMMIT.

To be fair, dbmlsync IS issuing a warning message which I did not
notice at first...

=====
I. 03/09 17:37:02. Cycle of foreign key references found. Cannot
guarantee referential integrity during upload.
=====

I then tried to use the TableOrder option. Two new problems arose:
First of all, you have to specify ALL the tables, and in this case the
string becomes 12K long. Let's just say it took a few minutes to come
up with the value :)... plus you can't put it in the database unless
you make the page size 16K, so it has to go into a dbmlsync @option
file.

Second, if the specified TableOrder string disagrees with the RI
structure as MobiLink sees it, you get this Catch 22 error message:

=====
E. 03/09 16:01:27. TableOrder option violates RI constraints. Table
'xxx' must come before table 'yyy'.
=====

It's Catch 22 because MobiLink is getting the order WRONG, but won't
accept MY TableOrder (which came from a query on SYSFOREIGNKEY). My
TableOrder might not be right either, but it's no worse than
MobiLink's guess.

I spent several fruitless hours manually rearranging TableOrder to
address MobiLink's endless whining... I don't think it is possible in
this case to get a TableOrder that (a) MobiLink accepts and (b)
actually works on the consolidated side.

Sometimes, you just have to admit defeat...

Workaround: Drop all foreign key relationships from the consolidated
database. In *this* case (an upload-only consolidated) it is barely
acceptable... more acceptable than breaking the cycles on the remote
side.

It sure ain't a general-purpose workaround :)... suggestions are
welcome.

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Breck Carter [TeamSybase]

2005-07-19, 9:23 am

Another posting of (I think) the same problem...

=====
From: "Tim Irwin" <nospam_tirwin@twr.org>
Newsgroups: sybase.public.sqlanywhere.mobilink
Subject: upload order not correct
Date: 18 Jul 2005 11:25:27 -0700
=====

Breck
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Graham Hurst

2005-07-26, 8:23 pm

Hi Breck,

I passed your concerns around earlier, and got this in response (sorry
for not posting it earlier):

In Jasper there is a new extended option that allows users to disable
our checking when they use the table order extended options. This will
allow a user to enter any table order he wants even if it violates the
fkey constraints on the remote.

If you have a lot of tables, this procedure can simplify generating the
table order:

1) Create a remote database and attempt to sync it using full verbosity
(-v+) and without specifying any table order extended option. The sync
may fail but as long as it attempts to upload that is not a problem.

2)Look at the dbmlsync log. You will find a line like the following:

I. 07/15 17:15:01. Table Upload Order:
tablesort_T9,tableso
rt_T1,tablesort_T2,t
ablesort_T3,tablesor
t_T4,tablesort_T5

This line lists all the tables being synchronized in the order in which
dbmlsync chose to try to upload them. If you want some different order
use this string as the starting point for creating your own tableorder
setting. If you only want to reorder a small subset of the tables then
it use cut and paste in a text editor.

Cheers,

Graham

Sponsored Links