Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

LOAD DATA LOCAL INFILE is refused: loading local data is disabled

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

Short answer

LOAD DATA LOCAL works only when both the server and the client allow it. The server variable local_infile is off by default, and the stock mysql client library ships with local loading off too. Enable local_infile on the server, start the client with --local-infile=1, or load a file that already sits on the server.

  • Applies to MySQL 8.0, MySQL 8.4 LTS, mysql client 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Read the server side of the switch. If it is OFF, fix the server first, then the client.

    SHOW GLOBAL VARIABLES LIKE 'local_infile';
  2. 2Match what you see, then jump to its fix

    1. 1The query above shows local_infile = OFF.The server has local_infile switched offFix
    2. 2The server shows local_infile = ON and the same error still appears.The mysql client was started without local loadingFix
    3. 3A security policy forbids local_infile = 1, or a shared client should read files from one folder only.You would rather not open local loading for all filesFix

What does the error look like?

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

Cause 1 of 3

The server has local_infile switched off

1Check

The manual states that local_infile is disabled by default. A LOCAL load is accepted only if the server and the client both permit it.

SHOW GLOBAL VARIABLES LIKE 'local_infile';

2Fix

Switch it on at runtime. To keep it after a restart, set local_infile in the server's startup options or option file as well.

SET GLOBAL local_infile = 1;

3Verify

The variable now reads ON.

SHOW GLOBAL VARIABLES LIKE 'local_infile';

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The mysql client was started without local loading

1Check

In binary distributions the client library is compiled with local loading disabled, so the client has to opt in. The mysql client takes --local-infile for this, and mysqlimport uses --local.

mysql --help | grep -i local-infile

2Fix

Start the client with the option, or put it in the [client] group of an option file with the loose- prefix so other programs that do not know the option do not fail.

mysql --local-infile=1 -u myuser -p mydb

# or in my.cnf
[client]
loose-local-infile=1

3Verify

Repeat the LOAD DATA LOCAL INFILE statement.

LOAD DATA LOCAL INFILE '/home/me/data.csv' INTO TABLE t1
FIELDS TERMINATED BY ',' IGNORE 1 LINES;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

You would rather not open local loading for all files

1Check

With local loading disabled, the mysql client option --load-data-local-dir limits loads to files inside a single directory. The path comparison is case-sensitive, and the setting has no effect once local loading is enabled.

ls -l /home/me/imports/data.csv

2Fix

Leave local loading disabled and give the client the directory. As an alternative, drop LOCAL and put the file on the server host. Without LOCAL you need the FILE privilege and the path must satisfy secure_file_priv.

mysql --load-data-local-dir=/home/me/imports -u myuser -p mydb

3Verify

Run LOAD DATA LOCAL INFILE with a path inside that directory.

LOAD DATA LOCAL INFILE '/home/me/imports/data.csv' INTO TABLE t1;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Turn local_infile on only on servers and accounts that really need it, and turn it off again after a one-off import.
  • Prefer --load-data-local-dir over a global switch for scripted imports.
  • Keep the loose- prefix in option files shared by several MySQL programs.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.