Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
desmondchew
Creator II
Creator II

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

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

 

 

Labels (2)
1 Solution

Accepted Solutions
john_wang
Support
Support

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

john_wang_0-1647689796986.png

Hope this helps.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

3 Replies
john_wang
Support
Support

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

john_wang_0-1647689796986.png

Hope this helps.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
desmondchew
Creator II
Creator II
Author

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
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!