ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access

Question

comment I'm trying ,

mysql> 

LOAD DATA LOCAL INFILE '/var/tmp/countries.csv' 
INTO TABLE countries 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES 
TERMINATED BY '\n' 
IGNORE 1 LINES 
(CountryId,CountryCode,CountryDescription,CountryRegion,LastUpdatedDate,created_by,created_on)
SET created_by = 'DH_INITIAL_LOAD', created_on = current_timestamp();

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.`

It was working fine, I downloaded pymysql and mysql connector for the python script. I uninstalled and checked still it is not working. The verion and infile is ON,

 select version() -| 8.0.17



mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

please check also the rights of the folder you can also try to update it to an newer version — nbk, Aug 11 '20 at 16:13
Remove `LOCAL` keyword - it is errorneous in your case. Then check: 1) Access rights (in the filesystem) for OS account which is used for MySQL daemon starting 2) `local_infile` system variable (done - ON is correct value) 3) `secure_file_priv` system variable 4) FILE privilege for MySQL account which is used for the query executing. — Akina, Aug 11 '20 at 17:31
if I remove the local keyword , it throws me a error saying ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES) — Nivethitha Thiyagarajan, Aug 12 '20 at 05:42
SHOW VARIABLES LIKE "secure_file_priv"; - /tmp/ tried placing the file under /tmp and checked still facing the issue. — Nivethitha Thiyagarajan, Aug 12 '20 at 06:50

score 70 · Answer 1 · answered Nov 12 '20 at 15:54

Using MySql Workbench 8 or above introduced this issue. This fixed it for me:

This restriction can be removed from MySQL Workbench 8.0 in the following way. Edit the connection, on the Connection tab, go to the 'Advanced' sub-tab, and in the 'Others:' box add the line 'OPT_LOCAL_INFILE=1'.

This should allow a client using the Workbench to run LOAD DATA INFILE as usual.

Quoted from this link: https://bugs.mysql.com/bug.php?id=91872

score 54 · Accepted Answer · answered Aug 13 '20 at 05:40

54

Known issue: https://bugs.mysql.com/bug.php?id=91872

for workaround when trying to connect to mysql in itself set local-infile to 1 and perform the load command: mysql --local-infile=1 -h$MASTER_DB_HOST -u$MASTER_DB_USER -p$MASTER_DB_PASSWD -D$MASTER_DB_NAME

answered Aug 13 '20 at 05:40

Nivethitha Thiyagarajan

1,043
1
6
10

Unfortunately this solution does not work for me. – spectre Feb 16 '22 at 05:27

score 13 · Answer 3 · edited Aug 17 '22 at 07:27

For ubuntu:

edit the file /etc/mysql/mysql.conf.d/mysqld.cnf and add the following:

[mysqld]
secure-file-priv = ""

Restart the service

systemctl restart mysql

run: mysql -u root -p and check the local infile variable

  mysql> show global variables like 'local_infile';

  +---------------+-------+ 
  | Variable_name | Value |
  +---------------+-------+
  | local_infile  | OFF   |
  +---------------+-------+
  1 row in set (0.00 sec)

  mysql> set global local_infile=true;

  +---------------+-------+ 
  | Variable_name | Value |
  +---------------+-------+
  | local_infile  | ON    |
  +---------------+-------+
  1 row in set (0.00 sec)

```
  mysql> exit
  Bye
```

run
```
 mysql --local-infile=1 -u root -p
