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.

Replication-Error 7137: How to avoid this??

7 posts in Replication Last posting was on 2007-08-03 06:57:58.0Z
Jörg Hoyer Posted on 2007-08-01 08:57:23.0Z
Date: Wed, 01 Aug 2007 10:57:23 +0200
From: =?ISO-8859-15?Q?J=F6rg_Hoyer?= <jo.hoyer@fernsteuergeraete.de>
Reply-To: jo.hoyer@fernsteuergeraete.de
Organization: FSG =?ISO-8859-15?Q?Fernsteuerger=E4te_GmbH?=
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
Newsgroups: Advantage.Replication
Subject: Replication-Error 7137: How to avoid this??
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: 80.228.161.251
Message-ID: <46b04983@solutions.advantagedatabase.com>
X-Trace: 1 Aug 2007 02:51:15 -0700, 80.228.161.251
Lines: 20
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!80.228.161.251
Xref: solutions.advantagedatabase.com Advantage.Replication:229
Article PK: 1134084

We created a bi-directional replication on many dictionaries between
server "a" and server "b". The row-identification-method is set to "all
columns" because we are not have primmary indices on the tables.

Now a lot of replication-errors occur in the error-log database. 95% of
this errors are error_code 7137 "Unexpected number of records modified
during a replication update". Some of the error contains at the
more_info field the information, that the sql statement affected a
different count of records

Both source and target of the replication was at the start of the
replication the same copy of the tables.


At moment, the main direction for the replication ist from server "A" to
server "B", so i think no conflicts are raising this behavior.
Why this error occur and how i can it avoid?

tia
J?rg


Peter Funk (ADS) Posted on 2007-08-01 15:22:47.0Z
Date: Wed, 1 Aug 2007 15:22:47 +0000 (UTC)
Message-ID: <864d0bcbe4c78c9a234a904964c@devzone.advantagedatabase.com>
From: Peter Funk (ADS) <pfunk@nospam.com>
Subject: Re: Replication-Error 7137: How to avoid this??
Newsgroups: Advantage.Replication
References: <46b04983@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Pro 1098.1
NNTP-Posting-Host: 10.24.38.185
X-Trace: 1 Aug 2007 09:17:51 -0700, 10.24.38.185
Lines: 39
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.185
Xref: solutions.advantagedatabase.com Advantage.Replication:232
Article PK: 1134088

Hello J?rg,
The 7137 page in our help documentation has some suggestions on how to deal
with 7137 errors:

http://devzone.advantagedatabase.com/dz/webhelp/Advantage8.1/mergedProjects/adserror/err7xxx/7137_unexpected_number_of_records_modified_during_a_replication_update.htm


7137 Unexpected number of records modified during a replication update

Problem: A replication update resulted in an unexpected number of records
updated at the target table. With each update at the source table, there
should be one corresponding update at the target.

Solution 1: Verify the identification columns for the table (in the publication
definition) uniquely identify the record. If the specified identification
columns do not uniquely identify the target record, multiple record updates
could occur.

Solution 2: Verify the record to update at the target does exist. The More_Info
field in the ads_err.adt error log should have information that helps identify
the record in question. You may want to check the "Log Data for Failed Replication
Updates" in the Advanced tab of the subscription properties dialog in Advantage
Data Architect. If this option is chosen, the non-memo data fields of the
record will be stored in the error log with the SQL statement that failed.

Solution 3: If the record does not exist at the target and, therefore, cannot
be updated successfully, then you can choose the "Ignore Replication Failures"
option in the Advanced tab of the subscription properties dialog in Advantage
Data Architect. This will cause Advantage to log the 7137 error for the failed
update but continue processing the replication queue. Alternatively, you
can delete the failed entry from the replication queue. If you are deleting
the entry and the failed entry is part of a transaction (TxnID is non-NULL
in the replication queue), then you should delete all entries in the transaction.

Regards,
Peter Funk
Advantage R&D


