Error while trying to load a new table from MySQL into Snowflake
The source endpoint is AWS RDS MySQL 8 while target is Snowflake endpoint.
The datatype for column expected_event_date is "datetime" in table "booking_event_data".
The table failed to load due to error below. Can you please help and suggest a workaround fix?
RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized File '7/LOAD00000001.csv.gz', line 6, character 15 Row 6, column ""booking_event_data""["expected_event_date":7]
Handling End of table 'LZ_SCHEMA'.'booking_event_data' loading failed by subtask 2 thread 1 Failed to copy data of file D:\Attunity\Replicate\data\tasks\BridgeNet\cloud\7\LOAD00000001.csv to database Failed to load LZ_SCHEMA.booking_event_data from stage, file name: LOAD00000001.csv RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized File '7/LOAD00000001.csv.gz', line 6, character 15 Row 6, column ""booking_event_data""["expected_event_date":7] 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"."booking_event_data"("id", "booking_id", "container_id", "process_event_id", "process_id", "event_id", "expected_event_date", "revised_event_date", "actual_event_date", "location", "vessel", "voyage", "created_by", "created_at", "updated_by", "updated_at", "is_revised_fix", "is_actual_fix", "is_revised_update", "is_actual_update") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 FROM '@"DEV"."LZ_LEGACYODS"."ATTREP_IS_DEV_31b7412f_d101_3a49_8e6a_f3a6e796d55f"/7/') files = ('LOAD00000001.csv.gz') force=true RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized File '7/LOAD00000001.csv.gz', line 6, character 15 Row 6, column ""booking_event_data""["expected_event_date":7] 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"."booking_event_data"("id", "booking_id", "container_id", "process_event_id", "process_id", "event_id", "expected_event_date", "revised_event_date", "actual_event_date", "location", "vessel", "voyage", "created_by", "created_at", "updated_by", "updated_at", "is_revised_fix", "is_actual_fix", "is_revised_update", "is_actual_update") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 FROM '@"DEV"."LZ_LEGACYODS"."ATTREP_IS_DEV_31b7412f_d101_3a49_8e6a_f3a6e796d55f"/7/') files = ('LOAD00000001.csv.gz') force=true RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized File '7/LOAD00000001.csv.gz', line 6, character 15 Row 6, column ""booking_event_data""["expected_event_date":7] 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"."booking_event_data"("id", "booking_id", "container_id", "process_event_id", "process_id", "event_id", "expected_event_date", "revised_event_date", "actual_event_date", "location", "vessel", "voyage", "created_by", "created_at", "updated_by", "updated_at", "is_revised_fix", "is_actual_fix", "is_revised_update", "is_actual_update") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 FROM '@"DEV"."LZ_LEGACYODS"."ATTREP_IS_DEV_31b7412f_d101_3a49_8e6a_f3a6e796d55f"/7/') files = ('LOAD00000001.csv.gz') force=true RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized File '7/LOAD00000001.csv.gz', line 6, character 15 Row 6, column ""booking_event_data""["expected_event_date":7] 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"."booking_event_data"("id", "booking_id", "container_id", "process_event_id", "process_id", "event_id", "expected_event_date", "revised_event_date", "actual_event_date", "location", "vessel", "voyage", "created_by", "created_at", "updated_by", "updated_at", "is_revised_fix", "is_actual_fix", "is_revised_update", "is_actual_update") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 FROM '@"DEV"."LZ_LEGACYODS"."ATTREP_IS_DEV_31b7412f_d101_3a49_8e6a_f3a6e796d55f"/7/') files = ('LOAD00000001.csv.gz') force=true RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized File '7/LOAD00000001.csv.gz', line 6, character 15 Row 6, column ""booking_event_data""["expected_event_date":7] 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"."booking_event_data"("id", "booking_id", "container_id", "process_event_id", "process_id", "event_id", "expected_event_date", "revised_event_date", "actual_event_date", "location", "vessel", "voyage", "created_by", "created_at", "updated_by", "updated_at", "is_revised_fix", "is_actual_fix", "is_revised_update", "is_actual_update") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 FROM '@"DEV"."LZ_LEGACYODS"."ATTREP_IS_DEV_31b7412f_d101_3a49_8e6a_f3a6e796d55f"/7/') files = ('LOAD00000001.csv.gz') force=true RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00' is not recognized File '7/LOAD00000001.csv.gz', line 6, character 15 Row 6, column ""booking_event_data""["expected_event_date":7] 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"."booking_event_data"("id", "booking_id", "container_id", "process_event_id", "process_id", "event_id", "expected_event_date", "revised_event_date", "actual_event_date", "location", "vessel", "voyage", "created_by", "created_at", "updated_by", "updated_at", "is_revised_fix", "is_actual_fix", "is_revised_update", "is_actual_update") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20 FROM '@"DEV"."LZ_LEGACYODS"."ATTREP_IS_DEV_31b7412f_d101_3a49_8e6a_f3a6e796d55f"/7/') files = ('LOAD00000001.csv.gz') force=true
Thanks for reaching out to Qlik via Qlik Community Support page. Such issue requires through analysis of Settings and logs, hence request you to reach out to technical Support via a case and provide Diagnostics package. And request you to not to upload any Log here as it can be access by larger Community member then the intended Support personal.
