Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Alter a column in table with default would cause DSI down

5 posts in General Discussion Last posting was on 2011-11-16 03:09:18.0Z
Eisen Posted on 2011-11-15 02:34:15.0Z
Sender: 3c6d.4ec1c710.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.rep-server
Subject: Alter a column in table with default would cause DSI down
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ec1cfa7.3f84.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Nov 2011 18:34:15 -0800
X-Trace: forums-1-dub 1321324455 10.22.241.41 (14 Nov 2011 18:34:15 -0800)
X-Original-Trace: 14 Nov 2011 18:34:15 -0800, 10.22.241.41
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9089
Article PK: 870250

Dear sirs
I've built a MSA system from A.a to B.b with DDL
replicate. It works fine for several months. But this
morning, when a guy alter a column datatype in a table with
default expression, there's a system warning raised on the
primary side --
"Warning: a default (object id 1033103740) is defined on
column 'v' being modified. Check the validity of the default
value after this ALTER TABLE operation."

Since this is only a warning, so nobody cares. But soon
we found the DSI to B.b is down. And with checking the
content inside the RS queue, we found it's due to the "alter
table" statement. But to our surprise, we found the datatype
on the replicate side is already modified... Does anyone
know what happend and what to prevent this? Thanks in
advance for any help.

Best Regards
Eisen


Manish Negandhi [TeamSybase] Posted on 2011-11-15 03:35:19.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.rep-server
References: <4ec1cfa7.3f84.1681692777@sybase.com>
Subject: Re: Alter a column in table with default would cause DSI down
Lines: 35
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ec1ddf7$1@forums-1-dub>
Date: 14 Nov 2011 19:35:19 -0800
X-Trace: forums-1-dub 1321328119 10.22.241.152 (14 Nov 2011 19:35:19 -0800)
X-Original-Trace: 14 Nov 2011 19:35:19 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9090
Article PK: 870248

Can you please post below details, it will help to investigate the problem
further

(1) version of primary and replicate ASE as well as repserver you are using
(2) log transaction with sysadmin log_first_tran and output of the
transaction using rs_helpexception
(3) exact message from the repserver error log when DSI goes down


Manish Negandhi
[TeamSybase]

<Eisen> wrote in message news:4ec1cfa7.3f84.1681692777@sybase.com...
> Dear sirs
> I've built a MSA system from A.a to B.b with DDL
> replicate. It works fine for several months. But this
> morning, when a guy alter a column datatype in a table with
> default expression, there's a system warning raised on the
> primary side --
> "Warning: a default (object id 1033103740) is defined on
> column 'v' being modified. Check the validity of the default
> value after this ALTER TABLE operation."
>
> Since this is only a warning, so nobody cares. But soon
> we found the DSI to B.b is down. And with checking the
> content inside the RS queue, we found it's due to the "alter
> table" statement. But to our surprise, we found the datatype
> on the replicate side is already modified... Does anyone
> know what happend and what to prevent this? Thanks in
> advance for any help.
>
> Best Regards
> Eisen


Eisen Posted on 2011-11-15 05:44:55.0Z
Sender: 3c6d.4ec1c710.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.rep-server
Subject: Re: Alter a column in table with default would cause DSI down
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ec1fc57.5235.1681692777@sybase.com>
References: <4ec1ddf7$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Nov 2011 21:44:55 -0800
X-Trace: forums-1-dub 1321335895 10.22.241.41 (14 Nov 2011 21:44:55 -0800)
X-Original-Trace: 14 Nov 2011 21:44:55 -0800, 10.22.241.41
Lines: 95
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9091
Article PK: 870252

