SQL Anywhere database replication - Re: Replication Anomaly 1

Author

Re: Replication Anomaly 1

Reg Domaratzki $iAnywhere Solutions$

2006-02-15, 9:24 am

Pavel, I've been swamped with other work and haven't had time to look into
this, despite what is obviously a very well written description of the
problem that should be easy to reproduce.

If this is a time critical problem, I'd suggest opening a call with tech
support. If it isn't check back to the newsgroup every so often and see if
I've (or maybe Rob -> hint hint) has gotten to it.

--
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:43ecc8df@forums
-2-dub...
> Greetings sages,
>
> we have a consolidated and a remote database, both ASA 9.0.2.2551.
> Basically, issuing an UPDATE on the remote, which gets to the cons but
> VERIFY does not match, so another UPDATE is fired to the remote, with some
> triggers involved cause the SQL Remote leave the column on remote which we
> updated first with a wrong value, the replicated update to cons updated
the
> good value.
>
> The statements to reproduce this are very easy to follow, just please
don't

> get scared by the look and amount.
>
> My comments continue below the statements.
>
> -- Both databases are running, both message agents are not running
>
> -- Remote database:
> CREATE TABLE DBA.t5 (
> id INTEGER PRIMARY KEY,
> userid LONG VARCHAR DEFAULT CURRENT USER,
> entry TIMESTAMP DEFAULT TIMESTAMP
> );
> INSERT t5 VALUES (1,'DBA','2005-05-05 05:05:05.005');
> COMMIT;
>
> CREATE TRIGGER "t_t5_set_userid" BEFORE UPDATE ORDER 2 ON "DBA"."t5"
> REFERENCING NEW AS @new_row
> FOR EACH ROW BEGIN
> IF CURRENT REMOTE USER IS NULL THEN
> SET @new_row.userid = CURRENT USER;
> END IF;
> END
>
>
> -- Consolidated database:
> CREATE TABLE DBA.t5 (
> id INTEGER PRIMARY KEY,
> userid LONG VARCHAR DEFAULT CURRENT USER,
> entry TIMESTAMP DEFAULT TIMESTAMP
> );
> INSERT t5 VALUES (1,'Luis Telles','2002-02-02 02:02:02.002');
> COMMIT;
>
> CREATE TRIGGER "t_t5_set_userid" BEFORE UPDATE ORDER 2 ON "DBA"."t5"
> REFERENCING NEW AS @new_row
> FOR EACH ROW BEGIN
> IF CURRENT REMOTE USER IS NULL THEN
> SET @new_row.userid = CURRENT USER;
> END IF;
> END
>
>
> -- Remote database:
> CREATE PUBLICATION t5 (TABLE t5);
> CREATE SUBSCRIPTION TO "t5" FOR "mainusr";
> START SUBSCRIPTION TO "t5" FOR "mainusr";
>
>
> -- Consolidated database:
> CREATE PUBLICATION t5 (TABLE t5);
> CREATE SUBSCRIPTION TO "t5" FOR "remusr";
> START SUBSCRIPTION TO "t5" FOR "remusr";
>
>
> -- Start both agents
>
>
> -- Remote database - log in as DBA
> UPDATE t5
> SET userid = 'Monicca'
> WHERE id = 1;
>
> UPDATE t5
> SET entry = '2004-04-04 04:04:04.004'
> WHERE id = 1;
>
> COMMIT;
>
>
>
> Both message agent logs are attached to this post.
>
> Basically, the use of the triggers is to change the userid value each time
a
> row gets updated, in other words, simulate the DEFAULT LAST USER.
>
> I've hard time developing these statements, started from scratch,
> replication worked very fine, then added a new column userid, reps worked
> very fine, added triggers etc. I believe these triggers play an important
> role in this, because without them, everything works fine.
>
> Aside of the entry column UPDATE bouce, the first UPDATE (update of the
> userid) doesn't get replicated at all.
>
> Any explanations to this would be remembered for the rest of my life.
>
> Pavel
>
>
>

Pavel Karady

2006-02-20, 7:23 am

Reg :)) No problem with that, take any time you want, unless the amount is <
than 6 months. Getting swamped with other work is a common thing to happen -
to myself included. Hope you're doing progress in your work with ease of a
breeze.

Pavel

"Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName@ianywhere.com>
wrote in message news:43f33fc7$1@foru
ms-1-dub...
> Pavel, I've been swamped with other work and haven't had time to look into
> this, despite what is obviously a very well written description of the
> problem that should be easy to reproduce.
>
> If this is a time critical problem, I'd suggest opening a call with tech
> support. If it isn't check back to the newsgroup every so often and see
> if
> I've (or maybe Rob -> hint hint) has gotten to it.
>
> --
> 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:43ecc8df@forums
-2-dub...
> the
> don't
> a
>
>

Sponsored Links