Jörg Hoyer Posted on 2007-08-01 17:20:41.0Z
Date: Wed, 01 Aug 2007 19:20:41 +0200
From: =?ISO-8859-1?Q?J=F6rg_Hoyer?= <jo.hoyer@fernsteuergeraete.de>
Reply-To: jo.hoyer@fernsteuergeraete.de
Organization: FSG =?ISO-8859-1?Q?Fernsteuerger=E4te_GmbH?=
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
Newsgroups: Advantage.Replication
Subject: Re: Replication-Error 7137: How to avoid this??
References: <46b04983@solutions.advantagedatabase.com> <864d0bcbe4c78c9a234a904964c@devzone.advantagedatabase.com>
In-Reply-To: <864d0bcbe4c78c9a234a904964c@devzone.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: 80.228.161.251
Message-ID: <46b0bf78@solutions.advantagedatabase.com>
X-Trace: 1 Aug 2007 11:14:32 -0700, 80.228.161.251
Lines: 47
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!80.228.161.251
Xref: solutions.advantagedatabase.com Advantage.Replication:233
Article PK: 1134087

Peter Funk (ADS) schrieb:

> Hello J?rg,
> The 7137 page in our help documentation has some suggestions on how to
> deal with 7137 errors:
>
> http://devzone.advantagedatabase.com/dz/webhelp/Advantage8.1/mergedProjects/adserror/err7xxx/7137_unexpected_number_of_records_modified_during_a_replication_update.htm
>
>
>
> 7137 Unexpected number of records modified during a replication update
>
> Problem: A replication update resulted in an unexpected number of
> records updated at the target table. With each update at the source
> table, there should be one corresponding update at the target.
>
> Solution 1: Verify the identification columns for the table (in the
> publication definition) uniquely identify the record. If the specified
> identification columns do not uniquely identify the target record,
> multiple record updates could occur.

With all fields as row-identifications, how can this error occurs?

> Solution 2: Verify the record to update at the target does exist. The
> More_Info field in the ads_err.adt error log should have information
> that helps identify the record in question. You may want to check the
> "Log Data for Failed Replication Updates" in the Advanced tab of the
> subscription properties dialog in Advantage Data Architect. If this
> option is chosen, the non-memo data fields of the record will be stored
> in the error log with the SQL statement that failed.
In the error-log many of this error without any information at the
more_info field. So how can i find out which record is affected?
("log data" is activated)

> Solution 3: If the record does not exist at the target and, therefore,
> cannot be updated successfully, then you can choose the "Ignore
> Replication Failures" option in the Advanced tab of the subscription
> properties dialog in Advantage Data Architect. This will cause Advantage
> to log the 7137 error for the failed update but continue processing the
> replication queue. Alternatively, you can delete the failed entry from
> the replication queue. If you are deleting the entry and the failed
> entry is part of a transaction (TxnID is non-NULL in the replication
> queue), then you should delete all entries in the transaction.
>
I choose now the "Ignore Replication Failures", but i think the affected
record are not replicated and so the tables in the source and target
dictionaries are different, isn't it?

J?rg


Peter Funk (ADS) Posted on 2007-08-01 19:52:34.0Z
Date: Wed, 1 Aug 2007 19:52:34 +0000 (UTC)
Message-ID: <864d0bcbe4d38c9a25a58bee45c@devzone.advantagedatabase.com>
From: Peter Funk (ADS) <pfunk@nospam.com>
Subject: Re: Replication-Error 7137: How to avoid this??
Newsgroups: Advantage.Replication
References: <46b0bf78@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Pro 1098.1
NNTP-Posting-Host: 10.24.38.185
X-Trace: 1 Aug 2007 13:47:38 -0700, 10.24.38.185
Lines: 47
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.185
Xref: solutions.advantagedatabase.com Advantage.Replication:234
Article PK: 1134089

> With all fields as row-identifications, how can this error occurs?

If the target table doesn't have the exact same record as was in the original
master table, then the SQL UPDATE query will fail to find the record. The
7137 error is logged because the SQL didn't update 1 record, it updated 0
records. Also it is possible that if multiple records in the target table
are the same as the original record in the master table, then more than 1
record will be updated - and that will cause the 7137 error as well.

