Ошибка 2068 mysql

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»

Источник

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»

Источник

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access is a common error that occurs when trying to load data from a local file into a MySQL table using the LOAD DATA INFILE statement. The error message indicates that the access to the local file is restricted, which can be due to various reasons such as security concerns, lack of file permissions, or MySQL server configuration.

Method 1: Grant File Privilege to the MySQL User

To fix the «ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access» error in MySQL, you can grant file privilege to the MySQL user. Here are the steps to do it:

  1. Log in to MySQL as a root user:
  1. Create a new MySQL user if you don’t have one already:
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
  1. Grant the file privilege to the new user:
GRANT FILE ON *.* TO 'new_user'@'localhost';
  1. Verify the file privilege has been granted:
SHOW GRANTS FOR 'new_user'@'localhost';
  1. Exit MySQL:

Now, you can use the LOAD DATA LOCAL INFILE command without getting the error. Here’s an example of how to use it:

LOAD DATA LOCAL INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(header1, header2, header3);

Make sure to replace ‘/path/to/file.csv’ with the actual file path and ‘table_name’ with the name of the table you want to load the data into. Also, replace ‘header1’, ‘header2’, and ‘header3’ with the actual column names in your table.

That’s it! Now you know how to fix the «ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access» error in MySQL by granting file privilege to the MySQL user.

Method 2: Enable Local_infile Parameter in MySQL Configuration File

To enable the local_infile parameter in MySQL configuration file, follow these steps:

  1. Open the MySQL configuration file my.cnf using your preferred text editor. The file is usually located in /etc/mysql/ directory.

  2. Add the following line under the [mysqld] section:

    This enables the local_infile parameter in MySQL.

  3. Save the file and exit the text editor.

  4. Restart the MySQL service to apply the changes:

    sudo service mysql restart

  5. Now you can use LOAD DATA LOCAL INFILE command without getting the ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access error.

Here’s an example of how to use LOAD DATA LOCAL INFILE command:

LOAD DATA LOCAL INFILE '/path/to/file.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

This command loads the data from the specified CSV file into the my_table table, where fields are separated by commas and enclosed by double quotes. The first row is ignored as it usually contains column headers.

Note that enabling the local_infile parameter may pose a security risk as it allows loading data from a file on the client’s computer. Make sure to use it only when necessary and with trusted sources.

Method 3: Specify the Full Path of the File in LOAD DATA INFILE Statement

To fix the «ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access» issue in MySQL, you can specify the full path of the file in the LOAD DATA INFILE statement. Here’s how to do it:

  1. First, find the full path of the file you want to load into MySQL. You can do this by navigating to the file in your terminal and using the pwd command.

  2. In your MySQL query, use the full path of the file in the LOAD DATA INFILE statement. For example:

LOAD DATA INFILE '/full/path/to/file.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

  1. Make sure that the MySQL user has the necessary permissions to access the file at the specified path.

  2. If you’re still encountering issues, you can try using the --local-infile=1 option when starting the MySQL command line client. This will enable the use of the LOAD DATA LOCAL INFILE statement.

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

By specifying the full path of the file in the LOAD DATA INFILE statement, you should be able to resolve the «ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access» issue in MySQL.

Method 4: Use a Different Method to Load Data into MySQL

To load data into MySQL using a different method, you can use the following steps:

  1. Connect to MySQL using your preferred method (e.g. command line, MySQL Workbench, etc.).
  2. Create a new table to store your data using the CREATE TABLE statement. For example:
CREATE TABLE my_table (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);
  1. Save your data in a CSV file (e.g. my_data.csv) with the same column names and order as your table.
  2. Use the LOAD DATA INFILE statement to load your data into the table. For example:
LOAD DATA INFILE '/path/to/my_data.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

This statement will load the data from the CSV file into the my_table table, using commas as field separators and newlines as row separators. The IGNORE 1 ROWS option skips the header row in the CSV file.

  1. Verify that the data was loaded correctly by running a SELECT statement on the table. For example:

This statement will return all rows from the my_table table.

By using this method to load data into MySQL, you can avoid the «ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access» error that can occur when attempting to load data using the LOAD DATA LOCAL INFILE statement.

Источник

Дано: требуется синхронизация данных между системами, входная информация раз в час поступает в виде файла в формате CSV через запрос к веб сервису, должна быть загружена в таблицу MySQL. Сервер MySQL не локальный. ОС Oracle Linux 7, MySQL 8

Что не работает

Неработающий скрипт:

#!/bin/bash
## переменные
DIR="/opt/syncdata"
SCRIPTNAME="statictable_import"
LOGFILE="$DIR/LOG/$SCRIPTNAME.log"
SCRIPTNAME="statictable_import"
DB="TEMPDB03"
DBTABLE="STATICTABLE"
CSVFILE="$DIR/CSV/STATICTABLE.CSV"
MYSQL="/usr/bin/mysql"
WGET="/usr/bin/wget"
## загрузка файла из веб-сервиса и сохранение его в каталог CSV
$WGET -O $CSVFILE https://USR0045:PASSWORD@restservice.local/pls/src_data/grafana/statictable
## подключение к MySQL и очищение таблицы
$MYSQL --database=$DB<<EOFMYSQL
TRUNCATE TABLE $DBTABLE;
EOFMYSQL
## загрузка данных из файла
$MYSQL --database=$DB<<EOFMYSQL
load data local infile '$CSVFILE' replace INTO TABLE $DBTABLE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ID,
TABLENAME,
NAME,
TIME,
VALUE01,
VALUE02,
VALUE03,
VALUE04,
VALUE05,
VALUE06,
VALUE07,
VALUE08
);
EOFMYSQL

