Error reporting and conflict resolution in SQL Remote

Chapter 28. SQL Remote Administration
Error reporting and conflict resolution in SQL Remote

A replication error occurs when a replication cannot be carried out because of some change to the database. If an INSERT statement is received at a database, but a row with the same primary key values has already been inserted, then the INSERT will fail. When a SQL operation fails, it generates an error message from the database. The other operations in the message are still applied.

Primary key errors must be avoided in SQL Remote setups. These errors can be avoided by proper design of publications. Update conflicts can be resolved by SQL Remote using a RESOLVE UPDATE trigger. Properly handled update conflicts are not a problem: they are an integral part of the replication system.

Error reporting and conflict resolution

Errors are reported and conflicts resolved by SQL Remote as follows:

SQL statement failures INSERT errors (two users at different databases insert an identical primary key) and other failures of operations contained in replication messages, are reported in the DBREMOTE output.

UPDATE conflicts SQL Anywhere provides a special form of trigger, the RESOLVE UPDATE trigger, which is triggered by UPDATE conflicts at a consolidated database. In this way, you can design appropriate resolutions based on the situation. If an UPDATE conflict occurs at the consolidated database, any RESOLVE UPDATE triggers are fired, then the UPDATE is applied. Both the RESOLVE UPDATE trigger actions and the UPDATE itself are replicated to other databases. If an update conflict is detected at a remote database, no RESOLVE UPDATE triggers are fired, and the UPDATE is carried out. Examples are provided in the following section.

Error reporting in the Message Agent output

The Message Agent sends log output to a window or a log file recording its operation. By default, log output is sent to the window only; the -o command-line option sends output to a log file as well.

The Message Agent log includes the following:

Listing of messages applied.
Listing of failed SQL statements.
Listing of other errors.

In addition, UPDATE conflicts can be inserted into a table by a RESOLVE UPDATE trigger, and reports from that table can be used to track such conflicts.

There may be exceptional cases where you wish to allow an error encountered by the Message Agent when applying SQL statements to go unreported. This may arise when you know the conditions under which the error occurs and are sure that it does not produce inconsistent data and that its consequences can safely be ignored.

To allow errors to go unreported, you can create a BEFORE trigger on the action that will cause the known error. The trigger should signal the REMOTE_STATEMENT_FAILED SQLSTATE or SQLCODE value. For example, if you wish to not report failed INSERT statements on a table because of a missing referenced column, you could create a BEFORE INSERT trigger that signals the REMOTE_STATEMENT_FAILED SQLSTATE when the referenced column does not exist. The INSERT statement fails, but the failure is not reported in the Message Agent log.

Using the REPLICATION_ERROR option

With the REPLICATION_ERROR database option, you can specify a stored procedure to be called by the Message Agent when a SQL error occurs. By default no procedure is called.

The procedure must have a single argument of type CHAR, VARCHAR, or LONG VARCHAR. The procedure is called once with the SQL error message and once with the SQL statement that causes the eror.

While the option allows you to track and monitor SQL errors in replication, you must still design them out of your setup: this option is not intended to resolve such errors.

For example, the procedure could insert the errors into a table with the current time and remote user ID, and this information can then replicate back to the consolidated database. An application at the consolidated database can create a report or send e-mail to an administrator when errors show up.

For information on setting the REPLICATION_ERROR option, see "SET OPTION statement".

RESOLVE UPDATE triggers

An UPDATE conflict is detected by the database engine as a failure of the VERIFY clause values to match the rows in the database.

When an UPDATE conflict is detected, the database engine takes two actions:

Any RESOLVE UPDATE triggers are fired.
The UPDATE is applied.

UPDATE statements are applied even if the VERIFY clause values do not match, whether or not there is a RESOLVE UPDATE trigger.

This section describes RESOLVE UPDATE triggers. These triggers are fired by the failure of values in the VERIFY clause of an UPDATE statement to match the values in the database before the update. An UPDATE statement with a VERIFY clause takes the following form:

     UPDATE table-list
     SET column-name = expression, ...
     [ FROM table-list ]
     [ VERIFY (column-name, ...)
         VALUES ( expression, ...) ]
     [ WHERE search-condition ]

The VERIFY clause compares the values of specified columns to a set of expected values, which are the values that were present in the publisher database when the UPDATE statement was applied there.

The verify clause is useful only for single-row updates. However, even multi-row update statements entered at a database are replicated as a set of single-row updates by the Message Agent, so this imposes no constraints on client applications.

