topic Re: canceling statement due to statement timeout - Full Load in Qlik Replicate

canceling statement due to statement timeout - Full Load

FrancoHR — Fri, 22 Sep 2023 15:33:36 GMT

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,

Re: canceling statement due to statement timeout - Full Load

john_wang — Fri, 22 Sep 2023 15:32:03 GMT

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.

Re: canceling statement due to statement timeout - Full Load

Heinvandenheuvel — Mon, 25 Sep 2023 14:26:58 GMT

[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.

Re: canceling statement due to statement timeout - Full Load

SushilKumar — Fri, 03 May 2024 10:55:35 GMT

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

Re: canceling statement due to statement timeout - Full Load

Ashs — Wed, 12 Feb 2025 15:17:54 GMT

We are facing similar kind of issue in our CDC task. I am not sure why it happened but during that time source running on some high volume data.

My source is as/400 DB2 and target is postgres RDS

My log looks like

00004852: 2025-02-11T10:36:50 [SOURCE_CAPTURE ]I: Starting journal receiver 'XYZ.XXYZ4345' (db2i_endpoint_capture.c:1776)
00003320: 2025-02-11T10:37:09 [TARGET_APPLY ]E: Failed (retcode -1) to execute statement: 'INSERT INTO "XYZ"."xyz_table"("col1","col2","asd","asda","ad") values (?,?,?,?,?)' [1022502] (ar_odbc_stmt.c:2810)
00003320: 2025-02-11T10:37:09 [TARGET_APPLY ]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:2816)
00003320: 2025-02-11T10:37:09 [TARGET_APPLY ]E: Failed to execute insert statement, stream position isXYZ.XXYZ4345:88271486 [1022506] (odbc_util.c:3867)

I don't want to add any timeout to endpoint as it might produce some latency.

But want to check why it failed? When I restarted the task, it ran fine.

Re: canceling statement due to statement timeout - Full Load

Dana_Baldwin — Wed, 12 Feb 2025 15:51:18 GMT

Hi @Ashs

This indicates a performance or throughput issue on the network between Replicate and the target or on the target database/environment itself. Please check those items.

Thanks,

Dana