Первая ошибка:

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

Запрещено. Это решается переменной сервера, проверяем её:

SHOW GLOBAL VARIABLES LIKE 'local_infile';
'local_infile','OFF'

Запрещаем запрещать и снова проверяем

SET GLOBAL local_infile = 'ON';
SHOW GLOBAL VARIABLES LIKE 'local_infile'
'local_infile','ON'

Выполняем скрипт, опять получаем ошибку:

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

Устанавливаем переменную в *.ini сервера и перезапускаем сервер

local_infile=ON

И опять не работает.

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

Решение

Не уверен баг это или особенность, но помимо глобальной переменной сервера требуется опция в команде mysql: –local-infile=1

Работающий скрипт:

#!/bin/bash
## переменные
DIR="/opt/syncdata"
SCRIPTNAME="statictable_import"
LOGFILE="$DIR/LOG/$SCRIPTNAME.log"
SCRIPTNAME="statictable_import"
DB="TEMPDB03"
DBTABLE="STATICTABLE"
CSVFILE="$DIR/CSV/STATICTABLE.CSV"
MYSQL="/usr/bin/mysql"
WGET="/usr/bin/wget"
## загрузка файла и сохранение его в каталог CSV
$WGET -O $CSVFILE https://USR0045:PASSWORD@restservice.local/pls/f_data/grafana/statictable
## подключение к MySQL и очищение таблицы
$MYSQL --database=$DB<<EOFMYSQL
TRUNCATE TABLE $DBTABLE;
EOFMYSQL
## загрузка данных из файла
$MYSQL  --local-infile=1 --database=$DB<<EOFMYSQL
load data local infile '$CSVFILE' replace INTO TABLE $DBTABLE
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES
(ID,
TABLENAME,
NAME,
TIME,
VALUE01,
VALUE02,
VALUE03,
VALUE04,
VALUE05,
VALUE06,
VALUE07,
VALUE08
);
EOFMYSQL

Хинт. Сервер удалённый, а в скрипте не используется логин, пароль и имя сервера MySQL. В ОС создан пользователь от имени которого выполняется синхронизация, в пространстве пользователя настроен CRONTAB со всеми задачами синхронизации, в файле ~/.my.cnf пользователя хранятся настройки подключения к MySQL.

cat ~/.my.cnf 
[client]
user=USER_TEMPDB03
password="PASSWORD"
host=mysql02-master.local

Источник

Follow us on Social Media

Error Code: 2068. Load Data Local Infile File Request Rejected Due To Restrictions On Access. With Code Examples

In this article, the solution of Error Code: 2068. Load Data Local Infile File Request Rejected Due To Restrictions On Access. will be demonstrated using examples from the programming language.

MySql Workbench 8 or above introduced this issue. To Fix:

Under Mysql Connections, Right Click Connection and "Edit Connection", 
Go to Advanced Tab, In Others Box add the line: OPT_LOCAL_INFILE=1

By examining various real-world cases, we’ve shown how to fix the Error Code: 2068. Load Data Local Infile File Request Rejected Due To Restrictions On Access. bug.

How do I fix error 2068 in mysql?

To Fix this error (mysql 8): ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.For ubuntu:

  • edit the file /etc/mysql/mysql.conf.d/mysqld.cnf and add the following line at the end:
  • Restart the service systemctl stop mysql systemctl start mysql.

How do I enable local data Infile in mysql?

For the mysql client, local data loading capability is determined by the default compiled into the MySQL client library. To disable or enable it explicitly, use the –local-infile=0 or –local-infile[=1] option. For the mysqlimport client, local data loading is not used by default.

How do I disable secure priv in MySQL?

To disable it, set the variable to a NULL value. This was successful. You have learned to configure secure-file-priv variable to fit your use case. Until next time, thanks for using our guide to solve “MySQL server is running with the –secure-file-priv” error when trying to load or save data.06-Jul-2021

How do I edit a connection in MySQL?

The Manage Server Connections dialog is another way to manage MySQL connections. This dialog is invoked by either clicking the manage connections icon ( ) on the home screen or by selecting Database and then Manage Connections from the main menu.

Where is my CNF file?

cnf is located in /etc/mysql/my. cnf .

What is load data infile in MySQL?

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. If the LOCAL keyword is specified, the file is read from the client host. If LOCAL is not specified, the file must be located on the server. ( LOCAL is available in MySQL 3.22.

How do I start MySQL from command line?

Launch the MySQL Command-Line Client. To launch the client, enter the following command in a Command Prompt window: mysql -u root -p . The -p option is needed only if a root password is defined for MySQL. Enter the password when prompted.

How do I restart MySQL?

First, open the Run window by using the Windows+R keyboard. Second, type services. msc and press Enter : Third, select the MySQL service and click the restart button.

What is my CNF file in MySQL?

cnf, /usr/my. This file contains configuration settings that will be loaded when the server is first started including settings for the clients, server, mysqld_safe wrapper and various other mysql client programs. Note: As of 5.6 version, the my. cnf is written, but all the lines are commented out.

Where can I find my INI file in Windows?

The my. ini is in hidden folder of program data. First go to C: drive and then hidden folder of program data. From that, move to the MySQL version directory.30-Jul-2019

Follow us on Social Media

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка 2039 порше кайен
  • Ошибка 2067 мерседес спринтер
  • Ошибка 2038 года
  • Ошибка 206 при обновлении sml 482
  • Ошибка 20360 шкода