SQL Anywhere Mobile - Foreign key and automatic delete : a reason to be afraid of

Author

Foreign key and automatic delete : a reason to be afraid of

flagasse

2006-02-01, 7:24 am

Thanks for your replies.

I understand the situation you have described.

But, in our case, we deal with financial datas.

What ever problem occured in the datas or in the
synchronisation process, we cannot loose our datas.

Weeks ago, we have lost our centralized database (ASE), and
we have restored a previous dump (we had not the choice).

So, some datas in the remote sites are not in the
centralized database. This cause to us FK resolution
problems.
Because for now we use SQLremote, constraints violation are
detected and resolved automaticaly by a group of SQL scripts
and treatments.

Because our system is very big (> 250 remotes sites) we
cannot resynchronise all datas.
So it will always be little de-synchronized datas in our
system.

So, if we do a migration from sqlremote to mobilink, we do
not want to have delete on the new datas.

flagasse

Reg Domaratzki \(iAnywhere Solutions\)

2006-02-01, 9:26 am

In the case you describe, when an "old" consolidated database is brought
back on-line, the progress offsets in the consolidated will not match. When
a remote with a higher progress value than is stored in the consolidated
database attempts to synch, the consolidated will fail the synchronization
and ask for the remote to re-send the data based on the progress value that
currently exists in the consolidated.

I. 02/01 08:23:23. <thread 1.5>: Working on a request
I. 02/01 08:23:23. <1.5> [rem1]: Cached ODBC statement:
SELECT progress FROM ml_subscription WHERE user_id = ?
AND subscription_id = ?
W. 02/01 08:23:23. <1.5> [rem1]: Warning: [10012] The consolidated and
remote databases disagree on when the last synchronization took place. The
remote is being asked to send a new upload that starts at the last known
synchronization point
I. 02/01 08:23:23. <1.5> [rem1]: Progress offsets for the publications that
are explicitly involved in the current synchronization
I. 02/01 08:23:23. <1.5> [rem1]: Subscription id 2: consolidated progress
7015 and remote progress 14242
I. 02/01 08:23:23. <1.5> [rem1]: Synchronization failed
I. 02/01 08:23:23. <thread 1.5>: Working on a request
I. 02/01 08:23:23. <1.5> [rem1]: Cached ODBC statement:
SELECT progress FROM ml_subscription WHERE user_id = ?
AND subscription_id = ?
I. 02/01 08:23:23. <1.5> [rem1]: Progress offsets for the publications that
are explicitly involved in the current synchronization
I. 02/01 08:23:23. <1.5> [rem1]: Subscription id 2: consolidated progress
7015 and remote progress 7015

As you can see, the first synchronization from rem1 had a progress offset
from the remote of 14242, but the consolidated had only received offset
7015. MobiLink asked the remote to resend from offset 7015, and the next
synchronization (all in the same synchronization session BTW, I didn't have
to start dbmlsync twice) sent an upload starting at this offset.

Note that in order for this to work, the ASA transaction log on the remote
must have access to log offsets 7015 through 14242. Since the remote will
have received a confirmation from the consolidated database before the crash
for offset 14242, it's possible that this transaction log has already been
deleted by dbmlsync if you have set delete_old_logs to 'on'. If you PLAN on
recovering old consolidated databases, you'll need to manage your own
transaction logs on the remote. Note that this is no different than the
situation you currently have with SQL Remote at the remote sites.

--
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:43e08fd1.4b24.1681692777@sybase.com...
> Thanks for your replies.
>
> I understand the situation you have described.
>
> But, in our case, we deal with financial datas.
>
> What ever problem occured in the datas or in the
> synchronisation process, we cannot loose our datas.
>
> Weeks ago, we have lost our centralized database (ASE), and
> we have restored a previous dump (we had not the choice).
>
> So, some datas in the remote sites are not in the
> centralized database. This cause to us FK resolution
> problems.
> Because for now we use SQLremote, constraints violation are
> detected and resolved automaticaly by a group of SQL scripts
> and treatments.
>
> Because our system is very big (> 250 remotes sites) we
> cannot resynchronise all datas.
> So it will always be little de-synchronized datas in our
> system.
>
> So, if we do a migration from sqlremote to mobilink, we do
> not want to have delete on the new datas.
>
>
> flagasse

