SQL Anywhere Mobile - Foreign key and automatic delete

Author

Foreign key and automatic delete

flagasse

2006-01-30, 3:24 am

I plan to migrate a sqlremote replication (Asa<->ASE) to a
Mobilink replication.

I have few questions :
I have read in the documentation that if a FK is violated
on the centralised database, an automatic delete occurs in
the distant database. Does it really delete the data from
the distant database ? The data will be definitively lost ?
Is there a way to avoid it ?

The extraction process is the only way to resynchronise
databases after a lost of synchronisation ?

Florent Lagasse

Liam Cavanagh

2006-01-30, 9:27 am

Yes, I believe it does a download delete of the row to the remote.

Could you explain what you would like to happen in this event? I don't
imagine that you would want the row to exist in the remote and not the
consolidated as the database would then be out of sync.

I guess the best thing would be to make sure the fk relationships on the
consolidated match those on the remote so this type of thing does not
happen.

--
Liam Cavanagh
Sr. Product Manager
Certified ASA Developer Version 8
iAnywhere
<flagasse> wrote in message news:43ddda6e.1e6c.1681692777@sybase.com...
>I plan to migrate a sqlremote replication (Asa<->ASE) to a
> Mobilink replication.
>
> I have few questions :
> I have read in the documentation that if a FK is violated
> on the centralised database, an automatic delete occurs in
> the distant database. Does it really delete the data from
> the distant database ? The data will be definitively lost ?
> Is there a way to avoid it ?
>
> The extraction process is the only way to resynchronise
> databases after a lost of synchronisation ?
>
>
> Florent Lagasse

Reg Domaratzki \(iAnywhere Solutions\)

2006-01-30, 11:25 am

FK Question :

Not exactly. Silent RI resolution happens only on the remote database.
When applying the download stream, it's all applied in a single transaction,
with the wait_for_commit option turned ON, so RI errors will only occur on
COMMIT. When an RI error is detected on the remote, dbmlsync will figure
out what rows need to be removed to allow the operation to continue without
failing, remove them, and then try the commit again. Imagine you have a
parent and child table that are perfectly in synch on consolidated and
remote with the following rows:

Parent : Rows 1,2,3
Child : Rows 1,2,3 with each row referencing same row number in parent

Now imagine that row #4 is added to the remote, which references Row#3 in
the parent table. At the same time, row#3 is deleted on the child+parent
tables at the consolidated. When synchronization occurs, an RI violation
will occur when row#4 is inserted, since row#3 no longer exists on the
consolidated. Assuming you are downloading deletes to the remote database
(i.e. you have a download_delete_curs
or for the Parent table), in this same
synchronization, a delete for row#3 on the parent and child table will be
sent to the remote. A delete for row #4 is NOT sent. However, when row#3
on the parent table is deleted, silent RI resolution will occur ON THE
REMOTE, and row#4 in the child table will be removed.

As Liam asked as well, how do you think dbmlsync should handle this
situation? Should the entire download stream be rolled back as a result of
the RI error?

Re-Synch Question :

What do you mean by "after a lost of synchronization"? If a synch fails, it
will be retired again the next time you synchronize.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

<flagasse> wrote in message news:43ddda6e.1e6c.1681692777@sybase.com...
> I plan to migrate a sqlremote replication (Asa<->ASE) to a
> Mobilink replication.
>
> I have few questions :
> I have read in the documentation that if a FK is violated
> on the centralised database, an automatic delete occurs in
> the distant database. Does it really delete the data from
> the distant database ? The data will be definitively lost ?
> Is there a way to avoid it ?
>
> The extraction process is the only way to resynchronise
> databases after a lost of synchronisation ?
>
>
> Florent Lagasse

David Fishburn

2006-02-08, 9:24 am

flagasse wrote in news:43ddda6e.1e6c.1681692777@sybase.com
of sybase.public.sqlanywhere.mobilink:

