SQL Anywhere Mobile - Does CHECK ON COMMIT break upload order?

Author

Does CHECK ON COMMIT break upload order?

Breck Carter [TeamSybase]

2005-03-30, 9:42 am

ASA 9.0.2.2551

Can the presence of the CHECK ON COMMIT clause in a remote ASA
database foreign key definition cause inserted child rows to be
uploaded before inserted parent rows, thus causing a foreign key
violation on a consolidated database that does not have the CHECK ON
COMMIT clause (e.g., any MSS database)?

Alas, that's what I'm seeing, as far as I can tell.

IMO dbmlsync should not pay attention to CHECK ON COMMIT since it has
no idea what the rules are on the consolidated.

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

David Fishburn

2005-03-30, 9:42 am

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
news:t0bs21hjeaqohl2
44f4ml8dohi9d6g391d@
4ax.com of
sybase.public.sqlanywhere.mobilink:

BC> ASA 9.0.2.2551
BC>
BC> Can the presence of the CHECK ON COMMIT clause in a remote ASA
BC> database foreign key definition cause inserted child rows to be
BC> uploaded before inserted parent rows, thus causing a foreign key
BC> violation on a consolidated database that does not have the CHECK ON
BC> COMMIT clause (e.g., any MSS database)?

Can you supply a simple reproducable of what you are seeing.
A 3-4 table example.

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Breck Carter [TeamSybase]

2005-03-30, 9:43 am

I'm working on a small reproducible, not done yet. It may not be a
CHECK ON COMMIT issue, but something else.

Here is some of the dbmlsync output from the real database; it shows
that the "Table Upload Order:" list is truncated in the log, and that
a row in the child table ReportInvocations is being uploaded before
the parent row in Reports. AFAIK these "Upload operations - Insert
row" displays are usually in the correct FK order.

FWIW the ReportInvocations insert fails when it hits dbmlsrv9.

Breck

I. 03/09 09:30:27. Table Upload Order:
NotifyDestinations,N
otifyMessages,NotifM
ailServers,ad_dra_hi
st_by_quarter,ad_dra
_hist_by_month,ad_dr
a_hist_by_week,ad_dr
a_hist_by_day,ad_dra
_hist_by_hour,ad_dra
_hist,ad_dra_last,ad
_dns_hist_by_quarter
,ad_dns_hist_by_mont
h,ad_dns_hist_b
I. 03/09 09:30:27. Uploading table operations
....
I. 03/09 09:30:28. Upload operations on table 'ReportInvocations'
I. 03/09 09:30:28. Insert row:
I. 03/09 09:30:28. <id>: 0
I. 03/09 09:30:28. <name>: xxx
I. 03/09 09:30:28. <sourceType>: xxx
I. 03/09 09:30:28. <scope>: xxx
I. 03/09 09:30:28. <condition>: xxx
I. 03/09 09:30:28. <targetReportId>: 55
I. 03/09 09:30:28. <subTitleTemplate>: xxx
....
I. 03/09 09:30:28. # rows inserted in table ReportInvocations : 241
I. 03/09 09:30:28. # rows deleted in table ReportInvocations : 0
I. 03/09 09:30:28. # rows updated in table ReportInvocations : 0
I. 03/09 09:30:28. Upload operations on table 'Reports'
I. 03/09 09:30:28. Insert row:
I. 03/09 09:30:28. <id>: 0
....
I. 03/09 09:30:28. Insert row:
I. 03/09 09:30:28. <id>: 55

CREATE TABLE ReportInvocations ( -- 241 rows
id /* PK */ INTEGER NOT NULL,
name VARCHAR ( 32 ) NULL,
sourceType VARCHAR ( 40 ) NULL,
scope VARCHAR ( 64 ) NULL,
condition VARCHAR ( 64 ) NULL,
targetReportId /* FK */ INTEGER NULL,
subTitleTemplate VARCHAR ( 64 ) NULL,
CONSTRAINT pk_ReportInvocation PRIMARY KEY (
id ) );
ALTER TABLE ReportInvocations ADD CONSTRAINT
fk_ReportInvocations
2Reports_targetRepor
t FOREIGN KEY (
targetReportId )
REFERENCES Reports (
id )
ON UPDATE RESTRICT ON DELETE RESTRICT CHECK ON COMMIT;

On 8 Mar 2005 20:01:20 -0800, David Fishburn
<fishburn_spam@off.ianywhere.com> wrote:

>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
> news:t0bs21hjeaqohl2
44f4ml8dohi9d6g391d@
4ax.com of
>sybase.public.sqlanywhere.mobilink:
>
>BC> ASA 9.0.2.2551
>BC>
>BC> Can the presence of the CHECK ON COMMIT clause in a remote ASA
>BC> database foreign key definition cause inserted child rows to be
>BC> uploaded before inserted parent rows, thus causing a foreign key
>BC> violation on a consolidated database that does not have the CHECK ON
>BC> COMMIT clause (e.g., any MSS database)?
>
>Can you supply a simple reproducable of what you are seeing.
>A 3-4 table example.

--
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-03-30, 9:43 am

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

On 8 Mar 2005 20:01:20 -0800, David Fishburn
<fishburn_spam@off.ianywhere.com> wrote:

>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
> news:t0bs21hjeaqohl2
44f4ml8dohi9d6g391d@
4ax.com of
>sybase.public.sqlanywhere.mobilink:
>
>BC> ASA 9.0.2.2551
>BC>
>BC> Can the presence of the CHECK ON COMMIT clause in a remote ASA
>BC> database foreign key definition cause inserted child rows to be
>BC> uploaded before inserted parent rows, thus causing a foreign key
>BC> violation on a consolidated database that does not have the CHECK ON
>BC> COMMIT clause (e.g., any MSS database)?
>
>Can you supply a simple reproducable of what you are seeing.
>A 3-4 table example.

--
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

Sponsored Links