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

[bhagtrajaram]

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

[Arun_Arasu]

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

[bhagtrajaram]

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, 

[Arun_Arasu]

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

[bhagtrajaram]

Hi,

Our main concern is the 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)

Regards,

[Steve_Nguyen]

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.

[desmondchew]

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.

[john_wang]

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

[DesmondWOO]

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