User's Guide Part III. Using SQL Anywhere Chapter 28. SQL Remote AdministrationSQL Remote allows data modification at any site in the system. This is a powerful and useful feature that some replication systems do not have. It does, however, raise the possibility of replication errors. For example, a user at one remote database could delete a row, and a remote user at another site update the same row. When these operations are replicated, if the DELETE arrives at the consolidated database first, the UPDATE will fail.
Proper design of publications can reduce the overhead required by the system and reduce the number of data modification errors and conflicts in the system.
SQL Remote can detect and resolve replication conflicts as long as the data being replicated conforms to certain good practices. Some important kinds of replication error must be avoided altogether with proper replication design. This section describes how to design publications so that important classes of replication errors are avoided.
A full discussion of conflict handling and error reporting is given in "Error reporting and conflict resolution in SQL Remote".
SQL Remote is designed to allow databases to be updated at many different sites. With a little planning, this can be made to work with few conflicts, but if some basic rules are not followed, replication errors can be a frequent and damaging occurrence. This section describes the kinds of conflict and error that can occur in a replication setup; subsequent sections describe how you can design your publications to avoid errors and manage conflicts.
There is one class of error that can and must be designed out of your SQL Remote publications to ensure that they do not occur. These are primary key errors. Replication errors and conflicts fall into the following categories:
Failures because of an INSERT Two users INSERT a row using the same primary key values. The second INSERT to reach a given database in the replication system will fail. This is an example of a primary key error, and must be avoided by proper design.
Failures because of a DELETE A user DELETES a row (that is, the row with a given primary key value). A second user UPDATES or DELETES the same row at another site. The second user's operation will not find the row, so the second statement fails.
Updates of primary keys An update of a primary key has similar effects to a DELETE followed by an INSERT, as far as conflicts are concerned. Any DELETE or UPDATE that refers to the old primary key values will not find the row, and any INSERT that refers to the new primary key will fail. This is an example of a primary key conflict, and must be avoided by proper design.
UPDATE conflicts A user updates a row. A second user UPDATES the same row at another site. The second user's operation succeeds, and SQL Remote allows a trigger to be fired to resolve these conflicts in a way that makes sense for the data being changed. These RESOLVE triggers are described in "RESOLVE UPDATE triggers". Properly handled UPDATE conflicts are not a problem in SQL Remote.
Referential integrity errors If a column containing a foreign key is included in a publication, but the associated primary key is not included, the extraction utility leaves the foreign key definition out of the remote database so that INSERTS at the remote database will not fail.
Also, referential integrity errors can occur when a primary table has a SUBSCRIBE BY expression and the associated foreign table does not: rows from the foreign table may be replicated, but the rows from the primary table may be excluded from the publication.
For information about designing to avoid such conflicts, see "Designing to avoid referential integrity errors".
Not all errors and conflicts can be designed out of a replication system. In particular, UPDATE conflicts will occur in many installations. SQL Remote allows appropriate resolution of UPDATE conflicts as part of the regular operation of a SQL Remote setup, using triggers.
For information about how SQL Remote handles conflicts as they occur, see "Error reporting and conflict resolution in SQL Remote".
Primary key errors can and must be designed out of publications. This section describes how to design publications so that primary key errors do not occur.
Primary key errors can be avoided if primary keys of tables that may be inserted at more than one site are guaranteed to be unique. This can be achieved by including a column identifying the site at which the INSERT is being made.
For example, a column with a default value of CURRENT PUBLISHER can be used. CURRENT PUBLISHER is the user ID that has been granted PUBLISH permissions on the current database. If such a column is included in the primary key, a row cannot be inserted at more than one site and primary key conflicts can be avoided.
Primary key errors are not corrected in SQL Remote, and designing them out of your publications is essential. Further, SQL Remote applications should not update primary key values.
UPDATE conflicts occur when two users at different databases update the same column, that is not a primary key column, in a row.
Consider a publication that includes the contacttable from the sample database. This table includes columns holding information about company contacts, such as the first and last name, the address, and phone and fax numbers.
If one user updates the street column for a particular contact, and another user updates the phone number, there is no conflict, and both updates will be replicated throughout the setup. Should you wish to detect and report such events, you can do so using the VERIFY_ALL_COLUMNS option, described in "RESOLVE UPDATE triggers".
If two users update the street column for a particular contact, then a conflict will occur, as SQL Remote checks that the old values in the database where an update originates made match the values in the database where a replication is being applied.
SQL Remote allows you to define triggers to handle UPDATE conflicts so that data can be altered reliably at more than a single site; these conflict reporting and handling procedures are discussed in "Error reporting and conflict resolution in SQL Remote". When an UPDATE conflict is detected at a consolidated database, the following sequence of events takes place.
At the end of the process, the data is consistent throughout the setup.
UPDATE conflicts cannot happen where data is shared for reading, but each row (as identified by its primary key) is updated at only one site.
The tables in a relational database are related through foreign key references. The referential integrity constraints applied as a consequence of these references ensure that the database remains consistent. If you wish to replicate only a part of a database, there are potential problems with the referential integrity of the replicated database.
By paying attention to referential integrity issues while designing publications you can avoid these problems. This section describes some of the more common integrity problems and suggests ways to avoid them.
The sales publication described in "Setting up publications" includes the sales_order table:
CREATE PUBLICATION pub_sales (TABLE customer,TABLE sales_order,TABLE sales_order_items,TABLE product)
The sales_order table has a foreign key to the employee table. The id of the sales rep is a foreign key in the sales_order table referencing the primary key of the employee table. However, the employee table is not included in the publication.
If the publication is created in this manner, new sales orders would fail to replicate unless the remote database has the foreign key reference removed from the sales_order table.
If you use the extraction utility to create the remote databases, the foreign key reference is automatically excluded from the remote database, and this problem is avoided. However, there is no constraint in the database to prevent an invalid value from being inserted into the sales_rep_id column of the sales_order table, and if this happens the INSERT will fail at the consolidated database. To avoid this problem, you can include the employee table (or at least its primary key) in the publication.
The pub_orders publication described in "Publishing a subset of rows using a SUBSCRIBE BY expression" includes the rows containing their own sales orders, for each sales rep:
CREATE PUBLICATION pub_orders (TABLE sales_order SUBSCRIBE BY sales_rep)
The following publication also includes all line items from these orders, but will cause problems in replication.
CREATE PUBLICATION pub_orders (TABLE sales_order SUBSCRIBE BY sales_rep,TABLE sales_order_items)
The primary key in the sales_order table is id, an order ID number. The sales_order_items table includes the sales order ID as a foreign key to the sales_order table. Although, for example, Samuel Singer receives only his own rows from the sales order table (sales_rep = 856), he receives all the rows of the sales_order_items table.
When another sales rep adds an order, the new sales_order_items entries are replicated to Samuel Singer. The replication fails as Samuel Singer's database does not have a primary key corresponding to the foreign key of the new sales_order_items entries.
One way of of avoiding the problem is to include the sales_rep column in the sales_order_items table. In this case, triggers can be used to fill in the proper values of the user ID in the sales_order_items table. An example is given in the SALESPUB.SQL script included in your installation directory.
Under certain restricted conditions only, you could also use a subquery in a SUBSCRIBE BY expression to solve the problem.
For information on using subqueries in SUBSCRIBE BY expressions, see the section "Using subqueries in publications".
Actions performed by triggers are not replicated: triggers that exist at one database in a SQL Remote setup are assumed by the replication procedure to exist at other databases in the setup. When an action that fires a trigger at the consolidated database is replicated at the replicate site, the trigger is automatically fired. By default, the database extraction utility replicates the trigger definitions, so that they are in place at the remote database also.
If a publication includes only a subset of a database, a trigger at the consolidated database may refer to tables or rows that are present at the consolidated database, but not at the remote databases. You can design your triggers to avoid such errors by making actions of the trigger conditional using an IF statement. The following list suggests some ways in which triggers can be designed to work on consolidated and remote databases.
The RESOLVE UPDATE trigger is a special trigger type for the resolution of UPDATE conflicts, and is discussed in the section "UPDATE conflict resolution examples". The actions of RESOLVE UPDATE triggers are replicated to remote databases, including the database that caused the conflict.
Subqueries can be used in publications, but require some extra work in order to maintain the integrity of subscriptions.
If no special measures are taken, rows in tables that use subqueries will not be removed from or added to subscriptions properly. This is illustrated by example,and the solution to this problem is then outlined.
The sample database includes a sales_order table and a sales_order_items table. The sales_order_items table has the order id number as part of its primary key, and also as a foreign key to the sales_order table. The sales_order table contains a region column that is not present in the sales_order_items table.
A publication that replicated the rows in sales_order to subscribers by region may include the following article:
CREATE PUBLICATION SalesPub (TABLE sales_order SUBSCRIBE BY region...)
If this publication was to replicate the rows in the sales_order_items table also, only those items corresponding to orders for the region should be replicated. The following statement shows a subquery that accomplishes this.
CREATE PUBLICATION SalesPub (TABLE sales_order SUBSCRIBE BY regionTABLE sales_order_items SUBSCRIBE BY( SELECT regionFROM sales_orderWHERE sales_order.id = sales_order_items.id)...)
There is a potential problem with a subquery SUBSCRIBE BY expression. If the region in a row in the sales_order table is updated, so that the row is removed from a remote user's subscription, that UPDATE is replicated to the remote user as a DELETE, to remove the row from the subscriber's table (see "Replication of updates").
However, the corresponding rows in sales_order_items, which are not affected by the UPDATE, remain in the tables at the remote database. SQL Remote replicates changes as recorded in the transaction log, and nothing in the sales_order_items has changed, so there are no log entries.
SQL Anywhere provides a special syntax of the UPDATE statement in order to allow subqueries to be used. The syntax is as follows:
UPDATE table
...PUBLICATIONpublication
...SUBSCRIBE BYexpression
...WHERE search-condition
An UPDATE statement of this form must be executed in a BEFORE trigger for any operation that has a side effect of changing a related table's SUBSCRIBE BY values. In this statement, expression is typically a value or a subquery.
The following trigger solves the updated region problem in the example above:
CREATE TRIGGER MoveItemsBEFORE UPDATE OF regionON sales_orderREFERENCING NEW AS newrowFOR EACH ROWBEGINUPDATE sales_order_items PUBLICATION SalesPubSUBSCRIBE BY newrow.regionWHERE sales_order_items.id = newrow.id;END
The UPDATE statement does not make any alteration to the table itself, but places entries in the transaction log which the Message Agent uses to adjust the rows that are present in remote databases.
If the trigger is not correct, replication errors result. If the incorrect SUBSCRIBE BY value is given, the rows are moved to the wrong remote databases.
An alternative solution is to duplicate the region column in the sales_order_items table, and maintain the value in the column using a trigger. In this way, applications do not have to be aware of the extra column. The sample publication built in the SALESPUB.SQL command file in your SQL Anywhere installation directory uses this approach.
Subqueries in SUBSCRIBE BY clauses can return more than one value. This is useful because it allows SUBSCRIBE BY publications that are not partitioned into disjoint subsets.
One situation where this feature is useful is where two tables have a many-to-many relationship, and both tables need to have only a subset of rows in the remote databases.
Multiple value SUBSCRIBE BY queries must be used in conjunction with the special UPDATE statement described in the previous section, in order to maintain subscriptions properly.