topic Re: CDC failed with inserting into a non-nullable column. Need help to identify the non-nullable column. in Qlik Replicate

CDC failed with inserting into a non-nullable column. Need help to identify the non-nullable column.

desmondchew — Tue, 16 Jul 2024 01:56:12 GMT

Hi,

I have an existing task that replicate from source MySQL 8 into Snowflake target endpoint. It failed during CDC and the error message below. I have masked some details related to our environment like schema name and columns.

I need help to identify which are the columns are hitting the error "CDC failed with inserting into a non nullable column"? Which is the non-nullable column? I have already made changes to the target table on column 9th and 10th to allow NULL however it still fails. Could you please help?

00016040: 2024-07-15T05:24:38:167903 [TARGET_APPLY ]E: Failed (retcode -1) to execute statement: INSERT INTO "LZ_MERIDIAN"."EX25" (col1, col2, col3, ........... ) SELECT CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 62 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col1", null ) as VARCHAR(131072)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 62 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col2", null ) as VARCHAR(131072)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 62 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col3", null ) as VARCHAR(131072)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 62 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col4", null ) as VARCHAR(200)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 62 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col5", null ) as VARCHAR(100)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 62 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col6", null ) as VARCHAR(100)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 62 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col7", null ) as VARCHAR(100)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 62 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col8", null ) as VARCHAR(100)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 62 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col9", null ) as VARCHAR(100)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 62 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 104, "SCHEMA"."attrep_changesACC417CB1... [1022502] (ar_odbc_stmt.c:5038) 00016040: 2024-07-15T05:24:38:167903 [TARGET_APPLY ]E: RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column [1022502] (ar_odbc_stmt.c:5046) 00016040: 2024-07-15T05:24:38:167903 [TARGET_APPLY ]E: Failed to execute bulk insert statement. 'INSERT INTO "SCHEMA"."EX25"

Thank you.
Desmond

Re: CDC failed with inserting into a non-nullable column. Need help to identify the non-nullable column.

DesmondWOO — Tue, 16 Jul 2024 03:09:21 GMT

Hi @desmondchew ,

Thank you for reaching out to the Qlik Community.

The error message is returned by the Snowflake. If you have already made changes to the target table on column 9th and 10th to allow NULL, the error may be caused by other columns.

Under Error Handling, please configure the task to stop when it encounters a data error. Your net change table "SCHEMA"."attrep_changesACC417CB1D0C8E44" may still exist. You can then query the table to identify the specific rows causing the problem.

Regards,
Desmond Woo

Re: CDC failed with inserting into a non-nullable column. Need help to identify the non-nullable column.

Heinvandenheuvel — Wed, 17 Jul 2024 20:10:17 GMT

Perhaps use the advanced - target endpoint setting - internal parameters - "keepCSVFiles".

Next after a failure, using the timestamps, find the right CSV files and use some scripting/editing tool to find a null?

Hein.

Re: CDC failed with inserting into a non-nullable column. Need help to identify the non-nullable column.

desmondchew — Fri, 26 Jul 2024 07:50:49 GMT

I have enabled "emptyasNull" in target Snowflake endpoint. I have checked on "SCHEMA"."attrep_changesACC417CB1D0C8E44", there are a lot of records. How can I identify which row is null or having issues?

I am not turning on "keepCSVFiles" as previously we enabled and it cause a production outage due to disk full over the weekend.

Desmond

Re: CDC failed with inserting into a non-nullable column. Need help to identify the non-nullable column.

DesmondWOO — Fri, 26 Jul 2024 08:55:23 GMT

Hi @desmondchew ,

I think you may check the NULL data with the following query:

select * from "DB"."PUBLIC"."attrep_changes499FC6950983F828" where "col1" is null or "col2" is null or ...

if you have a CSV file, you can use Excel or findstr command to search attrep_null.

Regards,
Desmond

Re: CDC failed with inserting into a non-nullable column. Need help to identify the non-nullable column.

SachinB — Sat, 27 Jul 2024 05:04:42 GMT

Hello @desmondchew ,

Looks your task is failing while updating table "'EX25", Could you please check do you have null values or empty string values present in the source where data type is defined as "NOT NULL" constraints

Else try with below option to send some value in identified column

CASE WHEN $Column_Name=='' THEN 'null'
ELSE $Column_Name
END

Also, I would request you to create support case, So that we can work together to resolve the issue.

Regards,

Sachin B