> In the error-log many of this error without any information at the
> more_info field. So how can i find out which record is affected? ("log
> data" is activated)

Let me clarify something about the error log. For each single failure, there
are usually multiple records added to the error log (this is true for most
errors logged by Advantage). For example, I just caused a replication update
to fail and 5 records were added to the error log, each with the 7137 error
code. The first two records contain useful information in the more_info
field, whereas the other three records have empty more_info fields.

The first record (of the group of 5) in the log with the 7137 error should
explain why the 7137 was logged in the more_info field. For example, in
my log the more_info field has "The SQL statement affected 0 records instead
of 1 record.". The second record then contains in the more_info field the
subscription name, table name, record number (of the master table), and the
SQL statement text including each parameter's data. That information, along
with the target table, should tell you why the SQL UPDATE statement failed
to update just 1 record.

> I choose now the "Ignore Replication Failures", but i think the
> affected record are not replicated and so the tables in the source and
> target dictionaries are different, isn't it?

Yes, if you choose to ignore replication failures then it is likely that
the tables would not stay identical. If you need to keep the tables identical,
you shouldn't use this option but instead use a primary key for row identification
and then implement conflict handling logic with an ON CONFLICT trigger.
This way you would have unique records (with respect to the primary key)
and if the target record wasn't exactly the same as the source record (what
is likely the problem in your current scenario) you can handle the situation
with an ON CONFLICT trigger.

Regards,
Peter Funk
Advantage R&D


Jörg Hoyer Posted on 2007-08-02 08:26:28.0Z
Date: Thu, 02 Aug 2007 10:26:28 +0200
From: =?ISO-8859-1?Q?J=F6rg_Hoyer?= <jo.hoyer@fernsteuergeraete.de>
Reply-To: jo.hoyer@fernsteuergeraete.de
Organization: FSG =?ISO-8859-1?Q?Fernsteuerger=E4te_GmbH?=
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
Newsgroups: Advantage.Replication
Subject: Re: Replication-Error 7137: How to avoid this??
References: <46b0bf78@solutions.advantagedatabase.com> <864d0bcbe4d38c9a25a58bee45c@devzone.advantagedatabase.com>
In-Reply-To: <864d0bcbe4d38c9a25a58bee45c@devzone.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: 80.228.160.34
Message-ID: <46b193c0@solutions.advantagedatabase.com>
X-Trace: 2 Aug 2007 02:20:16 -0700, 80.228.160.34
Lines: 39
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!80.228.160.34
Xref: solutions.advantagedatabase.com Advantage.Replication:235
Article PK: 1134090

Peter Funk (ADS) schrieb:

>> With all fields as row-identifications, how can this error occurs?
>
> If the target table doesn't have the exact same record as was in the
> original master table, then the SQL UPDATE query will fail to find the
> record.

The table starts identical, so why can't find the query the correct record?

> The 7137 error is logged because the SQL didn't update 1
> record, it updated 0 records. Also it is possible that if multiple
> records in the target table are the same as the original record in the
> master table, then more than 1 record will be updated - and that will
> cause the 7137 error as well.
Okay, in my enviroment can this only happend when all fields have the
same content?

> Let me clarify something about the error log. For each single failure,
> there are usually multiple records added to the error log (this is true
> for most errors logged by Advantage). For example, I just caused a
> replication update to fail and 5 records were added to the error log,
> each with the 7137 error code. The first two records contain useful
> information in the more_info field, whereas the other three records have
> empty more_info fields.
Thanx for the clarifying.

>> I choose now the "Ignore Replication Failures", but i think the
>> affected record are not replicated and so the tables in the source and
>> target dictionaries are different, isn't it?
>
> Yes, if you choose to ignore replication failures then it is likely that
> the tables would not stay identical. If you need to keep the tables
> identical, you shouldn't use this option but instead use a primary key
> for row identification and then implement conflict handling logic with
> an ON CONFLICT trigger.
Must i have unique primary keys?
How do i implement an ON CONFLICT trigger? Any examples out there?

thx for helping
J?rg


Peter Funk (ADS) Posted on 2007-08-02 14:51:09.0Z
Date: Thu, 2 Aug 2007 14:51:09 +0000 (UTC)
Message-ID: <864d0bcbe4e38c9a2f967967b86@devzone.advantagedatabase.com>
From: Peter Funk (ADS) <pfunk@nospam.com>
Subject: Re: Replication-Error 7137: How to avoid this??
Newsgroups: Advantage.Replication
References: <46b193c0@solutions.advantagedatabase.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Pro 1098.1
NNTP-Posting-Host: 10.24.38.185
X-Trace: 2 Aug 2007 08:46:12 -0700, 10.24.38.185
Lines: 45
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.185
Xref: solutions.advantagedatabase.com Advantage.Replication:236
Article PK: 1134091


