canceling statement due to statement timeout - Full Load

[FrancoHR]

An error is occurring with 2 tables in a Full Load of a task in a QA environment:

00007300: 2023-09-22T09:38:54 [SOURCE_UNLOAD ]I: Unload finished for table 'INSIS_GEN_V10'.'PREM_INST_MODAL' (Id = 10). 1950992 rows sent. (streamcomponent.c:3638)
00008524: 2023-09-22T09:38:54 [TARGET_LOAD ]I: Load finished for table 'INSIS_GEN_V10'.'PREM_INST_MODAL' (Id = 10). 1950992 rows received. 0 rows skipped. Volume transferred 1495008520. (streamcomponent.c:3927)
00008504: 2023-09-22T09:40:57 [SORTER ]I: Task is running (sorter.c:702)
00003880: 2023-09-22T09:41:27 [TARGET_LOAD ]E: Failed (retcode -1) to execute statement: 'DROP TABLE "usinsiv01"."BLC_POLICY_PAYMENT_PLAN"' [1022502] (ar_odbc_stmt.c:4906)
00003880: 2023-09-22T09:41:27 [TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: 57014 NativeError: 1 Message: ERROR: canceling statement due to statement timeout;
Error while executing the query [1022502] (ar_odbc_stmt.c:4913)
00003880: 2023-09-22T09:41:27 [TARGET_LOAD ]E: Failed to drop table usinsiv01.BLC_POLICY_PAYMENT_PLAN [1022506] (odbc_endpoint_imp.c:5978)
00003880: 2023-09-22T09:41:27 [TARGET_LOAD ]E: Handling new table 'usinsiv01'.'BLC_POLICY_PAYMENT_PLAN' failed [1022506] (endpointshell.c:2752)
00008432: 2023-09-22T09:41:27 [TASK_MANAGER ]W: Table 'INSIS_GEN_V10'.'BLC_POLICY_PAYMENT_PLAN' (subtask 5 thread 1) is suspended. Failed (retcode -1) to execute statement: 'DROP TABLE "usinsiv01"."BLC_POLICY_PAYMENT_PLAN"'; RetCode: SQL_ERROR SqlState: 57014 NativeError: 1 Message: ERROR: canceling statement due to statement timeout;
Error while executing the query; Failed to drop table usinsiv01.BLC_POLICY_PAYMENT_PLAN; Handling new table 'usinsiv01'.'BLC_POLICY_PAYMENT_PLAN' failed (replicationtask.c:3060)
00000620: 2023-09-22T09:41:27 [SOURCE_UNLOAD ]I: Unload finished for table 'INSIS_GEN_V10'.'BLC_POLICY_PAYMENT_PLAN' (Id = 2). 4640 rows sent. (streamcomponent.c:3638)

There are heavier tables than these but the timeout error returns only in those 2 tables, how can I solve this? Why is this generated?

Source: Oracle
Target: Aurora Postgres

Regards,

[john_wang]

Hello @FrancoHR ,

Thanks for opening the article here!

Per the task log file lines

#634:

2023-09-22T09:31:26 [TARGET_LOAD ]T: Drop table statement: DROP TABLE "uuu"."ttt"

#23488:

2023-09-22T09:41:27 [TARGET_LOAD ]E: Failed (retcode -1) to execute statement: 'DROP TABLE "uuu"."ttt"'

(where uuu is the schema name, ttt is the table name). We see the DROP TABLE timed out after running 10 minutes. 

Please try to add an internal parameter executeTimeout in target endpoint and set its value eg 1200 (the unit is seconds, means 20 minutes) to see if the problem is solved. 

In general a DROP TABLE SQL should be completed immediately. Maybe PostgreSQL server is busy during that time, or other exceptions occurred. Checking the slow queries in PostgreSQL side is helpful to understand the issue further.

BTW, I'm purging the attached task log file as it may contain sensitive information. if you need to attach log file or other data files, please open the support ticket and attach them to the support case. Community article is open for all visitors.

Regards,

John.

[Heinvandenheuvel]

[Forum server was down for a bit it seems]

I agree with @john_wang but I do not think allowing Replicate to wait longer will help.

If a DROP TABLE is not happening in a a few seconds, then it isn't going to happen in 10 minutes, nor 20 minutes. Something is wrong like a dead-lock perhaps due to a foreign key constraint, or some (security audit) trigger or such.

Try the same statement outside Replicate using the Replicate username... does it work within reasonable time? Does it give additional error indicators?

Hein.

[SushilKumar]

Hello @FrancoHR 

Refer below link as its more related to Postgre then the Qlik replicate.

 https://repost.aws/knowledge-center/dms-error-canceling-statement-timeout

Reagrds,

Sushil Kumar