错误:已禁用加载本地数据-必须在客户端和服务器端均启用此功能 [英] ERROR: Loading local data is disabled - this must be enabled on both the client and server sides

问题描述

除了最明显的问题(例如以下问题)之外，我不理解其他人对类似问题的回答:

I don't understand the responses that others have provided to similar questions except for the most obvious ones, such as the one below:

mysql> SET GLOBAL local_infile=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';

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

通过这个，我的意思是提供了确切的代码.如果有人可以逐步引导我完成启用客户端"端和服务器"端的本地数据所需执行的操作，我将不胜感激.好像我已经在客户端上启用了本地数据，但是我不知道我需要给我的计算机什么指令来启用服务器端".我一点都不精通技术，我只想了解将数据上传到MySQL工作台中的地步.

By this I mean the exact code was provided. I'd greatly appreciate if someone could walk me through, step by step, what I need to do to enable local data on the "client" side and "server" side. It seems like I've enabled local data on the client side, but I don't know what instructions I need to give my computer to enable the "server side". I'm not tech savvy at all, and I just want to be able to get to the point where the data has been uploaded into MySQL workbench.

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

CREATE TABLE toys (
uniq_id VARCHAR(1000),
product_name VARCHAR(1000),
manufacturer VARCHAR(1000),
price VARCHAR(1000),
number_available_in_stock VARCHAR (1000),
number_of_reviews INT,
number_of_answered_questions INT,
average_review_rating VARCHAR(1000),
amazon_category_and_sub_category VARCHAR(1000),
customers_who_bought_this_item_also_bought VARCHAR(1000),
description VARCHAR(1000),
product_information VARCHAR(1000),
product_description VARCHAR(1000),
items_customers_buy_after_viewing_this_item VARCHAR(1000),
customer_questions_and_answers VARCHAR(1000),
customer_reviews VARCHAR(1000),
sellers VARCHAR(1000)
);

LOAD DATA LOCAL INFILE ‘/Users/BruddaDave/Desktop/amazonsample.csv’ INTO TABLE toys
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
IGNORE 1 LINES
(uniq_id, product_name, manufacturer, price, number_available_in_stock, number_of_reviews, number_of_answered_questions, average_review_rating, amazon_category_and_sub_category, customers_who_bought_this_item_also_bought, description, product_information, product_description, items_customers_buy_after_viewing_this_item, customer_questions_and_answers, customer_reviews, sellers)
;

我只希望能够使用命令行shell将.csv文件导入MySQL.

I just want to be able to import a .csv file into MySQL using the command line shell.

推荐答案

如果在服务器或客户端上禁用了LOCAL功能，则尝试发出LOAD DATA LOCAL语句的客户端将收到以下错误消息:

If LOCAL capability is disabled, on either the server or client side, a client that attempts to issue a LOAD DATA LOCAL statement receives the following error message:

ERROR 3950 (42000): Loading local data is disabled; this must be
enabled on both the client and server side

当我按照Mysql的教程将文本文件pet.txt加载到pet表中时遇到了相同的问题:

I met the same issue when I want to load the text file pet.txt into the pet table following a tutorial of Mysql:https://dev.mysql.com/doc/refman/8.0/en/loading-tables.html

在线搜索后，我通过以下步骤对其进行了修复:

After searching online, I fixed it by these steps:

1. 使用以下命令设置全局变量:

mysql> SET GLOBAL local_infile=1;
Query OK, 0 rows affected (0.00 sec)

1. 退出当前服务器:

mysql> quit
Bye

1. 使用本地文件系统变量连接到服务器:

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

此变量控制LOAD DATA语句的服务器端LOCAL功能.根据local_infile设置，服务器将拒绝或允许在客户端启用LOCAL的客户端加载本地数据. 要显式地使服务器拒绝或允许LOAD DATA LOCAL语句(无论在构建时或运行时如何配置客户端程序和库)，请分别在禁用或启用local_infile的情况下启动mysqld.也可以在运行时设置local_infile.

This variable controls server-side LOCAL capability for LOAD DATA statements. Depending on the local_infile setting, the server refuses or permits local data loading by clients that have LOCAL enabled on the client side. To explicitly cause the server to refuse or permit LOAD DATA LOCAL statements (regardless of how client programs and libraries are configured at build time or runtime), start mysqld with local_infile disabled or enabled, respectively. local_infile can also be set at runtime.

1. 使用您的数据库并将文件加载到表中:

mysql> use menagerie
Database changed
mysql> load data local infile '/path/pet.txt' into table pet;
Query OK, 8 rows affected, 7 warnings (0.00 sec)

行得通吗?

参考文献:

https://dev.mysql. com/doc/refman/8.0/en/load-data-local-security.html https://dev.mysql.com/doc/refman/8.0/zh-CN/source-configuration-options.html#option_cmake_enabled_local_infile https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile

这篇关于错误:已禁用加载本地数据-必须在客户端和服务器端均启用此功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！