Hi Manish
Thanks for your reply. Here's is some information --
A. Both primary and replicate site are ASE15.0.3 for AIX,
only one RS server is Rep15.6 on AIX
B. let me describe the issue more clearly --
1. Both on A.a and B.b there are table named test_wj1
create table test_wj1(id int,v varchar(10) default ' ')
2. On A.a, someone execute "alter table test_wj1 modify v
varchar(20)". There's a warning "Warning: a default (object
id 1033103740) is defined on column 'v' being modified.
Check the validity of the default value after this ALTER
TABLE operation."
3. This time, we can find the DSI to B.b is down. and the RS
errorlog is --
"I. 2011/11/15 09:52:26. Message from server: Message:
13900, State 1, Severity 10 -- 'Warning: a default (object
id 1900582828) is defined on column 'v' being modified.
Check the validity of the default value after this ALTER
TABLE operation.'.
H. 2011/11/15 09:52:26. THREAD FATAL ERROR #5049 DSI
EXEC(110(1) B.b) - dsiqmint.c(4305)
The DSI thread for database 'B.b' is being shutdown.
DSI received data server error #13900 which is mapped to
STOP_REPLICATION. See logged data server errors for more
information. The data server error was caused by output
command #5 mapped from input command #1 of the failed
transaction.
I. 2011/11/15 09:52:26. The DSI thread for database 'B.b' is
shutdown."
4. At this time, sysadmin log_first_tran,B,b, in the dump
file --
"I. 2011/11/15 13:43:13. DUMP OF FIRST 1 TRANSACTIONS FOR
DATABASE 'B.b'
I. 2011/11/15 13:43:13. begin transaction
I. 2011/11/15 13:43:13. execute rs_update_lastcommit @origin
= 109, @origin_qid=
0x00000000006f90ea0001d026002d0001d025002100009f9c00e1f2b60000000000000001
, @secondary_qid =
0x000000000000000000000000000000000000000000000000000000000000000000000000
, @origin_time = '20111115 13:42:39:113'
I. 2011/11/15 13:43:13. if @@error <> 0 rollback
transaction
I. 2011/11/15 13:43:13. commit transaction
I. 2011/11/15 13:43:13. END DUMP OF FIRST 1 TRANSACTIONS FOR
DATABASE 'B.b'
I. 2011/11/15 13:43:13. The first 1 transaction(s) for
database 'sybwpmdr00.fawpmmo0xc' have been logged into the
exceptions log. They have also been logged either in
repserver log, or the location specified by sysadmin
dump_file command.
"
5. after "resume connection to B.b skip tran" and
"rs_helpexception 267,v" we can see the content is -- "alter
table test_wj1 modify v varchar(20)"

Please have a look. Thanks
Best Regards
Eisen

> Can you please post below details, it will help to
> investigate the problem further
>
> (1) version of primary and replicate ASE as well as
> repserver you are using (2) log transaction with sysadmin
> log_first_tran and output of the transaction using
> rs_helpexception (3) exact message from the repserver
> error log when DSI goes down
>
>
> Manish Negandhi
> [TeamSybase]
>
> <Eisen> wrote in message
> > news:4ec1cfa7.3f84.1681692777@sybase.com... Dear sirs
> > I've built a MSA system from A.a to B.b with DDL
> > replicate. It works fine for several months. But this
> > morning, when a guy alter a column datatype in a table
> > with default expression, there's a system warning raised
> > on the primary side --
> > "Warning: a default (object id 1033103740) is defined on
> > column 'v' being modified. Check the validity of the
> > default value after this ALTER TABLE operation."
> >
> > Since this is only a warning, so nobody cares. But
> > soon we found the DSI to B.b is down. And with checking
> > the content inside the RS queue, we found it's due to
> > the "alter table" statement. But to our surprise, we
> > found the datatype on the replicate side is already
> > modified... Does anyone know what happend and what to
> > prevent this? Thanks in advance for any help.
> >
> > Best Regards
> > Eisen
>
>


"Mark A. Parsons" <iron_horse Posted on 2011-11-15 12:28:59.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.rep-server
Subject: Re: Alter a column in table with default would cause DSI down
References: <4ec1ddf7$1@forums-1-dub> <4ec1fc57.5235.1681692777@sybase.com>
In-Reply-To: <4ec1fc57.5235.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ec25b0b$1@forums-1-dub>
Date: 15 Nov 2011 04:28:59 -0800
X-Trace: forums-1-dub 1321360139 10.22.241.152 (15 Nov 2011 04:28:59 -0800)
X-Original-Trace: 15 Nov 2011 04:28:59 -0800, vip152.sybase.com
Lines: 117
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9092
Article PK: 870253

