70

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.

Giacomo1968
  • 24,837
  • 11
  • 67
  • 96
sofrustrated
  • 823
  • 1
  • 5
  • 4
  • Does this answer your question? [MySQL: Enable LOAD DATA LOCAL INFILE reset](https://stackoverflow.com/questions/54623557/mysql-enable-load-data-local-infile-reset) – danblack Jan 30 '20 at 21:39

9 Answers9

102

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)
  1. quit current server: 
mysql> quit
Bye
  1. 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.

  1. 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

coderpc
  • 3,603
  • 5
  • 42
  • 72
Zhi-Qiang Ni
  • 1,021
  • 1
  • 3
  • 3
  • 1
    ** 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 Oct 05 '20 at 02:16
  • Works without --local-infile=1 if the client is localhost? For us this has meant no code changes for local scripts which is good. – mckenzm Oct 05 '20 at 02:26
36

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.

Fuzi
  • 461
  • 4
  • 6
10

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
  1. add to the bottom of the file these two lines:
[client]

local_infile    = 1
  1. 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.

Appaji Chintimi
  • 464
  • 1
  • 5
  • 16
Wonko the Sane
  • 526
  • 3
  • 12
  • 26
8

my.cnf file:

[client]  
local_infile=1

From the official MySQL 8.0 documentation.

Kate Orlova
  • 2,899
  • 5
  • 10
  • 29
Breky
  • 89
  • 2
  • 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? – pauljohn32 Apr 03 '20 at 21:56
  • 1
    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. – Adam Friedman Apr 13 '20 at 00:39
  • 2
    @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. – aderchox Apr 28 '20 at 02:11
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.

MySQL New User
  • 51
  • 1
  • 1
  • Thank you! this works for me combining the answers from [this](https://stackoverflow.com/a/55973574/12097072) and [this](https://stackoverflow.com/a/32737616/12097072) – stoneshishang Sep 30 '21 at 14:06
5

Type the following command on the terminal -

SHOW GLOBAL VARIABLES LIKE 'local_infile';

If it's 0 oe false then type the following command -

SET GLOBAL local_infile = TRUE;
Mureinik
  • 277,661
  • 50
  • 283
  • 320
Divyansh Sinha
  • 51
  • 1
  • 1
0

After trying all the steps outlined above, I was still getting Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access. The solution was to eliminate the word "LOCAL" in the SQL statement:

load data infile...

Dale Thompson
  • 85
  • 1
  • 7
0

For those who are stuck at step 3:

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

like I was, this is what I did:

  1. type

cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

in the command prompt.

  1. type

mysql --local_infile=1 -u root -pABC menagerie

where ABC is your password for MySQL and menagerie is the database you're working on (replace ABC & menagerie with whatever is your password & database name respectively)

  1. MySQL will open in command prompt. You can type your commands like:

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)

and it should work...

Adarsh Mohapatra
  • 1
0

on Ubuntu 20.04 / MySQL 8: This approach resolved the issue for me:

  1. systemctl stop mysql
  2. nano /etc/mysql/mysql.conf.d/mysqld.cnf
  3. unter [mysqld] add local_infile =1
  4. systemctl start mysql or restart mysql if you did stop it.
Sir_Lucky
  • 1
  • 1
  • 2