Error: Loading local data is disabled - this feature must be enabled on both client and server sides

Question

I don't understand other people's answers to similar questions, except for the most obvious ones, like the following:

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)

I mean the exact code is provided. I would be grateful if someone could walk me through what I need to do to enable local data on both the "client" side and the "server" side. It seems that I have local data enabled on the client side, but I don't know what instructions I need to give my computer to enable "server side". I'm not tech savvy at all, I just want to be able to upload data to 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 a command line shell.

Answer 1

If the LOCAL feature is disabled on the server or client side, a client attempting to issue a LOAD DATA LOCAL statement will receive the following error message:

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

I encountered the same problem when I wanted to load the text file pet.txt into the pet table following Mysql's tutorial: https://dev.mysql.com/doc/refman/8.0/en/ loading-tables.html

After searching online, I fixed it with the following steps:

1. Use the following command to set global variables:

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

2. Exit the current server:

mysql> quit
Bye

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

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

This variable controls the server-side LOCAL functionality of the LOAD DATA statement. Depending on the local_infile setting, the server denies or allows clients with LOCAL enabled on the client to load local data. To explicitly cause the server to deny or allow LOAD DATA LOCAL statements (regardless of how the client program and libraries are configured at build time or run time), 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)

Is it effective?

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