flagasse

2006-02-01, 11:25 am

Thanks for your reply.

I agree that if there is no transaction log that contains
the gap, the problem is the same if we use SQLremote or
Mobilink.

There is 2 simple solutions to restart the SQLremote
replication :
1/ re-extract the remote database
2/ reset all the offsets

In our case re-extract is not so easy : our network is a
very low bandwith one, and the remote database must not
stop.

With SQLremote, ater a new offsets configuration, the
replication start again, with a gap in the datas.

Then the difference is here : with SQL remote, no data will
be deleted if a foreign key constraint violation occured.

So the question is : In theory is it possible to implement a
treatment that do the following :
- if a FK viloation occurs : mark it and rollback the
entire transaction with no delete in the remote site
- get the missing data from the remote site and apply it
- do again the synchronisation.

Do you think it's possible ?
This can auto-correct missing datas.

flagasse

Reg Domaratzki \(iAnywhere Solutions\)

2006-02-01, 1:25 pm

In the situation where the ASA transaction log is not available for a resend
after the consolidated database has been restored, no synchronization will
be able to take place (since the remote would not be able to send an upload
stream to MobiLink), no download will occur, and therefore no deletes will
occur on the remote database, since the silent RI resolution occurs when the
download stream is applied. Once this state has been detected at the
consolidated (you'll see a progress offset mismatch error followed by NO
second synchronization attempt), you can then "get the missing data from the
remote site and apply it to the consolidated", then convince the MobiLink
server that it has already received the given log offset values from the
remote. If you keep verbose MobiLink logs showing the data being uploaded
from the remotes (similar to running dbremote -v), this could all be done at
the consolidated site without ever needing to contact the remote user.

I can't imagine any situation in a properly setup MobiLink system (i.e.
there are no problems in your synchronization scripts that would cause FK
violations) where rows would be silently deleted at the remote, or where a
failed recovery on either side would result in rows being deleted. If
someone else can point one out, I'd be happy to hear about it, and I'll help
to figure out how to avoid it.

IF cons_progress > remote_progress THEN
Data exists at cons that does not exist at the remote
ML will detect mismatch
ML will ask for resend using cons_progress
IF cons_progress offset is valid at remote THEN
Synch continues from cons_progress
Extra data on cons downloaded to remote
ELSE
Likely means failed recovery on remote
No Synch occurs -> no silent deletes
Modify cons_progress to match remote_progress
Extra data on cons downloaded to remote next sync
END IF
ELSE // remote_progress > cons_progress
Data exists in remote that does not exist at cons
ML will detect mismatch
ML will ask for resend using cons_progress
IF cons_progress offset is valid at remote THEN
Remote resends from cons_progress
Extra data on remote uploaded to cons
ELSE
Likely means failed recovery on cons
No Synch occurs -> no silent deletes
Manually add data from remote to consolidated
Modify cons_progress to match remote_progress
Extra data on remote uploaded to cons next sync
END IF
END IF

Modifying progress values on the consolidated would only EVER need to be
done when you are faced with a failed recovery on the remote or consolidated
and are forced back to an older version of that database. You should never
need to modify these values yourself in normal day-to-day operations.

--
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/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

<flagasse> wrote in message news:43e0f4c3.48e5.1681692777@sybase.com...
> Thanks for your reply.
>
> I agree that if there is no transaction log that contains
> the gap, the problem is the same if we use SQLremote or
> Mobilink.
>
> There is 2 simple solutions to restart the SQLremote
> replication :
> 1/ re-extract the remote database
> 2/ reset all the offsets
>
> In our case re-extract is not so easy : our network is a
> very low bandwith one, and the remote database must not
> stop.
>
> With SQLremote, ater a new offsets configuration, the
> replication start again, with a gap in the datas.
>
> Then the difference is here : with SQL remote, no data will
> be deleted if a foreign key constraint violation occured.
>
> So the question is : In theory is it possible to implement a
> treatment that do the following :
> - if a FK viloation occurs : mark it and rollback the
> entire transaction with no delete in the remote site
> - get the missing data from the remote site and apply it
> - do again the synchronisation.
>
> Do you think it's possible ?
> This can auto-correct missing datas.
>
>
> flagasse

