Apply and Target Databases

What to do when an Apply Process fails or does not apply, and when the Target Database cannot be prepared. The status of a failed Apply Process shows the error after the state (see Status of any Capture or Apply Processes). The same text is in the process log and in Incident Management of the Web UI (see Logs and Information for Support). A message that starts with the name of a database, such as mssql:, ORA- or pq:, is the error of that database, passed on unchanged.

An Apply Process has failed

The status shows ERROR = [...] SQL = [...] LSN = [...]

This is the usual form of the error of a statement. ERROR is the text of the Target Database, SQL is the statement that failed and LSN is the transaction. For rows that are written in bulk the SQL part is a list of the rows with their values, so remove the values before you send a log to somebody.

The Apply Process fails at the same transaction every time. The server runs it again every 5 minutes, at most restartattempts times, and each run fails at the same LSN until the cause is removed.

What to do: stop the process, which also stops the automatic restarts (see Stop any Capture or Apply Processes). Remove the cause in the Target Database: a missing column, a row that exists already, a value that does not fit. Run the process again; it continues with the transaction that failed.

When you decide to leave the transaction out, look first at what it contains. The command report with the LSN from the message at both ends lists the tables that the transaction changes, with the number of inserts, updates and deletes in each (see Report Process). It works while the Apply Process is stopped, because it reads the trail files of the Capture Process. The tables are named as in the trail files, before the rules of the Apply Process:

Then reset the process behind the transaction, with the same LSN, and run it:

Be careful: skip=1 leaves out the whole transaction with that LSN, with all the rows that it changed in all the tables, not only the row or the statement that failed. A transaction can hold thousands of rows, for example a batch job or a bulk update, and all of them are missing in the Target Database afterwards. Use it when there is no other way. Often a smaller step is enough: add the missing column or table, or leave it out with a rule of the Apply Process (see below), and the transaction is applied without it and nothing else is lost. After a skip compare the tables that the report listed and repair them (see DATA COMPARISON). The command is described in Reset Process.

I corrected a rule and the transaction still fails

The Apply Process reads its configuration every time it is run, so a corrected rule in the Apply Process applies at once, even to a transaction that has failed before. A rule in the Capture Process does not: the Capture Process writes the transformation into the trail files when it captures a transaction, and a transaction that is captured already keeps what was written. Correct the rule in the Apply Process, or reset the Apply Process behind the transaction (skip=1).

A mask for a data type is compared with the full name of the type, with its precision and scale. TYPE=NUMBER does not match a column of the type NUMBER(1,0); TYPE=NUMBER(1,0) does, and so does TYPE=NUMBER%, which matches every column of the type NUMBER. When the mask matches nothing the transformation never takes place and the value arrives as it is; a typical symptom is invalid type for bit column (see Data Type Conversion). Test a rule with preview=1 (see Objects Mapping and Possible Transforms).

Violation of PRIMARY KEY constraint, Cannot insert duplicate key

The Target table has the row already. It happens when two pipelines write into the same table, when a load is repeated, or when an Apply Process is pointed at a table that holds the data of an earlier run. There are three ways out. Delete the row in the Target Database: the details of the error are enough to find it, because the message of the database names the table and the constraint, most databases also give the key value, and the part SQL = [...] of the status shows the values of the row that the Apply Process tried to insert. Or skip the transaction. Or list the table in the parameter mergetables: its inserts and the rows of the Initial Load are then applied as MERGE, which updates a row that exists. The names are written as schema.table as they are in the Target Database after the mapping, they are case-sensitive and are separated by commas. Updates and deletes are not changed.

A column does not exist in the Target table

The Target Database refuses the statement with its own message, for example Invalid column name 'COMMENT_TEXT' (SQL Server), ORA-00904: "COMMENT_TEXT": invalid identifier (Oracle), column "comment_text" of relation "orders" does not exist (PostgreSQL) or Unknown column 'comment_text' in 'field list' (MySQL). For rows that are written in bulk to a SQL Server Target Database the message is column COMMENT_TEXT does not exist in destination table .... The Source table has the column and the Target table does not. Typical reasons: the column was added in the Source Database and the DDL was not replicated (the table does not match ddlinclude, or the statement was run while the Capture Process did not capture), or the Target table was made by hand.

What to do: when the Target Database needs the column, add it there and run the Apply Process again. When it does not need it, leave it out with a rule of the Apply Process. The right side of the rule names the table and has NULL in the place of the column:

Do not write NULL alone on the right side: a rule such as dbo.orders.comment_text:NULL leaves out the whole table. With preview=1 at the end of the alter command nothing is saved and the table is listed with the column marked [REMOVED]. The process reads its rules when it is run, and the transaction that failed is applied again, now without the column. See Objects Mapping and Possible Transforms for the other forms of a rule.

