Error code 2068 load data local infile file request rejected due to restrictions on access

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:ProgramDataMySQLMySQL 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:ProgramDataMySQLMySQL Server 8.0Datanorthwind

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 'rn'
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»

Tags:

Mysql

Load

Set the my.cnf file as the default configuration file in MySql Workbench. Click the Wrench icon next to Instance. Under configuration file, enter the path to my.cnf file: /etc/my.cnf.

Restart the MySQL server workbench.

Try the following statements in MySQL Workbench: SHOW VARIABLES LIKE «local_infile»; //Should be ON SHOW VARIABLES LIKE «secure_file_priv»; //Should have no values (not NULL but blank)

Using LOCAL keyword, loading is successful. However, without LOCAL keyword, I get access error.

Error Code: 1045. Access denied for user ‘admin’@’%’ (using password: YES)

After beating my head around this for an hour, I switched back to MySQL Workbench 6.3. Bliss.

Adding what worked for me here, I was able to find a working solution through the links @Zacca provided, but I had to dig through solutions that didn’t work, and I thought I’d repost here in case those links die at some point.

First, if I ran SHOW GLOBAL VARIABLES LIKE ‘local_infile’; I can see that INFILE is enabled. However, I was still getting «ERROR CODE 2068»

I was able to resolve this by editing the connection string in MySQL workbench by navigating to Home, then:

Edit Connection > Advanced > Copy OPT_LOCAL_INFILE=1 into Others box on a new line

Then restart the connection. After that, LOAD LOCAL INFILE runs without error.

Also, as a side note, before I found this solution, I found I could run the LOAD LOCAL INFILE command from R without issue, which helped me isolate the client as the issue, as opposed to a security/permissions related problem.

Источник

Response like that `Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.’ When I load the data from CSV

When I run the code below in MySQL5.0, I will have a response like:

Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

I already set the local infile to On, but still not working, do I need to still change any parameter?

1 Answer 1

OK, I found the solution finally, we need to go to the MySQL Command Line Client (My version is 5.7), and change all the in the path to / , like

In the workbench and cell will show us the different error, in the cell, the response is Error 1148, in the workbench is 2068.

Linked

Related

Hot Network Questions

Subscribe to RSS

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.1.11.43150

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Источник

MySQL : Load data infile

I am getting error when using load data to insert the query .

This file is there in the location . But mysql throws the below error . ERROR 29 (HY000): File ‘/home/bharathi/out.txt’ not found (Errcode: 13)

Data Dir is pointing to root permissioned folder . I can’t change this variable because it’s readonly .

How can I do the load data infile operation ?

I tried changing file permissions , load data local infile . It wont work .

2 Answers 2

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See Section 6.2.1, “Privileges Provided by MySQL”. For non- LOCAL load operations, if the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

You should therefore either:

Ensure that your MySQL user has the FILE privilege and, assuming that the secure_file_priv system variable is not set:

make the file readable by all; or

move the file into the database directory.

Or else, use the LOCAL keyword to have the file read by your client and transmitted to the server. However, note that:

LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with —local-infile=0 , LOCAL does not work. See Section 6.1.6, “Security Issues with LOAD DATA LOCAL ”.

Источник

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

comment I’m trying ,

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,

8 Answers 8

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.

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

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

1. Restart the service

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

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:

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

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:

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

Go to the terminal and type

Then add this to the file

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

enter mysql server using

Now enter the following in mysql

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

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

Update

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



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

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 )

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

follow the images as shown

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

• Under System Preferences «Start MySql Server»

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

If you like this method then please upvote this post



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:ProgramDataMySQLMySQL Server 8.0

In the [client] section add local_infile=ON

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

Use this data by creating a .CSV file

Copy the .CSV file to C:ProgramDataMySQLMySQL Server 8.0Datanorthwind

This code works to load the shippers table —

SELECT VERSION(); gives ‘8.0.31’

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

Источник

I am in a time consuming process of migrating a very large dataset stored in csv file on my laptop to our database. The target database is using mysql v.5.7.27. this db instance is running inside a docker container.
I already access to the root db user, it appear to have all the priviledges granted:

SHOW GRANTS;

results:
'GRANT ALL PRIVILEGES ON *.* TO ''root''@''%'' WITH GRANT OPTION'

So far what I have tried:

1. explicitly pass the flag local-infile=1 to let the server allow loading local data loading by clients, refer to https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile

2. override the secure_file_priv setting on startup of sql server, set to empty based on the doc it should disable the secure setting: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_secure_file_priv

3. grant root user FILE priviledge on all tables for localhost:

GRANT FILE on *.* to 'root'@'localhost';

Below is the complete docker compose section for starting up the db:

....
awesome-db:
    build: ./awesome/database
    hostname: "localhost"
    environment:
      MYSQL_ROOT_PASSWORD: xxxx
      MYSQL_DATABASE: dbname
    ports:
      - "3308:3306"
    command: mysqld --local-infile=1 --init-file="/tmp/database/schema.sql" --secure_file_priv=""

....

After tried all this, upon running LOAD DATA LOCAL INFILE command like the one below

LOAD DATA LOCAL INFILE '/mylaptopDir/someData.csv'
INTO TABLE AwesomeTable
FIELDS TERMINATED BY ','
IGNORE 1 ROWS;

result in following error:

LOAD DATA LOCAL INFILE 'someData.csv' INTO TABLE AwesomeTable FIELDS TERMINATED BY ',' IGNORE 1 ROWS    Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.   0.0035 sec

I would appreciate for constructive and helpful idea or suggestions on resolving this problem !

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)

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