0

I am new to mySql workbench and need help importing a large file for analysis. I read here MySQL workbench table data import wizard extremely slow that the data import wizard shouldn't be used so i used LOAD DATA INFILE as suggested but I had issues importing due to secure_file_priv=NULL.

I found a solution here How should I tackle --secure-file-priv in MySQL? but I don't know how to reset the value of secure_file_priv.

I found some sources that use command line to do so but I don't know how to use mySql workbench from command line.

Any help on how to disable this or change it would be appreciated

nbk
  • 45,398
  • 8
  • 30
  • 47
zim68
  • 15
  • 6

1 Answers1

0

don't touch it, it is basicakky a security feature.

use the data import under server in workbench,

enter image description here

If you want do it by sql, ecery thng os written already in How should I tackle --secure-file-priv in MySQL? which you linked

simple copy your file in the folder, that you get as value, when you do a SHOW VARIABLES LIKE "secure_file_priv";

Now you can make a LOAD DAT ideally from the console, because then you have no timeout.

Read also Bulk insert in the namual so that you can optimize a big data import

If you really want to use any folder which is not recommended, you have to edit the my.ini file for that and restart to server (both can be done in Workbench as well, you see in the picture options file and Start/shutdown.) There are some other ways do restart the service.

Here are the basics good described of importing csv

nbk
  • 45,398
  • 8
  • 30
  • 47
  • thanks for the reply ```SHOW VARIABLES LIKE "secure_file_priv"``` only shows the value of the variable. it cant be used to change the variable. and Data Import is not taking .csv files. its only taking .dumps files – zim68 Dec 04 '20 at 00:39
  • no, if you copy the file in that folder, you can use Load In file as i stated in my answer. if you want to edit it, you have to edit my.ini and restart it as also stated in my answer., Please show the error message that you get and also the sql command – nbk Dec 04 '20 at 00:43
  • 1
    there is no file or directory path. ```"secure_file_priv"``` is set to NULL – zim68 Dec 04 '20 at 00:47
  • i tried to reset ```"secure_file_priv``` using SET but it was giving me an error that this action cant be perfomed – zim68 Dec 04 '20 at 00:50
  • no i mean when you try to load the file, if secure_file_priv is empyt you can choose every folder , that you workbench or mysql client has access to – nbk Dec 04 '20 at 01:02
  • its not empty, its set to NULL, those are two different things – zim68 Dec 04 '20 at 01:04
  • no, they aren't tyr to load the file and see what happemns – nbk Dec 04 '20 at 01:07
  • the error is variable ```"secure_file_priv"``` is a read only variable – zim68 Dec 04 '20 at 01:16