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

user_13985795 0
Viewed 78982

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)
load mysql
6 Answers

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

user_54816610

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

user_139857950

For ubuntu:

  1. edit the file /etc/mysql/mysql.conf.d/mysqld.cnf and add the following:
[mysqld]
secure-file-priv = ""
  1. Restart the service
systemctl restart mysql

  1. 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)

  2.   mysql> set global local_infile=true;

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

  3.   mysql> exit
  Bye

  1. run

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

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

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`
);
user_8491040

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"
user_99882820

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 ...
user_97647830
Related