```

  LOAD DATA INFILE '/var/lib/mysql-files/filename' INTO TABLE tablename;

score 5 · Answer 4 · answered May 18 '21 at 18:01

To Fix this error (mysql 8):

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

add the following line into your server's config file, under the "client" section:

[client]
loose-local-infile=1

That will fix the error. However, this assumes you have already set the following under the "mysqld" section:

[mysqld]
local_infile=1

Having both parameters set in your config file will allow loading data from any directory.

Here's a simple example of use. Run in the mysql terminal:

load data local infile '/path/to/file/data.tsv' into table my_table_name
 fields terminated by '\t'
 LINES TERMINATED BY  '\n'
(
 `col1`,
 `col2`,
 `col3`,
 `col4`
);

This works for me, with mysql 8. Thank you. – Jadson Santos Jun 29 '21 at 19:08 — Jadson Santos, Jun 29 '21 at 19:08

score 1 · Answer 5 · answered Nov 06 '22 at 17:02

For mac m1 users (Big Sur, Monterey, Ventura) :-

• First System Preferences --> MySql "Stop MySql Server"

For macOS "Ventura" will go at the bottom of System Settings for MySQL

• Create etc/my.cnf file in /usr/local i.e ( /usr/local/etc/my.cnf )

https://drive.google.com/file/d/13XbMKYcmkzM4MGNCW0s70pfLqMDNhuoh/view?usp=sharing

• Now edit the "my.cnf" file using TextEdit:-

[client]
loose-local-infile=  1

[mysqld]
local_infile = 1
secure-file-priv = ""

follow the images as shown

[Step 1] : https://i.stack.imgur.com/8yZdk.png

[Step 2] : https://i.stack.imgur.com/yk9b1.jpg

Under connection --> Advanced --> Others (Edit it):-

 OPT_LOCAL_INFILE = 1

[Step 3] : https://i.stack.imgur.com/TlEJ5.jpg

• Under System Preferences "Start MySql Server"

• Always use it in your code in MySql Workbench for things to work

SHOW GLOBAL VARIABLES LIKE 'local_infile';
    
SET GLOBAL local_infile = true;   /* We can use here ON also in place of true */
    
LOAD DATA LOCAL INFILE  
'/Users/ram/Downloads/DataSet.csv'
into table dress
FIELDS TERMINATED by ','
ENCLOSED by '"'
lines terminated by '\n'
IGNORE 1 ROWS;

If you like this method then please upvote this post

score 0 · Answer 6 · edited Nov 17 '21 at 07:30

For Mac users, if the above solutions don't work (and I am using mac OS Big Sur)

Go to the terminal and type

$ vi ~/.my.cnf

Then add this to the file

[mysqld]
secure_file_priv = ''

Save and exit the window. Now go to terminal and restart mysql server using

$mysql.server restart

enter mysql server using

$ mysql -u <your username> -p

Now enter the following in mysql

mysql> SHOW VARIABLES LIKE "secure_file_priv";

This should show the value of the variable as blank. Now if you use the query

load data local infile '<complete_file_path>' 
into table <table_name>
fields terminated by ',';

This should work! also make sure you have set the following variable to "ON" using:

set global local_infile = 1;

Update

For windows user - having mysql 8.0 installed following is the secure file variable mentioned above

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"

score 0 · Answer 7 · answered Dec 15 '21 at 20:33

on windows :
for me after enabling local in both client and server and settings (in my.ini)

        [client]
          local_infile=1
        [mysqld]
          secure_file_priv=""

 the sql command ...
   load data local infile "FILE" 
 change to 
    load data infile "FILE"

works every time ...
so much so I do not even use the load local anymore and am not sure of the point of the command ...

score 0 · Answer 8 · answered Dec 24 '22 at 07:06

ERROR 2068: LOAD DATA LOCAL INFILE file request rejected due to restrictions on access

On Windows 11 64-bit operating system, x64-based processor, build 22621.963, Version 22H2 - MySQL installed using the web installer .MSI file as a developer configuration.

Change my.ini from the default in C:\ProgramData\MySQL\MySQL Server 8.0

In the [client] section add local_infile=ON

In the [mysql] section add local_infile=ON

In the [mysqld] section add local_infile=ON

Also change the line secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads" to secure-file-priv=""

The following code then works for me from MySQL Workbench connected to my local instance -

CREATE DATABASE northwind;

use northwind;
show tables;
create table shippers (
    shipperID int,
    company varchar(255),
    phone varchar(255)
);

Use this data by creating a .CSV file

ShipperID,CompanyName,Phone
1,Speedy Express,(503) 555-9831
2,United Package,(503) 555-3199
3,Federal Shipping,(503) 555-9931

Copy the .CSV file to C:\ProgramData\MySQL\MySQL Server 8.0\Data\northwind

This code works to load the shippers table -

use northwind;
#SHOW VARIABLES LIKE "secure_file_priv";
#SHOW VARIABLES LIKE "local_infile";
LOAD DATA INFILE 'nw_shippers.csv' 
INTO TABLE shippers 
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
select * from shippers;

Also for MySQL

SELECT VERSION(); gives '8.0.31'

Help > About Workbench gives "Version 8.0.31 build 2235049 CE 64 bits"

score 0 · Answer 9 · answered Feb 09 '23 at 07:26

If anyone looking for laravel than visit this link In case link is not working, you need to add the MYSQL_ATTR_LOCAL_INFILE to your PDO options. You can do this in config/database.php in the mysql block of the connections array: 'options' => array(PDO::MYSQL_ATTR_LOCAL_INFILE => true)

score 0 · Answer 10 · answered Jun 26 '23 at 22:20

Steps for Windows 10, MySQL 8.0 and using CMD:

Log into MySQL with local-infile=1:

mysql --local-infile=1 -u root -p

Make sure local_infile variable is ON:

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+

If this is OFF set to true:

mysqld> SET GLOBAL local_infile = true;

Add or check that the following lines are under the [client] and [mysqld] section of your my.ini configuration file: (Mine was located under 'C:\ProgramData\MySQL\MySQL Server 8.0')

[client] loose-local-infile=1

[mysqld] local_infile=1

Use LOAD DATA LOCAL INFILE

AND make sure the path to your data is seperated by '/'

LOAD DATA LOCAL INFILE 'C:/Path/To/Your/File.txt' INTO TABLE yourtable;

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access

10 Answers10

For mac m1 users (Big Sur, Monterey, Ventura) :-

Steps for Windows 10, MySQL 8.0 and using CMD:

Linked

Related

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access

10 Answers10

For mac m1 users (Big Sur, Monterey, Ventura) :-

Steps for **Windows 10**, **MySQL 8.0** and using **CMD**:

Linked

Related

Steps for Windows 10, MySQL 8.0 and using CMD: