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

Question

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.

Solution 1

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

Solution 2

You may check the local_infile is disabled or enable. So, you try this-

mysql> show global variables like 'local_infile';

if it shows-

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  |  OFF  |
+---------------+-------+
(this means local_infile is disable)

then enable with this-

mysql> set global local_infile=true;

Then check again and quit from mysql server with this-

mysql> exit

Now you have to connect/login server with local_infile. For this run this code from terminal command line-

mysql --local_infile=1 -u root -ppassword DB_name

now load the data from local file-

mysql> load data local infile 'path/file_name.extention' into table table_name;

It's work on my pc. you may try this. thanks.

Solution 3

This is what I had to do fix this issue on Ubuntu 20.04 / MySQL 8:

1. nano /etc/mysql/mysql.conf.d/mysqld.cnf
2. in the [mysqld] section add this line:

local_infile    = 1

3. add to the bottom of the file these two lines:

[client]
local_infile    = 1

4. run this command from my client: SET GLOBAL local_infile=1;

NOTE: revised the above to include extra step to make sure local_infile=1 is added to the [mysqld] section. This saves me having to run SET GLOBAL local_infile=1; after each reboot.

Solution 4

my.cnf file:

[client]  
local_infile=1

From the official MySQL 8.0 documentation.

Solution 5

For default installation of MySQL 8.0.20:

i) Look for the initialization file located at C:\ProgramData\MySQL\MySQL Server 8.0\my.ini.

ii) Open my.ini file with notepad.

iii) Look for the headers [client], [mysql] under the CLIENT section & [mysqld] under the SERVER section.

iv) Add the following statement under each header:

local_infile=ON

v) Save the file and restart MySQL80 service under the Windows local services.

It should work.

View more solutions

Comments

[pauljohn32]

MySql documents are not very clear. You set [client], how to make the server side and client sides both come out right? On Ubuntu with MySql 8, the my.cnf file is just a pair of directories referring to /etc/mysql/mysql.conf.d and /etc/mysql/conf.d. I'm trying the setting in the mysql.conf.d/mysql.cnf file. But, honestly, how can it make sense to have a single folder with same-named "mysql.cnf" files in top level, and in each sub directory. And no change for server?

[Adam Friedman]

Find whichever config file has the tag [mysqld] and is being used. Add the same global config under that tag as well as under the [client] tag.

[aderchox]

@AdamFriedman Thanks for the point! Server configuration file for me was in /etc/mysql/mysql.conf.d/mysqld.cnf and I added the local_infile=1 under [mysqld] and it's working now! I can't currently edit this answer because the edit queue is full, but it's incomplete without the mysqld pair.

[mckenzm]

** A lot ** of people are getting this issue after updating to "Focal". It is breaking a lot of utility scripts and reports. This question is at the top of Google search for the message in October 2020.

[mckenzm]

Works without --local-infile=1 if the client is localhost? For us this has meant no code changes for local scripts which is good.

[stoneshishang]

Thank you! this works for me combining the answers from this and this