By default the DSI connection is configured to shutdown when it sees most messages from the RDB; consider this a safety
issue (ie, 'better safe than sorry').

While this message is important (ie, you want to make sure the default still applies to the new datatype) you have the
ability to reconfigure the DSI with how it reacts to this specific message.

See the write-up for the 'assign action <action> for <errorclass> to <error#> [,<error#>]'.

There are several options for <action> which a) leaves the DSI up or brings it down, b) does (not) log the txn that
generated the error to the RSSD and/or c) discards or keeps the txn that generated the error.

Once you've researched the 'assign action' command, if you have questions just post back here with details of what you
want the DSI to do (ie, stay up or go down, dump anything to RSSD, keep/discard the txn, etc) when it sees this error
message again.

NOTE: To find the DSI's errorclass: log into RSSD and run 'rs_helpdb <RDS>, <RDB>'; this will generate a result set
containing the errorclass name for the DSI into RDS.RDB.

On 11/15/2011 00:44, Eisen wrote:
> Hi Manish
> Thanks for your reply. Here's is some information --
> A. Both primary and replicate site are ASE15.0.3 for AIX,
> only one RS server is Rep15.6 on AIX
> B. let me describe the issue more clearly --
> 1. Both on A.a and B.b there are table named test_wj1
> create table test_wj1(id int,v varchar(10) default ' ')
> 2. On A.a, someone execute "alter table test_wj1 modify v
> varchar(20)". There's a warning "Warning: a default (object
> id 1033103740) is defined on column 'v' being modified.
> Check the validity of the default value after this ALTER
> TABLE operation."
> 3. This time, we can find the DSI to B.b is down. and the RS
> errorlog is --
> "I. 2011/11/15 09:52:26. Message from server: Message:
> 13900, State 1, Severity 10 -- 'Warning: a default (object
> id 1900582828) is defined on column 'v' being modified.
> Check the validity of the default value after this ALTER
> TABLE operation.'.
> H. 2011/11/15 09:52:26. THREAD FATAL ERROR #5049 DSI
> EXEC(110(1) B.b) - dsiqmint.c(4305)
> The DSI thread for database 'B.b' is being shutdown.
> DSI received data server error #13900 which is mapped to
> STOP_REPLICATION. See logged data server errors for more
> information. The data server error was caused by output
> command #5 mapped from input command #1 of the failed
> transaction.
> I. 2011/11/15 09:52:26. The DSI thread for database 'B.b' is
> shutdown."
> 4. At this time, sysadmin log_first_tran,B,b, in the dump
> file --
> "I. 2011/11/15 13:43:13. DUMP OF FIRST 1 TRANSACTIONS FOR
> DATABASE 'B.b'
> I. 2011/11/15 13:43:13. begin transaction
> I. 2011/11/15 13:43:13. execute rs_update_lastcommit @origin
> = 109, @origin_qid=
> 0x00000000006f90ea0001d026002d0001d025002100009f9c00e1f2b60000000000000001
> , @secondary_qid =
> 0x000000000000000000000000000000000000000000000000000000000000000000000000
> , @origin_time = '20111115 13:42:39:113'
> I. 2011/11/15 13:43:13. if @@error<> 0 rollback
> transaction
> I. 2011/11/15 13:43:13. commit transaction
> I. 2011/11/15 13:43:13. END DUMP OF FIRST 1 TRANSACTIONS FOR
> DATABASE 'B.b'
> I. 2011/11/15 13:43:13. The first 1 transaction(s) for
> database 'sybwpmdr00.fawpmmo0xc' have been logged into the
> exceptions log. They have also been logged either in
> repserver log, or the location specified by sysadmin
> dump_file command.
> "
> 5. after "resume connection to B.b skip tran" and
> "rs_helpexception 267,v" we can see the content is -- "alter
> table test_wj1 modify v varchar(20)"
>
> Please have a look. Thanks
> Best Regards
> Eisen
>
>> Can you please post below details, it will help to
>> investigate the problem further
>>
>> (1) version of primary and replicate ASE as well as
>> repserver you are using (2) log transaction with sysadmin
>> log_first_tran and output of the transaction using
>> rs_helpexception (3) exact message from the repserver
>> error log when DSI goes down
>>
>>
>> Manish Negandhi
>> [TeamSybase]
>>
>> <Eisen> wrote in message
>>> news:4ec1cfa7.3f84.1681692777@sybase.com... Dear sirs
>>> I've built a MSA system from A.a to B.b with DDL
>>> replicate. It works fine for several months. But this
>>> morning, when a guy alter a column datatype in a table
>>> with default expression, there's a system warning raised
>>> on the primary side --
>>> "Warning: a default (object id 1033103740) is defined on
>>> column 'v' being modified. Check the validity of the
>>> default value after this ALTER TABLE operation."
>>>
>>> Since this is only a warning, so nobody cares. But
>>> soon we found the DSI to B.b is down. And with checking
>>> the content inside the RS queue, we found it's due to
>>> the "alter table" statement. But to our surprise, we
>>> found the datatype on the replicate side is already
>>> modified... Does anyone know what happend and what to
>>> prevent this? Thanks in advance for any help.
>>>
>>> Best Regards
>>> Eisen
>>
>>