The syntax for a RESOLVE UPDATE trigger is as follows:

     CREATE TRIGGER trigger-name
         RESOLVE UPDATE
         OF column-name ON table-name
     [ REFERENCING [ OLD AS old_val ]
     [ NEW AS new_val ]
     [ REMOTE AS remote_val ]
     FOR EACH ROW
     BEGIN
         ...
     END

RESOLVE UPDATE triggers fire before each row is updated. The REFERENCING clause allows access to the values in the row of the table to be updated (OLD), to the values the row is to be updated to (NEW) and to the rows that should be present according to the VERIFY clause (REMOTE). Only columns present in the VERIFY clause can be referenced in the REMOTE AS clause; other columns produce a "row not found" error.

Using the VERIFY_ALL_COLUMNS option

The database option VERIFY_ALL_COLUMNS is OFF by default. If it is set to ON, all columns are verified on replicated updates, and a RESOLVE UPDATE trigger is fired whenever any column is different. Setting this option to ON makes messages bigger, because more information is sent for each UPDATE.

If this option is set at the consolidated database before remote databases are extracted, it will be set at the remote databases also.

You can set the VERIFY_ALL_COLUMNS option either for the PUBLIC group or just for the user contained in the Message Agent connection string.

Using the CURRENT REMOTE USER special constant

The CURRENT REMOTE USER special constant holds the user ID of the remote user sending the message. This can be used in RESOLVE UPDATE triggers that place reports of conflicts into a table, to identify the user producing a conflict.

UPDATE conflict resolution examples

This section describes some ways of using RESOLVE UPDATE triggers to handle UPDATE conflicts.

Example: inventory control

The products table of the sample database has a quantity column holding the number of each product left in stock. An update to this column will typically deplete the quantity in stock or, if a new shipment is brought in, add to it.

A sales rep at a remote database enters an order, depleting the stock of small tank top tee shirts (product ID 300) by five, from 28 to 23, and enters this in on her database. Meanwhile, before this update is replicated to the consolidated database, a new shipment of tee shirts comes in, and the warehouse enters the shipment, adding 40 to the quantity column to make it 68.

The warehouse entry gets added to the database: the quantity column now shows there are 68 small tank-top tee shirts in stock. When the update from the sales representative arrives, it causes a conflict-SQL Anywhere detects that the update is from 28 to 23, but that the current value of the column is 68.

A suitable RESOLVE UPDATE trigger for this situation would add the increments from the two updates. For example:

     CREATE TRIGGER resolve_quantity
     RESOLVE UPDATE OF quantity
     ON "DBA".product
     REFERENCING OLD AS old_name
     NEW AS new_name
     REMOTE AS remote_name
     FOR EACH ROW
     BEGIN
         SET new_name.quantity =    new_name.quantity
                                         + old_name.quantity
                                         - remote_name.quantity
     END

This trigger adds the difference between the old value in the consolidated database (68) and the old value in the remote database when the original UPDATE was executed (28) to the new value being sent, before the UPDATE is implemented. Thus, new_val.quantity becomes 63 (= 23 + 68 - 28), and this value is entered into the quantity column.

Consistency is maintained at the remote database as follows:

The original remote UPDATE changed the value from 28 to 23.
The warehouse's entry is replicated to the remote database, but fails as the old value is not what was expected.
The changes made by the RESOLVE UPDATE trigger are replicated to the remote database.

Example: resolving date conflicts

Suppose the contact table in the sample database had a date column holding the most recent contact with each individual. Update conflicts on this column should be resolved in favor of the most recent date.

One representative talks with the contact on a Friday, but does not upload his changes to the consolidated database until the next Monday. Meanwhile, a second representative meets the contact on the Saturday, and updates her changes that evening.

There is no conflict when the Saturday UPDATE is replicated to the consolidated database, but when the Monday UPDATE arrives it finds the row already changed.

The following RESOLVE UPDATE trigger chooses the most recent of the two new values and enters it in the database.

     CREATE TRIGGER contact_date RESOLVE UPDATE
     ON contact
     REFERENCING OLD AS old_name
     NEW AS new_name
     FOR EACH ROW
     BEGIN
         IF new_name.contact_date <
                 old_name.contact_date THEN
             SET new_name.contact_date
                     = old_name.contact_date
         END IF
     END

If the value being updated is later than the value that would replace it, the new value is reset to leave the entry unchanged.