Cause of duplicate row and foreign key constraint errors in Rep Server?
lorraineoneill
I am new to replication and sybase. I have configured
replication using one warm standby ASE server. I am
wondering what is causing this errors at the rep server - as
both databases are in sync. No errors are reported in my
primary server so why are they occuring in the rep server?
E. 2009/02/19 14:52:19. ERROR #1028 DSI EXEC(104(1)
mscrown.msdata) - dsiqmint.c(3249)
Message from server: Message: 2601, State 6,
Severity 14 -- 'Attempt to insert duplicate key row in
object 'file_version' with unique index 'idx_f
ile_ver_file_id'
'.
I. 2009/02/19 14:52:19. Message from server: Message: 3621,
State 0, Severity 10 -- 'Command has been aborted.
'.
H. 2009/02/19 14:52:19. THREAD FATAL ERROR #5049 DSI
EXEC(104(1) mscrown.msdata) - dsiqmint.c(3256)
The DSI thread for database 'mscrown.msdata' is
being shutdown. DSI received data server error #2601 which
is mapped to STOP_REPLICATION. See logg
ed data server errors for more information. The data server
error was caused by output command #1 mapped from input
command #3 of the failed transaction.
I. 2009/02/19 14:52:19. The DSI thread for database
'mscrown.msdata' is shutdown.
How can I configure my database so these errors no longer
occur?
If there isnt a complaint in my primary about duplicate rows
I dont understand how these exist in the rep server?
I did think about create error_classes but is this just
avoiding the issue?
Thanks a million,
Lorraine
Manish Negandhi
> Thanks a million,
>
> Lorraine
There are various reasons why you see "duplicate key" errors at
replicate but not at primary
(1) check if you have same type of indexes in primary and replicate
(2) check if you are replicating identity columns which is causing
"duplicate key" error
(3) check if you have triggers enabled at replicate side
(4) check if the data is out of sync at replicate
You stated that data is in sync in primary and replicate therefore
check first 3 points.Whenever you see duplicate key issue, go to
replicate replication server and execute sysadmin log_first_tran , go
to ASE server hosting RSSD and check exception log by issuing
sp_helpexception id,v and see what is causing duplicate key issue
> I did think about create error_classes but is this just avoiding the issue?
That is right, its not a good idea to create error_classes to avoid
duplicates, it might cause replicate database to go out of sync
-HTH
Manish Negandhi
[TeamSybase]
lorraineoneill
Before I configured rep server though, I dumped my primary
db onto my secondary, therefore it contains the same schema
(tiggers, primary keys) etc as the primary.
Could you explain to me then how these errors occur in Rep
Server? I dont understand how/why they happen.
With rep server troubleshooting, it says dump the error to
the exception logs, I'm not a dba either so its all very
confusing for me.
Cheers
Leonid Gvirtz
Hi
RS exception log is a set of 3 tables in RSSD which hold the information
about problematic transactions. For example, when you issue "resume
connection to ds.db skip transaction", the content of the skipped
transaction is written to RS exception log in RSSD and you can analyze
it afterwards. If your replication is stopped because of "duplicate key"
problem, you can write the content of the transaction to the exception
log by "sysadmin log_first_tran" command and then analyze it.
Since RS exception log is just a set of tables in RSSD, it can be
accessed via regular SQL. See the structure of tables and some examples
in "Handling errors and exceptions" chapter in Administration Guide.
If you have triggers in your database, verify that dsi_keep_triggers
parameter for your replicate connection is set to 'off', so triggers in
the replicated database will not be fired by the replication. It is 'on'
by default, but should be set to 'off' in most of the cases.
Hope it helps
Leonid Gvirtz
lorraineoneill
Thanks for the advice.
I have 3 physical connections. It says in the admin guide
By default, the standby DSI thread executes a set triggers
off Adaptive Server
command when it logs in to a standby database. This prevents
Adaptive Server
from firing triggers for the replicated transactions,
thereby preventing
duplicate updates in the standby database.
You can alter the default behavior by using the alter
connection command to
configure a connection to fire or not fire triggers. To do
this, set the
dsi_keep_triggers configuration parameter to “on” or
“off.” The default
dsi_keep_triggers setting for all connections except standby
databases is “on.”
Ive ran admin who, dsi and my connection to primary db is
on, connection to rep server is on and connection to standby
is off. Are you recommending to set the triggers off for the
rep server connection?
Thanks
Mark A. Parsons
Lorraine O Neill wrote:
> By default, the standby DSI thread executes a set triggers
> off Adaptive Server command when it logs in to a standby database.
Yes, in a warm standby configuration the DSI connection will (by default) issue a 'set triggers off' when it connects to
the replicate database. This keeps triggers from firing in the replicate database.
> Ive ran admin who, dsi and my connection to primary db is
> on, connection to rep server is on and connection to standby
> is off. Are you recommending to set the triggers off for the
> rep server connection?
I think what Leonid is suggesting is that you verify that the DSI connection has *NOT* been configured to enable
triggers (ie, the opposite of the default behaviour).
Can you log into the RSSD, run the following query and post back here with the results:
========================
select d.dsname + '.' + d.dbname as Identifier,
rc.optionname as Config_Name,
substring(rc.charvalue,1,30) as Config_Value,
case when rt.status = 1
then rt.cv
else substring(rc.charvalue,1,30)
end Run_Value
from rs_config rc,
rs_tvalues rt,
rs_databases d
where rc.optionname = rt.name
and rt.type = "CF"
and rc.objid != 0
and d.dbid = hextoint(bintostr(substring(rc.objid,1,4)))
order by Identifier,
Config_Name
========================
This should tell us if the DSI connection into mscrown.msdata has been configured to enable or disable the firing of
triggers.
Leonid Gvirtz
Hi
I missed your first post where you said that your are using warm
standby, sorry. In warm standby configuration the default of
dsi_keep_triggers is off and usually should not be changed. Since it is
already "off" for your connection to standby - nothing need to be done
in this field.
I'm not sure what "connection to rep server" is, I guess that you mean
the connection to RSSD. If so, you should not touch it, it is definitely
not related to your problem with duplicate keys.
Mark A. Parsons
When a DSI connection goes down (as in your case due to a duplicate key error), normal procedure is to grab the text of
the SQL being submitted by the DSI (ie, the actual query(s) generating the errors), and troubleshoot based on what you find.
There are a couple ways to grab the questionable SQL text, with the easiest being to dump the SQL text into the
repserver's RSSD database.
In the repserver where the DSI connection is down:
sysadmin log_first_tran,mscrown,msdata
In the repserver's RSSD:
rs_helpexception
From the listed output find the most recent entry for mscrown.msdata, note the numeric value in the 'Xact ID' column,
and run:
rs_helpexception <Xact_ID_value>,'v'
The output from this command contains internal schtuff interspersed with the actual SQL text. You'll want to pull out
the SQL text and start troubleshooting with this.
For duplicate key errors you want to find out why the SQL text is generating a duplicate key error in the replicate
database. With the current contents of the replicate database, the contents of the SQL statement, and an understanding
of how the replicate database is accessed by various users/applications ... hopefully you'll be able to piece together a
scenario that would explain the duplicate key error, eg:
- someone directly modified the data in the replicate database
or
- triggers are firing in the replicate database (which it shouldn't be doing for a warm standby configuration)
> With rep server troubleshooting, it says dump the error to
> the exception logs, I'm not a dba either so its all very
> confusing for me.
Unfortunately the Replication Server product is a formidable product, ie, unless you've had some training on the product
(and preferably experience as an ASE DBA to boot) you'll probably find repserver management to be a painful task.
Chuck
sure though, that she first focuses and completes your first
recommended steps... She should perform and complete the
running of sysadmin log_fist_tran and then analyze the SQL
being ran via rs_helpexception. Then she should analyze the
targeted tables in the SQL statement (and data in them).
Seeing if she can eyeball the source of the problem (via
initial analysis). For example, records that exist in the
replica that do not exist in the primary, etc.
Once those basic steps are completed, then you can go into a
more advanced analysis / troubleshooting path (i.e.
investigating whether Rep server defaults have been changed,
etc.). Not to mention, confirm that there are even any
triggers running agains these tables in the first place.
Chuck
Thank you for your posting and breaking it down in such
great detail. I think your recommendations is always the
best place to start when investigating a duplicate key
error.
I thing both you and Manish are giving her great advice on
how to best proceed (i.e. first steps for troubleshooting
this problem).
I have asked my team to follow this posting and learn from
your great advice and postings ( postings from yourself,
Manish, and everyone participating).
Many thaks!
Chuck