MySQL LOAD DATA LOCAL INFILE Disabled and How to Enable It

Two different errors tell you that LOAD DATA LOCAL INFILE is switched off, and they point at different sides of the connection. One comes from the server and says the data loading is disabled on both sides, which sounds like it covers everything; the other comes from the client library and says "restrictions on access", which sounds like a file permission problem. Neither is. There are two switches, and each one has to be on. This article reproduces both errors, turns each switch on and explains why the defaults are off. The surrounding procedure is in the MySQL import guide, and the statement itself is covered in using LOAD DATA INFILE. The server side alternative, with its own error, is the subject of the error 1290 article.

The server side switch

The variable is documented in the server system variable reference together with its default for the release you run.

local_infile is a system variable on the server, off by default in 8.4. With it off, a client that asks for a local load gets this:

mysql -h mysql -uroot -e "SET GLOBAL local_infile=0"
mysql -h mysql -uroot --local-infile=1 lab -e "LOAD DATA LOCAL INFILE 'customers.csv' INTO TABLE customers FIELDS TERMINATED BY ',' IGNORE 1 LINES" 2>&1

ERROR 3948 (42000) at line 1: Loading local data is disabled; this must be enabled on both the client and server sides

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides. The client was started with --local-infile=1, so the client side was on, and the server refused. The fix is a statement, run by an account with the privileges to set global variables:

SET GLOBAL local_infile = 1;

It takes effect at once for new statements and does not survive a restart, unless you also put local_infile=1 in the server's option file. That is a reason to prefer the statement for one-off loads: the setting reverts by itself when the server restarts, and you can revert it yourself with SET GLOBAL local_infile = 0 when the job is done.

The client side switch

The other switch lives in the client. The mysql command line client, mysqlimport, libraries like pymysql and mysqlclient, and GUI tools each have their own way of enabling it, and by default they refuse. With the server variable on and a client started without the flag, I got a different error:

mysql -h mysql -uroot lab -e "LOAD DATA LOCAL INFILE 'customers.csv' INTO TABLE customers FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES (id, name, @e, signup_date, @b) SET email = NULLIF(@e,''), balance = NULLIF(@b,'')" 2>&1

ERROR 2068 (HY000) at line 1: LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access. The same error appeared when I passed --local-infile=0 explicitly:

mysql -h mysql -uroot -e "SET GLOBAL local_infile=1"
mysql -h mysql -uroot --local-infile=0 lab -e "LOAD DATA LOCAL INFILE 'customers.csv' INTO TABLE customers FIELDS TERMINATED BY ',' IGNORE 1 LINES" 2>&1

ERROR 2068 (HY000) at line 1: LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

Error numbers below 2000 come from the server, and 2000 and above come from the client library, which tells you which side to look at. The fix on the command line is the --local-infile=1 flag. In an option file, the manual recommends the loose- prefix so that programs that do not know the option still start:

[client]
loose-local-infile=1

In pymysql the connection argument is local_infile=True, as the benchmark script in the hub uses, and other drivers have similar settings: the PHP mysqli extension has MYSQLI_OPT_LOCAL_INFILE, and Node's mysql2 takes infileStreamFactory. In a GUI client it is usually a connection property or an advanced option. When all else fails and the symptom is error 2068, the client is the side that is off.

With both on

Once both sides are enabled, the same statement works:

mysql -h mysql -uroot -e "SET GLOBAL local_infile=1; SHOW VARIABLES LIKE 'local_infile'"
mysql -h mysql -uroot --local-infile=1 lab -e "TRUNCATE customers; LOAD DATA LOCAL INFILE 'customers.csv' INTO TABLE customers FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES (id, name, @e, signup_date, @b) SET email = NULLIF(@e,''), balance = NULLIF(@b,''); SELECT COUNT(*) AS loaded FROM customers" 2>&1

Variable_name	Value
local_infile	ON
loaded
4

The variable shows ON, and the load put four rows into the table. The file was read by the client, from the client's current directory, and sent to the server, which never saw its path.

Why the defaults are off

A local load reverses the usual direction of trust. The client sends a statement naming a file, and the server replies by asking the client to send a file. That request names the path, and a client library will send any file the user running it can read. The manual describes the consequence: a modified or malicious server can ask a client for files the client did not intend to share, and in a web application that passes user-written SQL, someone who can run LOAD DATA LOCAL can read files the web server's process can read.

The practical rules from that are short. Enable local_infile on the server only while you need it. Connect with a client that has it enabled only to servers you control, and use TLS with certificate verification, --ssl-mode=VERIFY_IDENTITY, so you know whose server answers. And do not enable it in a general purpose application's connection settings on the chance that someone will want to import a file.

For clients that must have it off but need to load from one folder, the --load-data-local-dir option restricts local loads to files under that directory. It applies only when the client's own setting is off, which is an unusual arrangement and a useful one: mysql --local-infile=0 --load-data-local-dir=/srv/import can load only files placed in /srv/import.

Managed services

On RDS, Aurora and other hosted MySQL, you cannot run SET GLOBAL. The server variable is a parameter in the instance's parameter group, which you change in the provider's console or API. Changes to a dynamic parameter apply without a reboot in most services, but check your provider's documentation for the one you use. Server side LOAD DATA INFILE is not available there, since there is no file system to put a file on, so the local form and the provider's import from object storage are the options.

How to tell it worked

Two checks. SHOW VARIABLES LIKE 'local_infile' shows ON on the server. And a small test file, three rows into a scratch table, loads with Records: 3. After the real load, look at the warnings: a local load treats data errors as warnings, which is a separate gotcha covered in the import guide, and @@warning_count should be zero.

When you should leave it off

If the load can be done server side with a file in the secure_file_priv directory, as in the 1290 error article, that avoids opening the local path and keeps strict error handling. If your job runs unattended against a shared server, the better design is a pipeline that stages the file on the server or in object storage, loads it and removes it, not a standing local_infile = 1. For a laptop and a development database, which is where most local loads happen, turning it on is fine. In production, turn it on for the load and off after it, and write the two statements into the runbook so that nobody has to remember.