f> I plan to migrate a sqlremote replication (Asa<->ASE) to a
f> Mobilink replication.
f>
f> I have few questions :
f> I have read in the documentation that if a FK is violated
f> on the centralised database, an automatic delete occurs in
f> the distant database. Does it really delete the data from
f> the distant database ? The data will be definitively lost ?
f> Is there a way to avoid it ?

Yes, there may be a way to avoid it (with version 9.0.2 software).

In the REMOTE database you can create stored procedures with specific
names. dbmlsync will automatically call these scripts at specified
times. One of the stored procedures is:
MobiLink Clients
Dbmlsync Client Event Hooks
sp_hook_dbmlsync_dow
nload_ri_violation

What you may be able to do (untested) is use the RAISERROR statement to
force a SQL error on the connection to FAIL the synchronization. This
will rollback the current download.

Something like:
SET TEMPORARY OPTION on_tsql_error = 'stop';
RAISERROR 32000 'Attempted to silently delete RI violations';
You can get fancier than that by including the table name which can be
looked up in the #hook_dict table.

Another option is to allow the silent deletion to happen but simply LOG
that it has and have this synchronize back to the consolidated.
sp_hook_dbmlsync_dow
nload_log_ri_violati
on

f> The extraction process is the only way to resynchronise
f> databases after a lost of synchronisation ?

Well, that depends on what you really need to do.
For example, if you just want MobiLink to send down previous data, that
is easy. You can modify the last_download timestamp which is passed to
all the download_cursors very easily, ML provides events so you can do
just that.

That way, you can do the following:
1. LOG the RI violation
2. Fix the issue (on the server or client)
3. Override this user's last_download timestamp
4. This will allow you to resend all the data that failed, there by
resynchronizing the remote's data with the data in the consolidated.

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

2006-02-08, 9:24 am

The RAISERROR technique works just fine, and there is no need to
change the setting of ON_TSQL_ERROR from the default.

Breck

On 8 Feb 2006 07:00:13 -0800, David Fishburn
<fishburn_spam@off.ianywhere.com> wrote:

>flagasse wrote in news:43ddda6e.1e6c.1681692777@sybase.com
>of sybase.public.sqlanywhere.mobilink:
>
>f> I plan to migrate a sqlremote replication (Asa<->ASE) to a
>f> Mobilink replication.
>f>
>f> I have few questions :
>f> I have read in the documentation that if a FK is violated
>f> on the centralised database, an automatic delete occurs in
>f> the distant database. Does it really delete the data from
>f> the distant database ? The data will be definitively lost ?
>f> Is there a way to avoid it ?
>
>Yes, there may be a way to avoid it (with version 9.0.2 software).
>
>In the REMOTE database you can create stored procedures with specific
>names. dbmlsync will automatically call these scripts at specified
>times. One of the stored procedures is:
> MobiLink Clients
> Dbmlsync Client Event Hooks
> sp_hook_dbmlsync_dow
nload_ri_violation
>
>What you may be able to do (untested) is use the RAISERROR statement to
>force a SQL error on the connection to FAIL the synchronization. This
>will rollback the current download.
>
>Something like:
> SET TEMPORARY OPTION on_tsql_error = 'stop';
> RAISERROR 32000 'Attempted to silently delete RI violations';
>You can get fancier than that by including the table name which can be
>looked up in the #hook_dict table.
>
>Another option is to allow the silent deletion to happen but simply LOG
>that it has and have this synchronize back to the consolidated.
> sp_hook_dbmlsync_dow
nload_log_ri_violati
on
>
>
>f> The extraction process is the only way to resynchronise
>f> databases after a lost of synchronisation ?
>
>Well, that depends on what you really need to do.
>For example, if you just want MobiLink to send down previous data, that
>is easy. You can modify the last_download timestamp which is passed to
>all the download_cursors very easily, ML provides events so you can do
>just that.
>
>That way, you can do the following:
>1. LOG the RI violation
>2. Fix the issue (on the server or client)
>3. Override this user's last_download timestamp
>4. This will allow you to resend all the data that failed, there by
> resynchronizing the remote's data with the data in the consolidated.

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