| Product: | Syniti Replicate (a.k.a Syniti Data Replication, DBMoto) |
| Version: | All |
| ID: | 1655 |
| Summary: | Issue with Oracle as a target when using bulk insert mode -- Syniti Replicate may show misleading information in the log |
Under the circumstances described below, Syniti Replicate can show misleading information about failed records in the log.
When there is an error during the replication of a single block of records in the bulk insert, Syniti Replicate shows a list of exceptions in the log. Normally, you should see an exception for each record that has failed. For instance, if you insert a block of records with primary key 1, 2, 3, 4 and the records 2 and 3 fail to be inserted, Syniti Replicate writes 2 error messages in the log related to the records with PK 2 and 3. This is what you would expect in case of a duplicate key error on both records:
…
2010-05-13 12:21:06.81 Info Refresh started, number of records to process: 3 (Replication: 'FOODA' - Target table: 'SCOTT.FOOD')
2010-05-13 12:23:08.19 Err Err Error inserting a new record (Replication: 'FOODA' - Target table: 'SCOTT.FOOD')
System.Exception: ORA-00001: unique constraint (SCOTT.SYS_C005540) violated
Record Type = 19
Record Origin = ForthWriter
Column Count = 3
Values = <null>
FOODNO = 2 ß PK of the first record that failed
DESCR = Pasta
KINDOF = 1
2010-05-13 12:23:45.19 Err Error inserting a new record (Replication: 'FOODA' - Target table: 'SCOTT.FOOD')
System.Exception: ORA-00001: unique constraint (SCOTT.SYS_C005540) violated
Record Type = 19
Record Origin = ForthWriter
Column Count = 3
Values = <null>
FOODNO = 3 ß PK of the second record that failed
DESCR = Cookies
KINDOF = 1
2010-05-13 12:23:47.30 Wrn Refresh completed with errors, inserted 2 records (Replication: 'FOODA' - Target table: 'SCOTT.FOOD')
Instead, what happens is that Oracle inserts a generic message exception on top of the list of exceptions, followed by the actual list of exceptions related to the records. The problem is that this initial exception refers internally to the first record of the block, and there is no information that tells Syniti Replicate that this is just a generic message and not a message related to a failure of the first record of the block. Here is what you will see:
…
2010-05-13 12:21:06.81 Info Refresh started, number of records to process: 3 (Replication: 'FOODA' - Target table: 'SCOTT.FOOD')
2010-05-13 12:23:08.19 Err Error inserting a new record (Replication: 'FOODA' - Target table: 'SCOTT.FOOD')
System.Exception: ORA-24381: error(s) in array DML
Record Type = 19
Record Origin = ForthWriter
Column Count = 3
Values = <null>
FOODNO = 1 ß PK of the first record of the block. NOTICE that the record was inserted correctly!
DESCR = Pancake
KINDOF = 1
2010-05-13 12:23:08.19 Err Err Error inserting a new record (Replication: 'FOODA' - Target table: 'SCOTT.FOOD')
System.Exception: ORA-00001: unique constraint (SCOTT.SYS_C005540) violated
Record Type = 19
Record Origin = ForthWriter
Column Count = 3
Values = <null>
FOODNO = 2 ß PK of the first record that failed
DESCR = Pasta
KINDOF = 1
2010-05-13 12:23:45.19 Err Error inserting a new record (Replication: 'FOODA' - Target table: 'SCOTT.FOOD')
System.Exception: ORA-00001: unique constraint (SCOTT.SYS_C005540) violated
Record Type = 19
Record Origin = ForthWriter
Column Count = 3
Values = <null>
FOODNO = 3 ß PK of the second record that failed
DESCR = Cookies
KINDOF = 1
2010-05-13 12:23:47.30 Wrn Refresh completed with errors, inserted 2 records (Replication: 'FOODA' - Target table: 'SCOTT.FOOD')
From the log excerpts above, you can see that the log displays misleading information, leading you to believe that there has been an error in record with PK = 1, when on the contrary the record was inserted correctly.