Mysql "load data local" issue (Replicate require "local_infile = 1)

[khchoy]

Hi,

In MySQL target endpoint, most customer ask why Replicate required "local_infile  = 1" . Because this setting has a lot of  security issues.  but Replicate still not give  any other options and did not any mention in the user guide.

If customer not allow "local_infile  = 1", do we must give up this customer ?  

Regards,

Kwangho

[john_wang]

Hello KwangHo, @khchoy 

This is MySQL itself prerequisites which uses LOAD DATA CLIENT method to gain the best performance. So far you need it in Replicate if use LOAD method. You know that Replicate composes CSV file(s) on Replicate Server (it's MySQL Client side) and then loads the file(s) to MySQL Server side. It's as same as other apps as long as it uses the LOAD DATA CLIENT method.

Certainly giving up is not the approach, we have other options includes:

1. MySQL recommends connecting using the --ssl-mode=VERIFY_IDENTITY option 

2. In Replicate you can set Internal Parameter loadUsingCSV in target EndPoint to turn off load Using CSV  . Take notes this may impact the performance.

Let me know if you need any additional assistance,

John.

[Heinvandenheuvel]

Can you educate me where yo find "local_infile  = 1"? I did not readily find the word "local_infile" in Replicate or MySQL.

> If customer not allow "local_infile  = 1", do we must give up this customer ?  

Yup, they are not worthy! 🙂

But seriously. This may be a matter of education and detailed understanding. Many security groups give high level guidelines and rules but they should allow for well documented exceptions (if they are worthy :-).

For example it may be enough to know this is only used for a specific username (as used by Replicate) through a specific non-alterable method (as implemented by Replicate) and files firmly protected for use specifically for that user. Or maybe they do allow this if the files are stored on an encrypted medium such that there is no risk of data-at-rest intrusions.

Hopefully the excellent suggestions by @john_wang  already help to solve this?

And while John, as a Qlik employee, cannot be expected to support 'walk away', myself as a casual interested party feel that sometimes walking away is best for all. This may wel be the beginning of a long series of struggles. Some may be for the right (technical) reasons or they may be for the wrong (political, power-play) reasons.

Good luck,

Hein

[john_wang]

Hello @Heinvandenheuvel ,

Glad to see you are around this question too 😄

The local_infile is required by MySQL Server.

We have options in both MySQL perspective, and Replicate perspective, and I'm sure there are other options too, it's worth to 'stay' 😄.

Regards,

John.