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

问题描述

我不明白其他人对类似问题的回答，除了最明显的问题，例如下面的问题:

mysql>设置全局 local_infile=1;查询正常，0 行受影响(0.00 秒)mysql>显示全局变量，如local_infile"；+--------------+-------+|变量名 |价值 |+--------------+-------+|local_infile |开 |+--------------+-------+1 行(0.01 秒)

我的意思是提供了确切的代码.如果有人能一步一步地引导我了解在客户端"端和服务器"端启用本地数据需要做什么，我将不胜感激.好像我在客户端启用了本地数据，但我不知道我需要给我的电脑什么指令才能启用服务器端".我一点也不精通技术，我只是希望能够将数据上传到 MySQL 工作台.

ERROR 3948 (42000):加载本地数据被禁用；这必须在客户端和服务器端启用

创建桌子玩具(uniq_id VARCHAR(1000),产品名称 VARCHAR(1000),制造商 VARCHAR(1000),价格 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),customer_who_bought_this_item_also_bought VARCHAR(1000),描述 VARCHAR(1000),产品信息 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),卖家 VARCHAR(1000));加载数据本地文件‘/Users/BruddaDave/Desktop/amazonsample.csv’ INTO TABLE玩具以‘,’结尾的字段以‘\n’结尾的行忽略 1 行(uniq_id、product_name、制造商、价格、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_questions、customers_answering_questions、customers_answering_questions、customer_answering_questions、customer_views_reviews之后;

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

解决方案

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

>

ERROR 3950 (42000): 加载本地数据被禁用；这一定是在客户端和服务器端都启用

当我想按照 Mysql 教程将文本文件 pet.txt 加载到 pet 表时遇到了同样的问题:https://dev.mysql.com/doc/refman/8.0/en/loading-tables.html

在网上搜索后，我按以下步骤修复了它:

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

mysql>设置全局 local_infile=1;查询正常，0 行受影响(0.00 秒)

2. 退出当前服务器:

mysql>退出再见

3. 使用 local-infile 系统变量连接到服务器:

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

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

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

mysql>使用动物园数据库已更改mysql>将数据本地 infile '/path/pet.txt' 加载到表 pet 中；查询正常，8 行受影响，7 条警告(0.00 秒)

有用吗?

参考文献:

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

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)

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

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

解决方案

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

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. set the global variables by using this command:

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

2. quit current server:

mysql> quit
Bye

3. connect to the server with local-infile system variable :

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

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.

4. use your Database and load the file into the table:

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)

Does it work?

References:

https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html https://dev.mysql.com/doc/refman/8.0/en/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屋！