> The table starts identical, so why can't find the query the correct
> record?

If the tables are identical, then it should have no problem finding the target
record. Could it be that the UPDATE query is finding more than one identical
record? If the UPDATE query updates more than one record, it will cause
the 7137 error just like if it updated zero records.

> Okay, in my enviroment can this only happend when all fields have the
> same content?

Correct. Since you're using the "all columns" identification method, all
records in the target table that have the same field content as the source
record will be udpated.

> Must i have unique primary keys?

The goal of replication is to update the same record (and only the same record)
on the target table as was updated on the source table. For that to work
correctly you must be able to uniqely identify the target record. Whether
you're using the "all columns" or the primary key method, your records must
be unique in some way.

> How do i implement an ON CONFLICT trigger? Any examples out there?

ON CONFLICT triggers can be implemented as a function in a DLL (Win32 or
Linux), .NET assembly, COM object, or as an SQL script. Personally I think
the SQL script option is the easiest to implement and maintain if you're
comfortable using SQL. For the other module options we have templates for
Delphi and .NET to get you started. Also we have tutorials in the help book.
Finally, we also have some online seminars you can view which talk about
replication and triggers.

Online Seminars (the replication seminar covers ON CONFLICT triggers, there's
also a triggers seminar just about triggers):
http://www.advantagedatabase.com/web/download_listing.aspx?key=AB6A67F23F42116B2AAF9E2E742230FF&filter=38

Code Central Samples (including 5 trigger samples):
http://devzone.advantagedatabase.com/dz/content.aspx?key=3

Regards,
Peter Funk
Advantage R&D


Jörg Hoyer Posted on 2007-08-03 06:57:58.0Z
Date: Fri, 03 Aug 2007 08:57:58 +0200
From: =?ISO-8859-1?Q?J=F6rg_Hoyer?= <jo.hoyer@fernsteuergeraete.de>
Reply-To: jo.hoyer@fernsteuergeraete.de
Organization: FSG =?ISO-8859-1?Q?Fernsteuerger=E4te_GmbH?=
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
Newsgroups: Advantage.Replication
Subject: Re: Replication-Error 7137: How to avoid this??
References: <46b193c0@solutions.advantagedatabase.com> <864d0bcbe4e38c9a2f967967b86@devzone.advantagedatabase.com>
In-Reply-To: <864d0bcbe4e38c9a2f967967b86@devzone.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: 80.228.160.244
Message-ID: <46b2d082@solutions.advantagedatabase.com>
X-Trace: 3 Aug 2007 00:51:46 -0700, 80.228.160.244
Lines: 40
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!80.228.160.244
Xref: solutions.advantagedatabase.com Advantage.Replication:238
Article PK: 1134093

Peter Funk (ADS) schrieb:

>> The table starts identical, so why can't find the query the correct
>> record?
>
> If the tables are identical, then it should have no problem finding the
> target record. Could it be that the UPDATE query is finding more than
> one identical record? If the UPDATE query updates more than one record,
> it will cause the 7137 error just like if it updated zero records.

Both kind of errors occurs. Sometimes it found more than one records,
sometime it found zero record.

>
>> Okay, in my enviroment can this only happend when all fields have the
>> same content?
>
> Correct. Since you're using the "all columns" identification method,
> all records in the target table that have the same field content as the
> source record will be udpated.

>
>> Must i have unique primary keys?
>
> The goal of replication is to update the same record (and only the same
> record) on the target table as was updated on the source table. For
> that to work correctly you must be able to uniqely identify the target
> record. Whether you're using the "all columns" or the primary key
> method, your records must be unique in some way.
Okay, i'll reconsider my database-design in this manner.

> Online Seminars (the replication seminar covers ON CONFLICT triggers,
> there's also a triggers seminar just about triggers):
> http://www.advantagedatabase.com/web/download_listing.aspx?key=AB6A67F23F42116B2AAF9E2E742230FF&filter=38
>
>
> Code Central Samples (including 5 trigger samples):
> http://devzone.advantagedatabase.com/dz/content.aspx?key=3
Thx for the links, i'll have a look at this.

Regards
J?rg