错误:加载本地数据被禁用 - 这必须在客户端和服务器端启用

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

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之后;

>

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

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

Mysql目录里的ibtmp1文件过大造成磁盘占满的解决办法

ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_path参数指定文件的路径，文件名和大小，默认配置为ibtmp1:12M:autoextend，也就是说在文件系统磁盘足够的情况下，这个文件大小是可以无限增长的。 为了避免ibtmp1文件无止境的暴涨导致...

2025-01-02 数据库问题

151

SQL 子句“GROUP BY 1"是什么意思?意思是?

What does SQL clause quot;GROUP BY 1quot; mean?(SQL 子句“GROUP BY 1是什么意思?意思是?)...

2024-04-16 数据库问题

62

MySQL groupwise MAX() 返回意外结果

MySQL groupwise MAX() returns unexpected results(MySQL groupwise MAX() 返回意外结果)...

2024-04-16 数据库问题

13

MySQL SELECT 按组最频繁

MySQL SELECT most frequent by group(MySQL SELECT 按组最频繁)...

2024-04-16 数据库问题

16

为什么 Mysql 的 Group By 和 Oracle 的 Group by 行为不同

Why Mysql#39;s Group By and Oracle#39;s Group by behaviours are different(为什么 Mysql 的 Group By 和 Oracle 的 Group by 行为不同)...

2024-04-16 数据库问题

13

MySQL GROUP BY DateTime +/- 3 秒

MySQL GROUP BY DateTime +/- 3 seconds(MySQL GROUP BY DateTime +/- 3 秒)...

2024-04-16 数据库问题

14