Eisen Posted on 2011-11-16 03:09:18.0Z
Sender: 3c6d.4ec1c710.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.rep-server
Subject: Re: Alter a column in table with default would cause DSI down
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ec3295e.439c.1681692777@sybase.com>
References: <4ec25b0b$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Nov 2011 19:09:18 -0800
X-Trace: forums-1-dub 1321412958 10.22.241.41 (15 Nov 2011 19:09:18 -0800)
X-Original-Trace: 15 Nov 2011 19:09:18 -0800, 10.22.241.41
Lines: 138
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9093
Article PK: 870264

Hi Mark
Thanks for your reply. Now I got it. Right, the safety is
much more than performance. So I'd leave the action to
errorclass to the default one. And manually handle these DSI
issue in the future.
Thanks again for all

Best Regards
Eisen

> By default the DSI connection is configured to shutdown
> when it sees most messages from the RDB; consider this a
> safety issue (ie, 'better safe than sorry').
>
> While this message is important (ie, you want to make sure
> the default still applies to the new datatype) you have
> the ability to reconfigure the DSI with how it reacts to
> this specific message.
>
> See the write-up for the 'assign action <action> for
> <errorclass> to <error#> [,<error#>]'.
>
> There are several options for <action> which a) leaves the
> DSI up or brings it down, b) does (not) log the txn that
> generated the error to the RSSD and/or c) discards or
> keeps the txn that generated the error.
>
> Once you've researched the 'assign action' command, if you
> have questions just post back here with details of what
> you want the DSI to do (ie, stay up or go down, dump
> anything to RSSD, keep/discard the txn, etc) when it sees
> this error message again.
>
> NOTE: To find the DSI's errorclass: log into RSSD and run
> 'rs_helpdb <RDS>, <RDB>'; this will generate a result set
> containing the errorclass name for the DSI into RDS.RDB.
>
>
>
>
> On 11/15/2011 00:44, Eisen wrote:
> > Hi Manish
> > Thanks for your reply. Here's is some information --
> > A. Both primary and replicate site are ASE15.0.3 for AIX
> > , only one RS server is Rep15.6 on AIX
> > B. let me describe the issue more clearly --
> > 1. Both on A.a and B.b there are table named test_wj1
> > create table test_wj1(id int,v varchar(10) default '
> > ') 2. On A.a, someone execute "alter table test_wj1
> > modify v varchar(20)". There's a warning "Warning: a
> > default (object id 1033103740) is defined on column 'v'
> > being modified. Check the validity of the default value
> > after this ALTER TABLE operation."
> > 3. This time, we can find the DSI to B.b is down. and
> > the RS errorlog is --
> > "I. 2011/11/15 09:52:26. Message from server: Message:
> > 13900, State 1, Severity 10 -- 'Warning: a default
> > (object id 1900582828) is defined on column 'v' being
> > modified. Check the validity of the default value after
> > this ALTER TABLE operation.'.
> > H. 2011/11/15 09:52:26. THREAD FATAL ERROR #5049 DSI
> > EXEC(110(1) B.b) - dsiqmint.c(4305)
> > The DSI thread for database 'B.b' is being
> > shutdown. DSI received data server error #13900 which is
> > mapped to STOP_REPLICATION. See logged data server
> > errors for more information. The data server error was
> > caused by output command #5 mapped from input command #1
> > of the failed transaction.
> > I. 2011/11/15 09:52:26. The DSI thread for database
> > 'B.b' is shutdown."
> > 4. At this time, sysadmin log_first_tran,B,b, in the
> > dump file --
> > "I. 2011/11/15 13:43:13. DUMP OF FIRST 1 TRANSACTIONS
> > FOR DATABASE 'B.b'
> > I. 2011/11/15 13:43:13. begin transaction
> > I. 2011/11/15 13:43:13. execute rs_update_lastcommit
> > @origin = 109, @origin_qid=
> >
> 0x00000000006f90ea0001d026002d0001d025002100009f9c00e1f2b6
> > 0000000000000001 , @secondary_qid =
> >
> 0x00000000000000000000000000000000000000000000000000000000
> > 0000000000000000 , @origin_time = '20111115
> > 13:42:39:113' I. 2011/11/15 13:43:13. if @@error<> 0
> > rollback transaction
> > I. 2011/11/15 13:43:13. commit transaction
> > I. 2011/11/15 13:43:13. END DUMP OF FIRST 1 TRANSACTIONS
> > FOR DATABASE 'B.b'
> > I. 2011/11/15 13:43:13. The first 1 transaction(s) for
> > database 'sybwpmdr00.fawpmmo0xc' have been logged into
> > the exceptions log. They have also been logged either in
> > repserver log, or the location specified by sysadmin
> > dump_file command.
> > "
> > 5. after "resume connection to B.b skip tran" and
> > "rs_helpexception 267,v" we can see the content is --
> > "alter table test_wj1 modify v varchar(20)"
> >
> > Please have a look. Thanks
> > Best Regards
> > Eisen
> >
> >> Can you please post below details, it will help to
> >> investigate the problem further
> >>
> >> (1) version of primary and replicate ASE as well as
> >> repserver you are using (2) log transaction with
> sysadmin >> log_first_tran and output of the transaction
> using >> rs_helpexception (3) exact message from the
> repserver >> error log when DSI goes down
> >>
> >>
> >> Manish Negandhi
> >> [TeamSybase]
> >>
> >> <Eisen> wrote in message
> >>> news:4ec1cfa7.3f84.1681692777@sybase.com... Dear sirs
> >>> I've built a MSA system from A.a to B.b with DDL
> >>> replicate. It works fine for several months. But this
> >>> morning, when a guy alter a column datatype in a table
> >>> with default expression, there's a system warning
> raised >>> on the primary side --
> >>> "Warning: a default (object id 1033103740) is defined
> on >>> column 'v' being modified. Check the validity of
> the >>> default value after this ALTER TABLE operation."
> >>>
> >>> Since this is only a warning, so nobody cares. But
> >>> soon we found the DSI to B.b is down. And with
> checking >>> the content inside the RS queue, we found
> it's due to >>> the "alter table" statement. But to our
> surprise, we >>> found the datatype on the replicate side
> is already >>> modified... Does anyone know what happend
> and what to >>> prevent this? Thanks in advance for any
> help. >>>
> >>> Best Regards
> >>> Eisen
> >>
> >>