topic Re: What is the implication without secure_file_priv=null with MySQL 8.0 as end point? in Qlik Replicate

What is the implication without secure_file_priv=null with MySQL 8.0 as end point?

desmondchew — Sat, 19 Mar 2022 00:21:28 GMT

Hi,

We are using Oracle 11.2.0.4 as source while target database MySQL 8.0.21 as end point in AWS.

We have constraint setting "secure_file_priv"=null according to our infrastructure team. I see that Qlik has listed that as a prerequisites https://help.qlik.com/en-US/replicate/May2021/Content/Replicate/Main/MySQL/prereq_mysql.htm?_ga=2.206128851.1567605253.1647499868-1077321936.1644194995

Is this parameter mandatory? Is there a workaround? What happen if it is not set?

Thank you.
Desmond

Re: What is the implication without secure_file_priv=null with MySQL 8.0 as end point?

john_wang — Sat, 19 Mar 2022 12:17:45 GMT

Hello Desmond, @desmondchew

First of all, Looks to me secure_file_priv = NULL is not a real MySQL target prerequisites in Qlik Replicate. Maybe a documentation defect, please open a support ticket (with a proper priority) to get the documentation amended.

According MySQL docs secure_file_priv ,

If set to NULL, the server disables import and export operations.

that means the "LOAD DATA LOCAL INFILE" will be disabled, Replicate cannot run Full Load and/or CDC under LOAD mode any more. By default Replicate uses CSV files to load data to MySQL database to gain the best performance. In general it's not recommended to set it to NULL.

However if it's NULL by your organizations secure rule, then You need to turn off the LOAD mode in MySQL Target Endpoint by adding an internal parameter "loadUsingCSV" and setting it to OFF, as below. Replicate will then use "INSERT" statements rather than "LOAD DATA" to replicate data from Oracle to MySQL.

Please take note that non-LOAD mode is significantly slower than LOAD mode, about 20~100 times slower (depends on the environment and data volumes).

Hope this helps.

Regards,

John.

Re: What is the implication without secure_file_priv=null with MySQL 8.0 as end point?

desmondchew — Sun, 20 Mar 2022 02:43:07 GMT

Hi John,

We will definitely like to use LOAD DATA which is faster. We have a value set for secure_file_privs to a path, not null. I read in the link that it requires set to NULL, but if this is not the case we should be good. https://help.qlik.com/en-US/replicate/May2021/Content/Replicate/Main/MySQL/prereq_mysql.htm?_ga=2.206128851.1567605253.1647499868-1077321936.1644194995

In order to work with MYSQL 8 as a target endpoint, you need to:

Set the parameter secure_file_priv = NULL in the MySQL database.
If you are using LOB columns, set the parameter sort_buffer_size = 128M in the MySQL database.

Thank you.
Desmond

Re: What is the implication without secure_file_priv=null with MySQL 8.0 as end point?

john_wang — Sun, 20 Mar 2022 02:53:17 GMT

Hello @desmondchew ,

Thanks for the update. So far your settings are good.

I noticed the first requirement however looks like it's inaccurate. Please raise an support ticket so far support team will confirm it for you, and certainly we will modify the docs if necessary.

Regards,

John.