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

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:

my.cnf file:

[client]  
local_infile=1

From the official MySQL 8.0 documentation.

Solution 4:

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.