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

[desmondchew]

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

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

Thank you.
Desmond

[john_wang · Accepted solution]

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.

[desmondchew]

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

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

[john_wang]

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.