SQL Anywhere database replication - CURRENT USER, LAST USER and Replication

Author

CURRENT USER, LAST USER and Replication

Clive Collie

2005-10-27, 7:41 am

I have a databse that started life under ASA 5 and has been unloaded and
reloaded in every version up to ASA 9.02. I have various fields with a
default of CURRENT USER. These get filled with the name of the currently
logged in user when a record is UPDATE-ed.

However any new tables I add containing a field with default CURRENT USER
does not get altered during and UPDATE. To do that I need to make it DEFAULT
LAST USER.

How come the old CURRENT USER fields carry on doing what they always did
even though they look idential to a new CURRENT USER field (which now have
to be LAST USER) when inspected in ISQL? If they have been magically altered
to LAST USER fields should it not call them LAST USER in ISQL.

If I make a field DEFAULT LAST USER will it get changed during Replication?
The old CURRENT USER fields did not. A default of COMPUTE(current user) gets
the name of the remote DBA put in during replication. I don't want this
information changed by replication.

Nick Elson

2005-10-27, 7:41 am

I am not aware of any bug in any version where a column
definition with a default of CURRENT USER ***ever***
changed values for update statements.

Are you sure you were not depending upon update triggers
in your older tables?

FWIW . . .

In normal or standard SQL, defaults only apply to INSERTS
and then only to inserts that have not provided a value for the
column [which can also happen if they supply a value using the
'place-holder', 'special constant' called 'DEFAULT'].

The only exceptions to this INSERT-ONLY-DEFAULT behaviour
includes the following:

- [update] triggers that override the standard behaviour or,
otherwise, some user logic added to change the standard

- cascade RI actions [though that would require an update
of a 'parent' table for that to take hold]

- when the default is on a timestamp/datetime column and
the 'special' default TIMESTAMP is used

- when the LAST_USER 'special' default is chosen

anything else should be considered to be a bug.

