Some rows failed to load due to date timestamp error from MySQL

[desmondchew]

I have a new task loading from MySQL 8 on premise into Snowflake AWS. The task run for several hours before failing. The error is related to a column Timestamp - "DateTimeStampAL"

I am attaching the error here. Can I know how to workaround this record? Can I apply transformation? If so, could you please share the transformation script?

ERROR LOG:

Table 'SCHEMA'.'Log' (subtask 1 thread 1) is suspended. Failed (retcode -1) to execute statement: COPY INTO "LZ_SCHEMA"."TableXYZ"("CONOAL", "SKEYAL", "COL3", "COL4", "DateTimeStampAL", "ActionAL") FROM (select $1, $2, $3, $4, $5, $6 FROM '@"PROD"."LZ_LEGACYSCH"."ATTREP_IS_PROD_2047810c_2549_e349_84cd_7137febe4647"/20/') files = ('LOAD00000001.csv.gz') force=true; RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '2031-07-00 00:00:00.00000' is not recognized
File '20/LOAD00000001.csv.gz', line 624212, character 46
Row 624212, column ""TableXYZ""["DateTimeStampAL":5]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.; Failed (retcode -1) to execute statement: COPY INTO "LZ_SCHEMA"."TableXYZ"("COL1", "COL2", "COL3", "COL4", "DateTimeStampAL", "ActionAL

Thank you.
Desmond

[john_wang]

Hello Desmond, @desmondchew 

Thanks for reaching out to Qlik Community!

Could you please confirm whether the CSV file contains an invalid DATE value: '2031-07-00 00:00:00.00000'? Looks to me the error caused by the zero date.

Based on the error message, the issue is located at:

• File '20/LOAD00000001.csv.gz', line 624212, character 46
  Row 624212, column ""TableXYZ""["DateTimeStampAL":5]

To retrieve the CSV file, please follow these steps:

1. Set keepCSVFiles and keepErrorFiles to True in the target endpoint.
2. Reproduce the issue.
3. Locate and review the corresponding CSV file.

If the CSV file contains an invalid DATE value, we would prefer to resolve the issue at the source side rather than applying transformations to modify the value at the target side.

Let me know if you need further clarification.

John.

[desmondchew]

Hi John,

I turn on verbose and traced line 624212 which was indicated in the log. The date format look perfectly fine to me. There's no abnormal issue.

How can we fix this?

Desmond

[john_wang]

Hello Desmond, @desmondchew 

Thanks for the update.

Since MySQL allows zero month and zero date values, it is best to handle the zero-date issue directly within MySQL. However, without the diagnostic package and CSV files, it's difficult to determine the exact approach.

If you want to replace zero dates with "01," please refer to the following article:

Qlik Replicate and Snowflake Endpoint: Tables are suspended with Timestamp error

Hope this helps.

John.

[desmondchew]

Hi John,

I am not seeing any zero on the date values. 
At row 624212, this is the timestamp after the "FILEOPEN" column. There doesn't seem to have any issue with the date.

"FILEOPEN",2023-02-13 03:00:53.78098

Desmond

[john_wang]

Hello Desmond, @desmondchew 

We need additional information to address the issue. Please open a support ticket and attach the Trace Task Log file (decrypt it if necessary), also with the generated CSV files. Our support team will be more than happy to assist you.

Regards,

John.