Need help to troubleshoot why a task fail to load

[desmondchew]

We have a failed task and the log file shows the following.

Source changes that would have had no impact were not applied to the target database. Refer to the 'attrep_apply_exceptions' table for details

I query the 'attrep_apply_exceptions table. It showed some update statements on the replicated table.  But is 0 row affected. I am uploading the output.

Could you share how could I dig deeper for the failure please?

[john_wang]

Hello @desmondchew ,

"0 rows affected" means the given row cannot be found in the target table. Many reason may lead the row cannot be found eg the row does not exist, or the PK column was not marked correctly, or the source/target side is out of synchronize etc. you can query the table table to see if the row (corresponding the PK, or "where" clause) exist, compare the row between source/target sides tables, and/or enable source_capture/target_apply to Verbose, the task log file will print rich information to help you to understand the reason.

Another option is set the task to "UPSERT" mode. However I'd like to suggest to find out the reason first because upsert mode can prevent the warning but it may not solve the problem completely.

Regards,

John.

[desmondchew]

Hi,

Thanks for your reply. I looked into the exception table again and noted that the SQL is trying to update the same column with the same filter column value. This looked strange. Any idea why it could happen?

Thank you.
Desmond

[john_wang]

Hello Desmond @desmondchew ,

We need additional information to understand the reason why the SQL is trying to update the same column with the same filter column value, if you want to trace it deeper, I'd like suggest you to open a support case (because the shared files here are public to all) with task Diagnostics Package and source/target side table creation DDL, the SQL how to prepare the row data to reproduce the behavior etc. I can guide you how to do that if necessary. 

Before the above steps, I'd like to confirm with:

1. Does the row exist in target table or not?

2. if you execute the exact same UPDATE query, what's the result in your database?

    You never mentioned what's the source/target Database however I'm guessing you are running MySQL as target DB? if yet then I've solution to solve the "0 rows affected" being treat as ERROR rather than treat as warning, or even simply ignore it.

Regards,

John.

[desmondchew]

Hi,

I am happy to open a support case with diagnostics package. The task has failed to run as of now again.

To answer your questions.

1. The row does not exist in source and target.

2. If I execute manually, the update will be still zero.

I have turn on verbose more on source and target apply. Please see the error. The source is Oracle and target is MySQL.

I have masked the table_name.

00006136: 2021-12-18T23:14:51:565962 [SOURCE_CAPTURE ]V: Produce DELETE event: table 'TABLE_XYZ' object id '770097' context '00000018.88f6c66c.00000001.04e7.02.0000:709912.921139.16' xid [0000000000130010] timestamp '2021-12-18 15:51:40' thread 1 (oradcdc_oralog.c:867)
00006136: 2021-12-18T23:14:51:565962 [SOURCE_CAPTURE ]V: Retrieved event with context '00000018.88f6c66c.00000001.04e7.02.0000:709912.921139.16', thread '1' (oracdc_merger.c:1280)
00006136: 2021-12-18T23:14:51:565962 [SOURCE_CAPTURE ]V: Column '40' in the table 'TABLE_XYZ' is not supported (oracle_endpoint_capture.c:1142)
00006136: 2021-12-18T23:14:51:581590 [SOURCE_CAPTURE ]V: Column '41' in the table 'TABLE_XYZ' is not supported (oracle_endpoint_capture.c:1142)
00006136: 2021-12-18T23:14:51:581590 [SOURCE_CAPTURE ]V: Column '226' in the table 'TABLE_XYZ' is not supported (oracle_endpoint_capture.c:1142)
00006136: 2021-12-18T23:14:51:581590 [SOURCE_CAPTURE ]V: Column '227' in the table 'TABLE_XYZ' is not supported

[john_wang]

Hello Desmond, @desmondchew 

We need a support case with below information to understand why Replicate compose such an UPDATE SQL Statement if the row does not exist in both sides. Or a Reload is necessary.

1. Source_capture/target_apply set to Verbose, then upload the Diag Package;

    If you are running latter version of 2021.5, or 2021.11, please check How to decrypt Qlik Replicate verbose task log files before you reproduce the behavior

2. Both source/target sides table creation DDL

3. What's the source side DML leads the behavior

4. How about if you remove the filter

Regarding the information:

Column 'nn' in the table 'XYZ' is not supported

Please check if the columns are virtual column, or computed column, or any other Non-supported data types . 

Regards,

John.

[desmondchew]

Hi,

I'd like to follow up with this case. Am I suppose to upload the required info in this forum or you have other platform? I do not want to expose our table name and info that could identify us.

1. Source_capture/target_apply set to Verbose, then upload the Diag Package;

    If you are running latter version of 2021.5, or 2021.11, please check How to decrypt Qlik Replicate verbose task log files before you reproduce the behavior

2. Both source/target sides table creation DDL

3. What's the source side DML leads the behavior

4. How about if you remove the filter

[john_wang]

Hello Desmond, @desmondchew 

You are right, that's why we suggest you to open support case in our Support Portal . 

Regards,

John.

[desmondchew]

Hi John,

I got a access denied error after clicking on the link "Support Portal". 

Do you know why? Do you have the full URL?

Thank you.

Desmond

[Steve_Nguyen]

@desmondchew i was able to access the support portal link that John provided.

please try to do on another browser or workstation.