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.

Cahn

Does this answer your question? MySQL: Enable LOAD DATA LOCAL INFILE reset – Obstetrician

Answer 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

Gwyngwyneth

** 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. – Lenticular

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

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

Bazar

Never pass the password on the command line! Use only -p, and wait for it to prompt you for the password. – Otten

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

Overhaul

Answer 4

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;

Incorporeal

Answer 5

my.cnf file:

[client]  
local_infile=1

From the official MySQL 8.0 documentation.

Desist

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? – Ammoniacal

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. – Pirnot

@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. – Telethermometer

Answer 6

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.

Kiruna

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

Answer 7

as the question said this must be enable in client and server both, so in my case i was able to enabled that in server side

SET GLOBAL local_infile = true;

using above(it was not permanent when restarting server again it was OFF state) so i add below in my.cnf

[mysqld]
local-infile 

this works and server side is okay with that, so in the client side(mine was a spring application) i have to add

allowLoadLocalInfile=true

this at the end of connection string. after adding this worked fine below is my connection string (you can see that in the end of string)

jdbc.url=jdbc:mysql://127.0.0.1:<port>/<dbname>?autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=yes&characterEncoding=UTF-8&allowLoadLocalInfile=true

Yuille

Answer 8

how global variables like 'local_infile';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | OFF   |
+---------------+-------+

set global local_infile=true;


exit

if you don't have any password for your mysql

$->>   sudo mysql --local_infile=1 -u root -p

if you have password for your mysql

$->>   sudo mysql --local-infile=1 -u root

now done your work

LOAD DATA LOCAL INFILE  
'/home/soyanswartz/Documents/Study/My_Course/Learn_SQL/05_Day/machine-learning-with-sql/data/weather_data.csv'
INTO TABLE TBL_WEATHER  
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Thyroiditis

Answer 9

I just had this problem with PyMySQL and SQLAlchemy. Here are some things that got this to work...

1. Here is how I connect to the DB Engine...

    connect_args = ssl_args
    connect_args['local_infile'] = True
    qry_engine = create_engine('mysql+pymysql://' + db_uid + ':' + db_pw + '@' + db_addr, connect_args=connect_args,
                               pool_recycle=3600, echo=False)
   

Note that ssl_args is a dictionary that contains my ssl parameters (you can leave this out if not connecting with SSL but connect_args is expecting a dictionary.)

2. When loading data, I have to run the following...

    sql_stmt = 'SET GLOBAL local_infile = TRUE'
    common.qry_engine.execute(text(sql_stmt))
    sql_stmt = "LOAD DATA LOCAL INFILE '" + filepath + "' INTO TABLE " + self.tst_tbl.tbl.fullname
    common.qry_engine.execute(text(sql_stmt))
   

When I try this without that SET GLOBAL command I get that "must be enabled on both the client and server side" message.

Incontinent

Thanks Ben! I've updated the configuration (.cnf) file as recommended above but still got the permission issue. However your solution using SQLAlchemy engine nailed it. – Katt

Answer 10

Just set this below command in MySQL console

SET GLOBAL local_infile=1;

Then exit the console.

exit;

Now start the MySQL console with this below command

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

It worked or me.

Puritanism

Answer 11

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

Selfwinding

Answer 12

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.

2. 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)

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

Lichenology

Answer 13

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.

Councillor

Answer 14

Executing this global setting change in SQL script should help

SET GLOBAL local_infile=1;

Crossley

Answer 15

use the code:

 (LOAD DATA INFILE 'data.csv' 
INTO TABLE TABLE_THAT_WAS_CREATED)

istead of load local data, don't use "LOCAL"

and save the data.csv in C:\ProgramData\MySQL\MySQL Server 8.0\Data\TABLE_THAT_WAS_CREATED

Adriell