The TIMESTAMP DEFAULT TIMESTAMP case is a 'special
case' that also maps to an ASE feature that is only used for
'row-versioning' ; often used with optimistic locking designs. It
is not a standard SQL feature but row-version is just something
that exists in the industry and has some conveniences for some
application domains. [note you can always craft your own
flavour of row-versioning using triggers if you don't like ours]

The default LAST_USER is another special case, that (in my mind)
is an early form of tracking/auditing; if you chose to take advantage
of it. It too is also not a standard SQL feature but an ASA product
feature.

"Clive Collie" <clive. doentdospam@dillisto
ne.com> wrote in message
news:435644c3$1@foru
ms-1-dub...
>I have a databse that started life under ASA 5 and has been unloaded and
>reloaded in every version up to ASA 9.02. I have various fields with a
>default of CURRENT USER. These get filled with the name of the currently
>logged in user when a record is UPDATE-ed.
>
> However any new tables I add containing a field with default CURRENT USER
> does not get altered during and UPDATE. To do that I need to make it
> DEFAULT LAST USER.
>
> How come the old CURRENT USER fields carry on doing what they always did
> even though they look idential to a new CURRENT USER field (which now have
> to be LAST USER) when inspected in ISQL? If they have been magically
> altered to LAST USER fields should it not call them LAST USER in ISQL.
>
> If I make a field DEFAULT LAST USER will it get changed during
> Replication? The old CURRENT USER fields did not. A default of
> COMPUTE(current user) gets the name of the remote DBA put in during
> replication. I don't want this information changed by replication.
>

Nick Elson

2005-10-27, 7:41 am

The last posting was an attempt to answer this paragraph from your original
posting:

> How come the old CURRENT USER fields carry on doing what they always did
> even though they look idential to a new CURRENT USER field (which now have
> to be LAST USER) when inspected in ISQL? If they have been magically
> altered to LAST USER fields should it not call them LAST USER in ISQL.

As to your last paragraph:

> If I make a field DEFAULT LAST USER will it get changed during
> Replication?

Yes and probably many times for the same update operation; since it can fire
at all locations the update statement flows too. In fact it will appear to
change
unpredictably under slightly different and very subtle cases. All of which
is
perfectly normal, so, ... I generally recommend against using thet LAST USER
special default when synchronizing or replicating.

In fact there is a pending change to the documentation that will recommend
against using LAST USER in systems that replicate or synchronize.

> The old CURRENT USER fields did not.

Correct! It should only supply a value for the site that inserts the row.

> ... A default of COMPUTE(current user) gets the name of the remote DBA put
> in during replication.

Woh - Ah ... COMPUTE( ) columns?
Is that what you are really attempting here?

Computed columns do not store any values and so the values
are not replicated but calculated on each query access.

???So what is your exact usage???

> I don't want this information changed by replication.

Thus don't use either

- a COMPUTEd column
or
- a default of LAST USER

Again I suspect you have some user logic wrapped into triggers and possibly
triggers that were sensitive to if values are being replicated or supplied
from
a normal user connection.

"Clive Collie" <clive. doentdospam@dillisto
ne.com> wrote in message
news:435644c3$1@foru
ms-1-dub...
>I have a databse that started life under ASA 5 and has been unloaded and
>reloaded in every version up to ASA 9.02. I have various fields with a
>default of CURRENT USER. These get filled with the name of the currently
>logged in user when a record is UPDATE-ed.
>
> However any new tables I add containing a field with default CURRENT USER
> does not get altered during and UPDATE. To do that I need to make it
> DEFAULT LAST USER.
>
> How come the old CURRENT USER fields carry on doing what they always did
> even though they look idential to a new CURRENT USER field (which now have
> to be LAST USER) when inspected in ISQL? If they have been magically
> altered to LAST USER fields should it not call them LAST USER in ISQL.
>
> If I make a field DEFAULT LAST USER will it get changed during
> Replication? The old CURRENT USER fields did not. A default of
> COMPUTE(current user) gets the name of the remote DBA put in during
> replication. I don't want this information changed by replication.
>

Reg Domaratzki $iAnywhere Solutions$

2005-10-27, 7:41 am

I'll answer one specific question in your post, since it appears that you've
figured out that there are triggers on the tables that have DEFAULT CURRENT
USER, but seem to be changed when you update the row.

> If I make a field DEFAULT LAST USER will it get changed during
Replication?

Possibly, and here's why, which will also explain why you have triggers on
the tables in question and a DEFAULT CURRENT USER. Assume a table exists
with the following schema at both the consolidated and remote :

create table t1 (
pkey bigint default global autoincrement primary key,
c1 integer,
lu varchar(128) default last user
);

Assume that the tables are in synch and both contain a single row with
values ( 1, 1, 'DBA' ).

Connect to the remote database as user 'CLIVE' and execute the following SQL
:

update t1 set c1 = 4 where pkey=1;
commit;
update t1 set c1 = 5 where pkey=1;
commit;

If you translate the transaction log right now for the remote database,
you'll see the updates, and they'll look similar to :

update t1 set c1=4, lu='CLIVE' where pkey=1;
update t1 set c1=5 where pkey=1;

Notice that the lu column did not change on the 2nd update (it was 'CLIVE',
the new value was 'CLIVE'), so there is nothing in the transaction log to
indicate this. These two update statements will be placed in the message
when dbremote runs on the remote.

Now, let's consider what happens when the consolidated picks up the message.
Assume that user 'DBA' is used in the connection string for dbremote. The
first update applies, and a specific value was given for the lu column, so
the default never kicks in, and the value 'CLIVE' is used, as expected.
Now, the second update occurs, but there's no value specified for the lu
column, so the default on the consolidated kicks in. Since dbremote
connects with user 'DBA', that's the value that is used for the lu column on
the 2nd update. You get around this limitation by doing exactly what you
did. Define the default to be CURRENT USER instead of LAST USER so that you
still get the same effect on inserts. Now, define a before update trigger
that only fires when dbremote is NOT running.

create trigger bu_t1 before update on t1
referencing new as nr for each row
begin
if CURRENT REMOTE USER IS NULL then
set nr.lu = CURRENT USER;
end if;
end;

Now, when dbremote runs and there are two updates in a row from the same
user, it does not get set to the user that is used by dbremote.

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

"Clive Collie" <clive. doentdospam@dillisto
ne.com> wrote in message
news:435644c3$1@foru
ms-1-dub...
> I have a databse that started life under ASA 5 and has been unloaded and
> reloaded in every version up to ASA 9.02. I have various fields with a
> default of CURRENT USER. These get filled with the name of the currently
> logged in user when a record is UPDATE-ed.
>
> However any new tables I add containing a field with default CURRENT USER
> does not get altered during and UPDATE. To do that I need to make it
DEFAULT
> LAST USER.
>
> How come the old CURRENT USER fields carry on doing what they always did
> even though they look idential to a new CURRENT USER field (which now have
> to be LAST USER) when inspected in ISQL? If they have been magically
altered
> to LAST USER fields should it not call them LAST USER in ISQL.
>
> If I make a field DEFAULT LAST USER will it get changed during
Replication?
> The old CURRENT USER fields did not. A default of COMPUTE(current user)
gets
> the name of the remote DBA put in during replication. I don't want this
> information changed by replication.
>
>

Clive Collie

2005-10-27, 7:41 am

Thanks Reg,
I was struggling to reverse engineer a database, even though I wrote it
myself a few years back. As it turns out I did use all the tricks you
mention including the REMOTE USER IS NULL. Thanks for confirming that this
was the correct way to design these fields for replication. Hopefully next
time I will remember why I did what I did!
Clive.

"Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName@ianywhere.com>
wrote in message news:43565d73$1@foru
ms-1-dub...
> I'll answer one specific question in your post, since it appears that
> you've
> figured out that there are triggers on the tables that have DEFAULT
> CURRENT
> USER, but seem to be changed when you update the row.
>
> Replication?
>
> Possibly, and here's why, which will also explain why you have triggers on
> the tables in question and a DEFAULT CURRENT USER. Assume a table exists
> with the following schema at both the consolidated and remote :
>
> create table t1 (
> pkey bigint default global autoincrement primary key,
> c1 integer,
> lu varchar(128) default last user
> );
>
> Assume that the tables are in synch and both contain a single row with
> values ( 1, 1, 'DBA' ).
>
> Connect to the remote database as user 'CLIVE' and execute the following
> SQL
> :
>
> update t1 set c1 = 4 where pkey=1;
> commit;
> update t1 set c1 = 5 where pkey=1;
> commit;
>
> If you translate the transaction log right now for the remote database,
> you'll see the updates, and they'll look similar to :
>
> update t1 set c1=4, lu='CLIVE' where pkey=1;
> update t1 set c1=5 where pkey=1;
>
> Notice that the lu column did not change on the 2nd update (it was
> 'CLIVE',
> the new value was 'CLIVE'), so there is nothing in the transaction log to
> indicate this. These two update statements will be placed in the message
> when dbremote runs on the remote.
>
> Now, let's consider what happens when the consolidated picks up the
> message.
> Assume that user 'DBA' is used in the connection string for dbremote. The
> first update applies, and a specific value was given for the lu column, so
> the default never kicks in, and the value 'CLIVE' is used, as expected.
> Now, the second update occurs, but there's no value specified for the lu
> column, so the default on the consolidated kicks in. Since dbremote
> connects with user 'DBA', that's the value that is used for the lu column
> on
> the 2nd update. You get around this limitation by doing exactly what you
> did. Define the default to be CURRENT USER instead of LAST USER so that
> you
> still get the same effect on inserts. Now, define a before update trigger
> that only fires when dbremote is NOT running.
>
> create trigger bu_t1 before update on t1
> referencing new as nr for each row
> begin
> if CURRENT REMOTE USER IS NULL then
> set nr.lu = CURRENT USER;
> end if;
> end;
>
> Now, when dbremote runs and there are two updates in a row from the same
> user, it does not get set to the user that is used by dbremote.
>
> --
> 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"
>
>
> "Clive Collie" <clive. doentdospam@dillisto
ne.com> wrote in message
> news:435644c3$1@foru
ms-1-dub...
> DEFAULT
> altered
> Replication?
> gets
>
>

Sponsored Links