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
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
Peter Funk (ADS) schrieb:
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
> 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.
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
Peter Funk (ADS) schrieb:
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
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
Peter Funk (ADS) schrieb:
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