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

Question

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

Answer 1

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

4. Click 'Test Connection'. It will successfully update the connection.
5. Click close.

Answer 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