Mysql node - how to pass parameter (OPT_LOCAL_INFILE=1) to connection string - when using load local in sql

self-hosted n8n (0.222.3)
Trying to use the mysql node to execute some sql using load local to load in csv file.

In MySql workbench, I have to add the parameter OPT_LOCAL_INFILE=1 to the advanced connection settings. see screenshot
image

This allows the load local sql statement to use a local .csv file on my (n8n) machine.

How do I add this to the connection string that the mysql node uses ?

This is the sql:

load data local
infile '/files/fred_customers.csv'
into table boospa.prestashop_customers
FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY ';\n' STARTING BY ''
ignore 1 lines
(id_customer, customer_group, customer_language, @customer_company, customer_firstname, customer_lastname, customer_email, @customer_birthday, customer_newsletter, customer_optin, customer_creation_date)
set 
customer_birthday = str_to_date(if(@customer_birthday='',null,@customer_birthday),'%Y-%m-%d')
,customer_company = if(@customer_company='NULL',null,@customer_company)

Current workaround is to copy the csv file to the mysql server and not use load local.
It’s would just be easier to use the local n8n machine though.

sample csv (i couldn’t attach the file to this topic ?)

id_customer;customer_group;customer_language;customer_company;customer_firstname;customer_lastname;customer_email;customer_birthday;customer_newsletter;customer_optin;customer_creation_date;
"10";"Client Boospa";"Français";"NULL";"Thierry";"PATERAC";"[email protected]";"2020-01-01";"1";"1";"2020-09-30 16:55:36";
"11";"Client Boospa";"Français";"NULL";"severine";"mion";"[email protected]";"";"0";"0";"2020-09-30 16:55:36";
"12";"Client Boospa";"Français";"NULL";"Arnaud";"GUYON";"[email protected]";"";"0";"0";"2020-09-30 16:55:36";

And this is the (mysql) error in n8n node as I am not using the OPT_LOCAL_INFILE=1 setting:

I would appreciate any guidance you can give.
Many thanks. Mark.

Hi @whitbym!

I’m sorry you’re having trouble. n8n does not currently provide an option to set this parameter in its own MySQL credentials I am afraid. You might want to raise a separate feature request for this.

Possible alternatives would be:

  1. Reading the CSV file in n8n using the Read Binary Data and Spreadsheet nodes before executing the MySQL node. Like so:

  1. Configuring local_infile directly on your database server:

To explicitly cause the server to refuse or permit LOAD DATA LOCAL statements (regardless of how client programs and libraries are configured at build time or runtime), start mysqld with local_infile disabled or enabled, respectively.

1 Like

Thanks for the response MutedJam. And for your suggestions.
It is appreciated.
The read binary + spreadsheet node + insert (mysql node) is too slow (a mysql issue not n8n) when there is more than about 50,000 lines, even when using split in batches. Hence my alternative approach.
Also, the mysql server is already set with local_infile=true - see screenshot.

image

I suspected my question is a feature request but thanks for the confirmation.

I’m not blocked as I use ssh n8n node to copy the csv files to the mysql server and then run the mysql n8n node using the sql syntax for “non-local” load.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.