SQL Anywhere database replication - Resetting SQL remote

Author

Resetting SQL remote

Pavel Karady

2006-02-07, 11:23 am

Greetings excelsiors,

I've downloaded two databases that replicate using SQL Remote to my local
comp. Database (1) from a certain date, database (2) is from other date,
different than by (1).

It's natural that replication won't work perfectly between these. I can see
some missing messages, but what's worse, the remote db says "Not applying
operations that have already been applied". If I update something *new* on
the cons, the remote db receives the message and says "Not applying
operations that have already been applied" again, even if the message had
brand new instructions what to do with the data. So I have the change in
cons (1), but not in remote db (2). I bet this is something with the
transaction log offset.

Is there any way, how to completely reset the replication, how to start from
the beginning? No missing messages, no already applied, no nothing just pure
start from where we are now. I've dropped all the publications, subscribed
and started the subscriptions, but still can't get rid of this.

Very pretty thanks
Pavel

Rob Waywell

2006-02-08, 9:23 am

Take a look at the REMOTE RESET command, but I expect you would be much
better off re-extracting.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:43e8ce71$1@foru
ms-1-dub...
> Greetings excelsiors,
>
> I've downloaded two databases that replicate using SQL Remote to my local
> comp. Database (1) from a certain date, database (2) is from other date,
> different than by (1).
>
> It's natural that replication won't work perfectly between these. I can
> see some missing messages, but what's worse, the remote db says "Not
> applying operations that have already been applied". If I update something
> *new* on the cons, the remote db receives the message and says "Not
> applying operations that have already been applied" again, even if the
> message had brand new instructions what to do with the data. So I have the
> change in cons (1), but not in remote db (2). I bet this is something with
> the transaction log offset.
>
> Is there any way, how to completely reset the replication, how to start
> from the beginning? No missing messages, no already applied, no nothing
> just pure start from where we are now. I've dropped all the publications,
> subscribed and started the subscriptions, but still can't get rid of this.
>
> Very pretty thanks
> Pavel
>

Pavel Karady

2006-02-09, 3:23 am

"Rob Waywell" < rwaywell_no_spam_ple
ase@ianywhere.com> wrote in message
news:43ea1a9a$1@foru
ms-1-dub...
> Take a look at the REMOTE RESET command, but I expect you would be much
> better off re-extracting.

Thanks so far, you were right with the re-extracting suggestion, REMOTE
RESET looked fantastic until I've really used it. Still, warnings and errors
sustain:

W. 02/09 09:25:56. Not applying operations that have already been applied
---------
W. 02/09 09:26:03. Not applying operations with old resend count
I. 02/09 09:26:03. Resend requests are being queued
---------
E. 02/09 09:31:02. Missing message from "mainusr"
(0-0000000000-01223175400-0)

(these messages come from different logs)

Re-extracting is not a solution for me, because I need the data in both
databases to remain as they are now.

I'm still looking for a complete-replication-restart solution, I don't mind
loosing any changes or anything, I just need the replication to perform
without any error or warning. I can't believe, there isn't one.

Dropping every remote/consolidated user on both databases, every
publication, then applying transaction logs on both databases and
re-creating all the users, publications and subscriptions - will this help?

Or will truncating the transaction log and issuing REMOTE RESET on every
remote user help?
Or do the transaction logs need to be dropped and re-created?

Pavel

Pavel Karady

2006-02-09, 7:23 am

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:43eb02eb$1@foru
ms-2-dub...
> Dropping every remote/consolidated user on both databases, every
> publication, then applying transaction logs on both databases and
> re-creating all the users, publications and subscriptions - will this
> help?

This worked, replications are "restarted" and perform flawless. I've created
a script which connects to every database, drops everything, apllies logs,
connects again and recreates and sets and starts everything, so future
restarts will be easy. But I still think, there should be one single
statement for that, and REMOTE RESET is not doing the job.

Thank you Rob

Pavel

Reg Domaratzki

2006-02-09, 7:23 am

If you execute REMOTE RESET on the remote side for the consolidated user and
at the consolidated database for the appropriate remote user, it should have
resolves the errors you are getting. Did you do the REMOTE RESET on both
sides?

Reg

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:43eb0ebd$1@foru
ms-2-dub...
> "Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
> news:43eb02eb$1@foru
ms-2-dub...
>
> This worked, replications are "restarted" and perform flawless. I've
created
> a script which connects to every database, drops everything, apllies logs,
> connects again and recreates and sets and starts everything, so future
> restarts will be easy. But I still think, there should be one single
> statement for that, and REMOTE RESET is not doing the job.
>
> Thank you Rob
>
> Pavel
>
>