flagasse

2006-02-02, 3:25 am

Thanks,

To resume, The silent delete occurs in this case :
table TA (CA )
table TB (CA, CB)
FK TB(CA) => TA(CA)

1. manual : insert 1 into TA(CA) on consolidate
2. replication : insert 1 into TA(CA) on remote
3. manual : insert 1,1 into TB(CA,CB) on remote
4. manual : delete 1 from TA(CA) on consolidate
5. replication : try to insert 1,1 into TB(CA,CB) on
colidate : FK violation
6. replication : delete 1,1 from TB(CA, CB), delete 1 from
TA(CA) on remote

But now I tell to you why I am afraid :
We really have some rows that are not "perfectly"
synchronise in our system, this is not a conceptual case.
We have : TA(1), TB(1,1) on remote and TA(1) on
consolidate, and no further replication will insert TB(1,1)
on consolidate.

We have 250 remote sites (1.5 GB).
We have a "big" consolidated database (120GB).
We have a low bandwith network (6.4kB/s, 1.5GB takes more
than 2 days to be transfered)
The remote sites can only be stop on sundays on during the
night.
Because we have financial datas on remote, we cannot
directly re-extract remote datas from consolidate.

So, silent deletes have to be avoid.
And I see 5 solutions to avoid them :
keep using sqlremote (I think the worse solution)
use mobilink with perfectly synchronised datas (any ideas
?)
use mobilink with no FK on consolidate (!!!!!!!)
use mobilink combine with a system that can detect and
correct FK violation (the ideal solution)
use a non Sybase product (they are numerous)

flagasse

Reg Domaratzki \(iAnywhere Solutions\)

2006-02-02, 7:24 am

How do currently handle the problem of distributed deletes using SQL Remote?

--
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/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

<flagasse> wrote in message news:43e1d138.51a8.1681692777@sybase.com...
> Thanks,
>
> To resume, The silent delete occurs in this case :
> table TA (CA )
> table TB (CA, CB)
> FK TB(CA) => TA(CA)
>
> 1. manual : insert 1 into TA(CA) on consolidate
> 2. replication : insert 1 into TA(CA) on remote
> 3. manual : insert 1,1 into TB(CA,CB) on remote
> 4. manual : delete 1 from TA(CA) on consolidate
> 5. replication : try to insert 1,1 into TB(CA,CB) on
> colidate : FK violation
> 6. replication : delete 1,1 from TB(CA, CB), delete 1 from
> TA(CA) on remote
>
> But now I tell to you why I am afraid :
> We really have some rows that are not "perfectly"
> synchronise in our system, this is not a conceptual case.
> We have : TA(1), TB(1,1) on remote and TA(1) on
> consolidate, and no further replication will insert TB(1,1)
> on consolidate.
>
> We have 250 remote sites (1.5 GB).
> We have a "big" consolidated database (120GB).
> We have a low bandwith network (6.4kB/s, 1.5GB takes more
> than 2 days to be transfered)
> The remote sites can only be stop on sundays on during the
> night.
> Because we have financial datas on remote, we cannot
> directly re-extract remote datas from consolidate.
>
>
> So, silent deletes have to be avoid.
> And I see 5 solutions to avoid them :
> keep using sqlremote (I think the worse solution)
> use mobilink with perfectly synchronised datas (any ideas
> ?)
> use mobilink with no FK on consolidate (!!!!!!!)
> use mobilink combine with a system that can detect and
> correct FK violation (the ideal solution)
> use a non Sybase product (they are numerous)
>
> flagasse

Reg Domaratzki \(iAnywhere Solutions\)

2006-02-02, 9:28 am

> But now I tell to you why I am afraid :
> We really have some rows that are not "perfectly"
> synchronize in our system, this is not a conceptual case.
> We have : TA(1), TB(1,1) on remote and TA(1) on
> consolidate, and no further replication will insert TB(1,1)
> on consolidate.