A table does not exist in the Target Database

The messages of the Target Database are, for example, Invalid object name 'dbo.orders_archive' (SQL Server), ORA-00942: table or view does not exist (Oracle), relation "public.orders_archive" does not exist (PostgreSQL) and Table 'sales.orders_archive' doesn't exist (MySQL). The Capture Process captures the table (it matches dmlinclude) and the Target Database does not have it: the table was created in the Source Database after the Target Database was prepared and the DDL was not replicated, or it is a table that the Target Database does not need, for example a work table.

When the Target Database needs the table, create it with the same columns and run the Apply Process again. When it does not, leave the table out with a rule of the Apply Process that has NULL as its whole right side:

The table name can contain %: dbo.work_%:NULL leaves out all the tables whose names start with work_, and dbo.%:NULL all the tables of the schema dbo. With preview=1 the table is listed as [REMOVED] and nothing is saved. When no Apply Process needs the table, leave it out of the Capture Process instead (see Capture Objects Specification): nothing is then written to the trail files for it.

A value does not fit the column of the Target table

A column of the Target table is too narrow for the value (value 300 out of range for tinyint column, String or binary data would be truncated, ORA-12899: value too large for column), has another type than the Source column (Conversion failed when converting date and/or time from character string), or does not accept NULL. Change the Target table, or change the value with a rule of the Apply Process (see Data Type Conversion). The command validate process=apply id=1 warns about such tables before the run: about the missing tables and, for a SQL Server Target Database, about the missing and the too narrow columns (see Validate Process).

A DDL statement fails

The text has the form of the first entry, with the DDL as the SQL, for example There is already an object named 'T' in the database. A DDL statement is not left out by itself. Fix the Target Database, or reset the process behind its LSN. The parameters ddlcreate and ddldrop with the option skip let a CREATE TABLE of a table that exists already, and a DROP TABLE of a table that is gone already, pass without an error (see Prepare Apply Process).

The connection to the Target Database is lost

A message such as failed to send SQL Batch: write tcp ...: write: broken pipe or unable to open tcp connection with host ...: i/o timeout means that the database, or something between, has closed the connection. The process fails and the server runs it again within 5 minutes, which is enough when the database is back. When it happens again and again, look at the idle timeouts of the firewalls and of the load balancers between the two. For a login or a name that is not right (Login failed for user, No database is provided) correct the parameters of the process with alter.

Triggers and foreign keys of the Target Database

The triggers of the Target tables fire

The Apply Process writes the changes with ordinary INSERT, UPDATE and DELETE statements, so a trigger of a Target table fires for every row that the Apply Process applies, as it does for any other application. Repstance does not disable the triggers. It can change the result: a trigger that writes into another table (an audit table, a history table) adds its own rows to the rows that the Apply Process writes there, which gives duplicate keys or rows twice; a trigger that changes the row (a time stamp, a calculated value) makes the Target Database different from the Source Database, and Data Comparison reports the row; a trigger that raises an error stops the Apply Process with that error; and in a replication in both directions the changes that a trigger makes can be captured and sent back (see Bi-Direction Replication).

What to do: when the Target Database is only a copy of the Source Database during the replication, disable the triggers of the replicated tables in the Target Database. The changes that the triggers of the Source Database make are replicated as ordinary changes, so a trigger of the Target Database would do the same work a second time. Enable the triggers again when the Target Database stops being a copy and is used by applications. When the triggers have to stay enabled, keep them from firing for the Apply Process:

• SQL Server: create the triggers with NOT FOR REPLICATION and set repuser=1 in the Apply Process. The Apply Process then connects as a replication login, and these triggers, and the foreign keys and the check constraints that are marked in the same way, do not fire (see SQL Server Apply Parameters). The parameter ddldisabletriggers=1 disables the triggers of a table after a replicated DDL statement on it, so that triggers that came with the DDL do not repeat the replicated changes.
• PostgreSQL: repuser=1 sets session_replication_role = replica for the Apply Process, and the triggers and the foreign keys of the Target tables do not fire. The user needs the privilege for it (see Database Privileges).
• Oracle and MySQL: Repstance has no switch, so let the triggers leave out the changes of the database user that the Apply Process uses.

Disable the triggers before an Initial Load as well when they must not fire for the loaded rows.

The foreign keys of the Target tables

Disable the foreign keys of the Target tables in two cases: during an Initial Load, and when the Apply Process runs with batchmode=2.

