3

Hey everyone im having this problem trying to import a CSV on mysql. im using the root user to make sure i have all the grants and access but stil it keeps giving me the same error. The queries im using are the following:

CREATE table if not exists Order_items(
    order_id VARCHAR(255) NOT NULL,
    order_item_id VARCHAR(255) NOT NULL,
    product_id VARCHAR(255) NOT NULL,
    seller_id VARCHAR(255) NOT NULL,
    shipping_limit_date datetime NOT NULL,
    price INT NOT NULL,
    freight_value INT NOT NULL,
    PRIMARY KEY(order_id)
);

load data local infile 'C:\Users\julia\Desktop\SUPR\Test\olist_order_items_dataset.csv' 
into table order_items
FIELDS terminated by ','
optionally enclosed by '"'
lines terminated by '\n'
ignore 1 rows
(order_id, order_item_id, product_id, seller_id, shipping_limit_date, price, freight_value); `

Error code 2068: file requested rejected due to restrictions on access with root user

I have tried everything but it still gives me the same error due to restrictions.

Thanks

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Julian Kim
  • 31
  • 1
  • 1
  • 2

4 Answers4

18

Change the back slashes (\) to forward slashes (/) in the file path. Run the file again. Even if the error persists, then follow the method below.

  1. Go to the MySQL Connections page.
  2. Right click the connection and click 'Edit connection'.
  3. Select 'Advanced' option. Paste the below line in the 'Others' box.
OPT_LOCAL_INFILE=1
  1. Click 'Test Connection'. It will successfully update the connection.
  2. Click close.

enter image description here enter image description here enter image description here

mpriya
  • 823
  • 8
  • 15
2

This is an answer specifically for when the client is not the same machine as the server. It is also an answer for when the client is not using workbench, that is using mysql from the command line. This is tested using a MySQL client version 8 against databases from version 5 against both MySQL and MariaDB servers

  1. Like all previous answers, the SET GLOBAL INFILE must be set TRUE on the server.

  2. The name of the load file must be set using forward slashes, even on Windows. So:

    • incorrect: c:\temp\file.txt
    • correct: c:/temp/file.txt

  3. The LOAD DATA command must include the keyword LOCAL

  4. Create a "defaults-extra-file", e.g. data-infile.ini. Note that the "--defaults-extra-file" must always follow the mysql.exe command

  5. In the file, add:

    [mysql]

    local_infile = 1

The command syntax is as follows

mysql --defaults-extra-file=data-infile.ini -e "LOAD DATA LOCAL INFILE c:/temp/file.txt INTO table some_table" some_database
mnewnham
  • 70
  • 6
1

I was getting the same error on windows using mysql workbench. When I changed the path from C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\myfile.csv to C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/myfile.csv it worked just fine. I didn't have to add local before infile

Make sure you enable infile loading SET GLOBAL local_infile=1; and are putting the files in the correct directory. You can run SHOW VARIABLES LIKE "secure_file_priv"; to see where that directory is located.

Assuming C:\Users\julia\Desktop\SUPR\Test is where mysql wants the uploads, which by default that isn't the case it is C:\ProgramData\MySQL\MySQL Server 8.0\Uploads then your statement would be

load data infile 'C:/Users/julia/Desktop/SUPR/Test/olist_order_items_dataset.csv' 
into table order_items
FIELDS terminated by ','
optionally enclosed by '"'
lines terminated by '\n'
ignore 1 rows
(order_id, order_item_id, product_id, seller_id, shipping_limit_date, price, freight_value);
odyth
  • 4,324
  • 3
  • 37
  • 45
0

The local_infile option should be enabled both on client and server sides, and you could find answer here:

ERROR:loading local data is disabled

Malcolm.Lau
  • 1
  • 1