Pavel Karady

2006-02-09, 9:23 am

"Reg Domaratzki" <firstname.lastname@ianywhere.com> wrote in message
news:43eb49c0@forums
-1-dub...
> If you execute REMOTE RESET on the remote side for the consolidated user
> and
> at the consolidated database for the appropriate remote user, it should
> have
> resolves the errors you are getting. Did you do the REMOTE RESET on both
> sides?

Yes, I have a "mainusr" and "remusr" in both databases, in (1) "mainusr" is
the publisher and "remusr" is the remote user,
in (2) "mainusr" is the consolidated remote user and "remusr" is the
publisher.

I've pushed REMOTE RESET "remusr" at (1) and REMOTE RESET "mainusr" at (2).
I've even tried REMOTE RESET "mainusr" at (1) and REMOTE RESET "remusr" at
(2), but a correct error was shown, that these users are not remote users.

After that, nothing was solved. Maybe it's in the publications, I've not
dropped and created new ones.

Even more, I'm currently experiencing
1. in other rep environment, a message 'UPDATE FIRST table SET ... WHERE ...
AND ...' sent by the remote database six times, with only one UPDATE issued
from dbisqlc, and
2. in another rep environment, a statement is sent from remote (X) to cons
(Y) db, then sent by cons db to other remote dbs including the (X) db! The
returning statement(s) look somewhat weird so I am still investigating this,

but these are for another threads I guess. After a lot of investigation.

Pavel

Volker Barth

2006-02-10, 3:23 am

Hi Pavel,

just a few remarks - see below.

Greetings
Volker

> "Reg Domaratzki" <firstname.lastname@ianywhere.com> wrote
> in message news:43eb49c0@forums
-1-dub...
>
> Yes, I have a "mainusr" and "remusr" in both databases, in
> (1) "mainusr" is the publisher and "remusr" is the remote
> user, in (2) "mainusr" is the consolidated remote user and
> "remusr" is the publisher.
>
> I've pushed REMOTE RESET "remusr" at (1) and REMOTE RESET
> "mainusr" at (2). I've even tried REMOTE RESET "mainusr"
> at (1) and REMOTE RESET "remusr" at (2), but a correct
> error was shown, that these users are not remote users.
>
> After that, nothing was solved. Maybe it's in the
> publications, I've not dropped and created new ones.
>
I've had to use REMOTE RESET a few times, and it worked well
(under the assumption that the corresponding data *is*
correct and up-to-date in both DBs). Did you do an explicit
COMMIT after the statement?

> Even more, I'm currently experiencing
> 1. in other rep environment, a message 'UPDATE FIRST table
> SET ... WHERE ... AND ...' sent by the remote database
> six times, with only one UPDATE issued from dbisqlc, and

To my understanding, as SQL Remote replicates operations,
not statements, it has to split each UPDATE/DELETE into a
separate statement for each particular row. - So if the
original UPDATE changed 6 rows (that are meant to be in the
remote DB) SQL Remote will have to send 6 rows.

> 2. in another rep environment, a statement is sent from
> remote (X) to cons (Y) db, then sent by cons db to other
> remote dbs including the (X) db! The returning
> statement(s) look somewhat weird so I am still
> investigating this,
Looks like the default handling in case of an update
conflict - I think this is the only default situation in
which SQL Remote sends an operation back to the sending DB.
Otherwise, you have to use advanced features like remote
hooks to get such behaviour.

>
> but these are for another threads I guess. After a lot of
> investigation.
>
> Pavel

Pavel Karady

2006-02-10, 7:23 am

Hi Volker,

thanks for your comments, my comments are also inline.

> I've had to use REMOTE RESET a few times, and it worked well
> (under the assumption that the corresponding data *is*
> correct and up-to-date in both DBs). Did you do an explicit
> COMMIT after the statement?

Yes, this could be it (the missing explicit COMMIT). The effect of my bad
habit and unrealized expectations. (I get a habit of what's committed and
what's not and I surely thought this statement has an autocommit - missing
the fact that the entire help of this command is about the commit NOT
performed.)

> To my understanding, as SQL Remote replicates operations,
> not statements, it has to split each UPDATE/DELETE into a
> separate statement for each particular row. - So if the
> original UPDATE changed 6 rows (that are meant to be in the
> remote DB) SQL Remote will have to send 6 rows.