The Initial Load does not load the tables in the order of their foreign keys, and the load options T and D empty the tables first, which an enabled foreign key refuses. Disable or drop the foreign keys of the tables that are loaded, and enable them again when the load is complete.

With batchmode=2 the Apply Process combines transactions, nets out the changes of a row and applies the changes table by table, so a row of a child table can reach the Target Database before its parent row, or a parent row can be deleted before its child rows. An enabled foreign key refuses it, and the Apply Process can fail with the message of the database, for example The INSERT statement conflicted with the FOREIGN KEY constraint (SQL Server), ORA-02291: integrity constraint ... violated - parent key not found (Oracle), insert or update on table "orders" violates foreign key constraint (PostgreSQL) or Cannot add or update a child row: a foreign key constraint fails (MySQL). Disable the foreign keys of the Target tables, or use batchmode 0 or 1, which keep the order of the transactions of the Source Database. The default is 0 for SQL Server, Oracle, MySQL and PostgreSQL, and 2 for Redshift and Snowflake, which do not enforce foreign keys.

An Apply Process does not start or does not apply

[mysql] DB is not supported

The Apply Process is for a database that the edition does not include. The name of the database type is in the brackets. See Supported Database Requirements.

Database is not prepared for apply

The message ends with the number of the first object of Repstance that is missing in the Target Database, for example Schema objects do not exist: 4: the checkpoint table, its history table or one of the procedures. Prepare the Target Database with prepare database=target, with a user that has the rights that are listed in Database Privileges. validate database tells more (see Validate Database).

The status stays at Waiting for the Trail Files

The status RUN (In Process), Waiting for the Trail Files. LSN: 0x... Sequence: 3 is not a failure: the Apply Process looks every 5 seconds for the trail file of its Capture Process and has not found it. Check that the parameter capturename is the name of the Capture Process exactly (it is case-sensitive), that the Capture Process has been run and has written trail files, and that the trail files of this Apply Process have not been deleted (see below).

Invalid map file, Broken trail, Invalid MDF header

A trail file of the Capture Process is damaged, or a file of the trail is missing. Restore the files of the trail from a backup (see Backup Repstance Files), or reset the Apply Process to a transaction whose files exist. A reset to an LSN that is not in the files is refused with LSN 0x... has not been found. Available LSN/LSNs: 0x... 0x..., and the LSNs that the message names are the ones to use.

The Apply Process waits and the trail files are gone

The Apply Processes delete the trail files that every Apply Process has applied (the parameter cleanuptrail, 0 by default). A trail file that you need again, for example after you restored the Target Database to an earlier time, is gone and the process then waits for it for ever. Keep cleanuptrail at a number of days that is at least the age of your oldest backup of the Target Database, or -1 to keep everything (the trail directory then grows until you clean it yourself, see Housekeeping).

A new Apply Process starts at an old position

The position of an Apply Process is kept in the Target Database, in the table apply_ckpt of the schema repi (of the user REPI or C##REPI in Oracle), and for an AWS S3 target in the file trail/s3/applyckpt.{id} on the server. A new Apply Process that takes an id that was used before finds the position of the old one and continues from it, and its status shows an old time as the last applied change. Use an id that has not been used for this Target Database.

The lag grows

The status shows where the time goes (see Status of any Capture or Apply Processes). When the Apply Process waits most of the time, it is waiting for the Capture Process and the lag comes from the Source side (see Capture and Source Databases). When it processes all the time, look at the Performance Tuning Parameters of the Apply Process (for example bulkthreshold and batchmode) and at the Target Database itself: its indexes, its triggers and its disks. The Lag is valid only while the Capture Process is running.

Preparing the Target Database

prepare, validate or remove database fail

The message is the error of the database, or one of Repstance. Missed database parameter and An invalid value [x] has been supplied for parameter [y] are mistakes in the command, and the help of the parameters follows the message. Process is active. Try again later means that a prepare or a remove of a SQL Server database is still running. Connection errors and missing rights are the text of the database; the rights are listed in Database Privileges. A WARNING in a result that says Valid is about optional objects: run prepare database again to create them.

Snowflake: failed to parse PEM block containing the key

The key pair authentication needs an RSA private key in PEM format without a passphrase (see Snowflake Apply Parameters). The messages found a non-RSA private key in PKCS#8 format and failed to parse RSA private key in PKCS#1 or PKCS#8 format say the same. A password that is empty with authtype=0 is refused with password is empty.

AWS S3: the credentials or the bucket are not right

validate database only lists the buckets of the account (it needs the permission s3:ListAllMyBuckets), so a wrong bucket or region shows up when the Apply Process writes the first file. The text is the error of AWS, for example NoCredentialProviders. prepare and remove do nothing for S3 (see AWS S3 as Target).