topic Re: qlik replicate; getting error after full-load;load data local infile in Qlik Replicate

qlik replicate; getting error after full-load;load data local infile

bhagtrajaram — Thu, 06 Oct 2022 07:22:58 GMT

Hi,

After starting a full-load were are getting the following error:

00004868: 2022-10-04T09:50:01 [TARGET_LOAD ]E: Failed (retcode -1) to execute statement: 'load data local infile "E:\\somelocation\\data_files\\3\\LOAD00000065.csv" into
table `DUMMY_TABLE`.`DUMMY` CHARACTER SET UTF8MB4 fields terminated by ',' enclosed by '"' lines terminated by '\n'
( `dummy1`,`dummy1`,`dummy3...' [1022502] (ar_odbc_stmt.c:4888)

Were are running

software: Qlik Replicate

version: 2022.5.0.499

odbc drivers: 8.0.25

Regards

Re: qlik replicate; getting error after full-load;load data local infile

Arun_Arasu — Thu, 06 Oct 2022 07:47:54 GMT

Hello @bhagtrajaram ,

Can you please confirm if the target database is MYSQL

If yes , as per the user guide , below are the supported driver version

Only these driver versions are supported:

5.3.11 to 5.3.13
8.0.23

https://help.qlik.com/en-US/replicate/May2022/Content/Replicate/Main/MySQL/prereq_mysql.htm

Also , please follow the below steps

In sql work bench --> can you please add the below command

set global local_infile=1;

Then do a reload of your task and verify if the data is loading

Thanks & Regards,

Arun

Re: qlik replicate; getting error after full-load;load data local infile

bhagtrajaram — Thu, 06 Oct 2022 09:28:14 GMT

Hi,

The target db is mysql version 5.7.mysql_aurora.2.10.2.

sql workbench:

Variable_name|Value|
-------------+-----+
local_infile |ON |

In the log we are seeing:

RetCode: SQL_ERROR SqlState: HY000 NativeError: 1205 Message: [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.10-log]Lock wait timeout exceeded;

meaning the odbc version: ODBC 5.3

We have installed '8.0.25' but why is the log saying ODBC 5.3?

Within our endpoint configuration we are seeing:

MySQL ODBC 8.0 Unicode Driver

Regards,

Re: qlik replicate; getting error after full-load;load data local infile

Arun_Arasu — Thu, 06 Oct 2022 11:51:12 GMT

Hello @bhagtrajaram

For the below issue

RetCode: SQL_ERROR SqlState: HY000 NativeError: 1205 Message: [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.10-log]Lock wait timeout exceeded

Increase the value for innodb_lock_wait_timeout -

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout

Permitted Values Type integer
Default 50
Min Value 1
Max Value 1073741824
===

We had other customers that did the below value and it helps.

innodb_lock_wait_timeout=1200
innodb_deadlock_detect=off;

Regards,

Arun

Re: qlik replicate; getting error after full-load;load data local infile

bhagtrajaram — Thu, 06 Oct 2022 14:05:36 GMT

Hi,

Our main concern is the error:

Regards,

Re: qlik replicate; getting error after full-load;load data local infile

Steve_Nguyen — Thu, 06 Oct 2022 17:27:31 GMT

1. set target_load ,, to vebose, and rerun the task.

-once fail look at the task log to see where it fail,

2. if still fail on some csv file, look at the loadxx.csv to see any field that got shift

or better yet, try loading a small table to see if it works, then try to isolate down to the table that having issue.

Re: qlik replicate; getting error after full-load;load data local infile

desmondchew — Fri, 14 Jun 2024 00:48:51 GMT

I have a similar situation. I believe that the target endpoint MySQL is unable to cope with a sudden huge surge of records coming in.

I resolve it by reducing the concurrent load under the tuning. Thereafter the table was able to load successfully for my case.

Re: qlik replicate; getting error after full-load;load data local infile

john_wang — Fri, 14 Jun 2024 00:59:32 GMT

Thank you so much for your sharing and great support! @desmondchew

Re: qlik replicate; getting error after full-load;load data local infile

DesmondWOO — Fri, 14 Jun 2024 01:38:28 GMT

Hi @bhagtrajaram ,

As Steve mentioned, please check if any fields have shifted in the LOAD00000065.csv. For example, verify whether your data contains commas, double quotes, or other anomalies in the CSV file.
"LOAD DATA LOCAL INFILE" is MySQL command, you can execute this command directly to check.

Regards,
Desmond