CODEWITHSUNDEEP
mysqlrcsvsql-insertload-data-infile
A.Salehi
A.Salehi

Reputation: 33

MySQL : error with --secure- file-priv while using USE LOAD DATA INFILE from R

Im running code in MYSQL works fine no problem. But when i try to run the same code in R I get the error:

could not run statement: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

I googled a bit and found alot of people having trouble with running this from their workbench. This isnt my problem though, i can insert from workbench. But I want to do everything from R.

The code im running is:

LOAD DATA 
    INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\Fred_mbf_meta_data.csv' 
    INTO TABLE fred.fredmetambf
    FIELDS TERMINATED BY '\t'
    IGNORE 1 LINES
    (Title,Series_ID,Frequency,Units,Seas_adj,Start_Date,End_Date,Last_Updated);

I also tried adding >> LOCAL after LOAD DATA LOCAL INFILE. But then I got the error: "The used command is not allowed with this MySQL version"

Is it game over?

Upvotes: 1

Views: 281

Answers (1)

GMB
GMB

Reputation: 222482

As explained in the documentation, the secure-file-priv option restricts the directory from which files can be imported and exported.

You can use SHOW VARIABLES LIKE "secure_file_priv"; to find out what the current setting is.

Then, you can either move your import file to this directory, or disable the option (this requires modifying the init file and restarting the server).

Upvotes: 1

Related Questions