How is it possible in a SQL Remote environment to have a row in a
replicating table on the remote that does not exist at the consolidated if
the two databases are in synch (i.e. no changes to send between them)? Why
did the insert on TB(1,1) not replicate up the consolidated when it was
first inserted?

I made mention in a earlier post to a "properly designed system", but the
situation you describe over certainly doesn't fall into the category.

--
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/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

<flagasse> wrote in message news:43e1d138.51a8.1681692777@sybase.com...
> Thanks,
>
> To resume, The silent delete occurs in this case :
> table TA (CA )
> table TB (CA, CB)
> FK TB(CA) => TA(CA)
>
> 1. manual : insert 1 into TA(CA) on consolidate
> 2. replication : insert 1 into TA(CA) on remote
> 3. manual : insert 1,1 into TB(CA,CB) on remote
> 4. manual : delete 1 from TA(CA) on consolidate
> 5. replication : try to insert 1,1 into TB(CA,CB) on
> colidate : FK violation
> 6. replication : delete 1,1 from TB(CA, CB), delete 1 from
> TA(CA) on remote
>
> But now I tell to you why I am afraid :
> We really have some rows that are not "perfectly"
> synchronise in our system, this is not a conceptual case.
> We have : TA(1), TB(1,1) on remote and TA(1) on
> consolidate, and no further replication will insert TB(1,1)
> on consolidate.
>
> We have 250 remote sites (1.5 GB).
> We have a "big" consolidated database (120GB).
> We have a low bandwith network (6.4kB/s, 1.5GB takes more
> than 2 days to be transfered)
> The remote sites can only be stop on sundays on during the
> night.
> Because we have financial datas on remote, we cannot
> directly re-extract remote datas from consolidate.
>
>
> So, silent deletes have to be avoid.
> And I see 5 solutions to avoid them :
> keep using sqlremote (I think the worse solution)
> use mobilink with perfectly synchronised datas (any ideas
> ?)
> use mobilink with no FK on consolidate (!!!!!!!)
> use mobilink combine with a system that can detect and
> correct FK violation (the ideal solution)
> use a non Sybase product (they are numerous)
>
> flagasse

Reg Domaratzki \(iAnywhere Solutions\)

2006-02-02, 11:24 am

underprocessable

flagasse

2006-02-07, 9:24 am

What am trying to do is not to demonstrate that your product
has a weakness. I am just looking for problems that could be
in the future if we use MobiLink. And I am trying to find a
way to avoid this sort of problem.

That is what we are payed for.

"How do currently handle the problem of distributed deletes
using SQL Remote?"
> SQL Remote just do its job, if deletes are made, deletes
will be synchronised. If a FK is viloated it just logs it.

"I made mention in a earlier post to a "properly designed
system", but the situation you describe over certainly
doesn't fall into the category."
Your are absolutly right !
But that you do not know is that Sybase Support and Sybase
Consulting have caused the problem.

When we have started to use SQLRemote, Sybase consulting has
install it on our sites. But in year 2000, losts of problems
occured : bugs were detected, many versions were installed.
They left us with de-synchronized datas.
But, at least, SQLRemote was working.
In 2004 when we planed to migrate from SQLremote 6 to
SQLRemote 9 a bug was found during the first week (bug with
ASE 12.x and SQLRemote 9) : SQL remote sent data to wrong
subscribers ! This will cause a big de-synchronisation.
To correct the problem Sybase Support ask us to do a reset
of all the SQLRemote offsets ! this was an other cause of
de-synchronisation.

In decembre 2005 our Raid 5 disk were broken. The provider
of the raid system said that "It can occured some times
!!!!!!!". Because on a faulty manipulation on tapes we have
lost the last database dump. So we used an old one : this
has caused an other de-synchronisation.

BUT SQLREMOTE CAN HANDLE THIS SITUATION !
Of course FK were viloated, but they were tracked and
corrected by scripts. FK violation occurds almost every day,
but they are automaticly correted : NOT BY A DELETE, but by
a script that can get correct data on remote.

You have made your own conclusion : Mobilink product feets
all the situations.
I hope it to, but I think there is always exceptions.

Sponsored Links