This would be true, when the table which I have tested this on, had not only
an *one single row*. But this was a special situation and if this doesn't
get solved or explained by myself during the next weeks, a detailed thread
will be started.

> Looks like the default handling in case of an update
> conflict - I think this is the only default situation in
> which SQL Remote sends an operation back to the sending DB.
> Otherwise, you have to use advanced features like remote
> hooks to get such behaviour.

This is a valuable comment, I'll try to get to the bottom of this.

Thanks!
Pavel

Reg Domaratzki $iAnywhere Solutions$

2006-02-10, 9:23 am

> 2. in another rep environment, a statement is sent from remote (X) to cons
> (Y) db, then sent by cons db to other remote dbs including the (X) db! The
> returning statement(s) look somewhat weird so I am still investigating
this,

Like Volker, I agree this looks like default conflict resolution behaviour.
Let's assume that two remotes ( r1 + r2 ) and the consolidated (c1) all
share the same row in a simple table ( create table t1 pkey integer primary
key, c1 integer), and the value is (1,1).

Imagine the following sequence of events :

1) c1 : update t1 set c1 = 10 where pkey = 1
2) r1 : update t1 set c1 = 20 where pkey = 1
3) r1 : Run dbremote. The SQL that is placed in the message file is :
update t1 set c1 = 20 verify (c1) values (1) where pkey = 1
4) c1 : Run dbremote. Update goes through, but marked in transaction log as
conflict, since the verify clause did not match. The log entry also stores
information about which remote user the message came from.

When dbremote at c1 sends messages, remember there are two things to
remember :

1) dbremote simply scans operations in the transaction log to send messages.
2) dbremote does not send operations that came from remote user X back to
remote user X.

When dbremote on c1 scans the transaction log to send messages to r2, it
sees two operations to the row where pkey=1. The message that is generated
for r2 will contain (in this order) :

update t1 set c1=10 where pkey = 1
update t1 set c1 = 20 where pkey = 1

This is expected, and The Right Thing To Do.

When dbremote on c1 scans the transaction log to send messages to r1, it
also sees two operations, but normally it would not send the 2nd operation,
since it came from r1. If this were to happen, then only a single update
would be sent to r1 :

update t1 set c1=10 where pkey = 1

This would leave the row on r1 in a different state than one the
consolidated and r2, so when a conflict happens, we need to "echo" the
update back down to the remote that caused the conflict.

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

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:43eb4244$1@foru
ms-2-dub...
> "Reg Domaratzki" <firstname.lastname@ianywhere.com> wrote in message
> news:43eb49c0@forums
-1-dub...
both[color=darkred]
>
> Yes, I have a "mainusr" and "remusr" in both databases, in (1) "mainusr"
is
> the publisher and "remusr" is the remote user,
> in (2) "mainusr" is the consolidated remote user and "remusr" is the
> publisher.
>
> I've pushed REMOTE RESET "remusr" at (1) and REMOTE RESET "mainusr" at
(2).
> I've even tried REMOTE RESET "mainusr" at (1) and REMOTE RESET "remusr" at
> (2), but a correct error was shown, that these users are not remote users.
>
> After that, nothing was solved. Maybe it's in the publications, I've not
> dropped and created new ones.
>
> Even more, I'm currently experiencing
> 1. in other rep environment, a message 'UPDATE FIRST table SET ... WHERE
....
> AND ...' sent by the remote database six times, with only one UPDATE
issued
> from dbisqlc, and
> 2. in another rep environment, a statement is sent from remote (X) to cons
> (Y) db, then sent by cons db to other remote dbs including the (X) db! The
> returning statement(s) look somewhat weird so I am still investigating
this,

>
> but these are for another threads I guess. After a lot of investigation.
>
> Pavel
>
>

Pavel Karady

2006-02-10, 11:23 am

Reg, thanks for your exhausting response, you really cleared things up. I
think I fully understand what you have written (and more, I've realized that
behavior just a minute before I've read your post :) it still cleared things
up more) but the erroreous behavior I am experiencing does not fit into this
(or looks like it does not fit into this). Working hard to reproduce the
erroreous scenario, I've created a sequence of statements that might be able
to perform the reproduction.

I am going to start a "Replication Anomaly 1" thread for that.

Pavel

Pavel Karady

2006-02-13, 7:23 am

<Volker Barth> wrote in message news:43ec62d7.6d52.1681692777@sybase.com...
> Did you do an explicit COMMIT after the REMOTE RESET statement?

That was it. Committed REMOTE RESET did the job.

Thanks
Pavel

Sponsored Links