MySQL Error 1290 The Server Is Running With secure file priv

You run a LOAD DATA INFILE, with a path that exists and a user with every privilege, and MySQL answers ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement. The message names an option and says nothing about the directory you are allowed to use, which is what you need. The cause is a deliberate restriction and the fix is usually to put the file in the right place. This article shows the setting, the two fixes, and why I would not change the setting to make the error go away. The whole import path is in the MySQL import guide.

What the error means

secure_file_priv limits the server's file operations: LOAD DATA INFILE, SELECT ... INTO OUTFILE and the LOAD_FILE() function. When it has a directory as its value, those operations may only read or write files inside it. When it is NULL, they are disabled entirely, and when it is an empty string they are unrestricted, which the manual calls insecure. A server started from a package or a Docker image almost always has a directory.

The manual's description of secure_file_priv and the LOAD DATA page both state the restriction. The reason is what these statements can do. A user with the FILE privilege can read any file the server process can read, such as configuration files, into a table, and write files with INTO OUTFILE. The restriction confines that to one folder.

The most common cause and its fix

You are loading from a directory that is not the allowed one. First read the setting:

mysql -h mysql -uroot -e "SHOW VARIABLES LIKE 'secure_file_priv'; SHOW VARIABLES LIKE 'local_infile'"

Variable_name	Value
secure_file_priv	/var/lib/mysql-files/
Variable_name	Value
local_infile	ON

secure_file_priv is /var/lib/mysql-files/, and local_infile is ON because I had enabled it for other tests. The statement that failed named /tmp/customers.csv:

mysql -h mysql -uroot lab -e "LOAD DATA INFILE '/tmp/customers.csv' INTO TABLE customers FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' IGNORE 1 LINES" 2>&1

ERROR 1290 (HY000) at line 1: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

The fix is to put the file in the allowed directory and load it from there. With the file in place at /var/lib/mysql-files/customers.csv, the same statement with the new path loaded and selected four rows:

mysql -h mysql -uroot lab -e "TRUNCATE customers; LOAD DATA INFILE '/var/lib/mysql-files/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 id, name, email FROM customers ORDER BY id" 2>&1

id	name	email
1	Ana Silva	[email protected]
2	Smith, Jane	[email protected]
3	Bo Chen	NULL
4	Cy Dubois	[email protected]

Two things about that. The path is on the database server's filesystem, so if your file is on a laptop and MySQL is elsewhere, you have to transfer it first, with scp, a shared mount or, for a container, docker cp file container:/var/lib/mysql-files/. And the file must be readable by the operating system user that the server runs as, normally mysql. A permission problem at that level shows up as ERROR 1290 too, or as Can't get stat of '...' (OS errno 13 - Permission denied), so check the file's owner and mode if the path is right and the error persists.

The other fix: LOCAL

If you cannot or should not put files on the server, read them on the client with LOAD DATA LOCAL INFILE. secure_file_priv does not apply to local loads, because the file is not read by the server. The cost is that local loading must be enabled on both the server and the client, which has its own error, covered in the article on LOCAL being disabled, and it changes error handling: a local load is treated as IGNORE, so values the server would reject become warnings, as the hub article demonstrates with a file of US dates that all became 0000-00-00.

I would choose between the two on that second point, not on convenience. If you want strict errors, which is the right default for a controlled load, use the server side form with the file in the allowed directory. If you want the convenience of loading from your own machine, use LOCAL and check @@warning_count after every load.

Changing the setting

secure_file_priv is read only at server startup. Trying to change it from a client fails, and the system variable reference documents it as a startup option:

ERROR 1238 (HY000) at line 1: Variable 'secure_file_priv' is a read only variable

On a managed service such as RDS or Cloud SQL, you cannot change it at all, and server side LOAD DATA INFILE is unavailable. The route there is LOAD DATA LOCAL INFILE, with local_infile set in the parameter group, or the provider's import from object storage.

Checking the directory from the operating system

The variable names a path, and a path is only useful if it exists and is writable by the right account. On the server host, list it and look at the owner:

ls -ld /var/lib/mysql-files

On the Docker image it is owned by the mysql user with mode 750, which means only that user and its group can read it, and a file copied in as root must be made readable by the server before the load works. When you copy with docker cp, the file arrives with the owner and mode it had on the host: mine came in as user id 501 with mode 644, which the server can read, but a file with mode 600 would be unreadable to the server even though it sits in the right place. chmod 644 on the copied file, or chown mysql, fixes it. That is a likely next failure once the 1290 error is solved, and the server then reports a different message, which is worth reading carefully.

How to tell it worked

After moving the file and rerunning, three checks. Records: n in the summary must equal the number of data lines in the file counted by a CSV aware tool. SHOW WARNINGS should be empty. And a query of a few known rows confirms that the contents match. If you moved the file with a shell command, compare its checksum on both sides, because a partial copy produces a load that succeeds with fewer rows.

Related errors that look similar

ERROR 1045 (28000): Access denied for user on a LOAD DATA INFILE means the account lacks the FILE privilege, which is a grant and not a path problem. ERROR 1290 with the --read-only option is a different statement about the server being read only. ERROR 29 (HY000): File '...' not found (OS errno 2) is the file genuinely missing from the server's filesystem, which is what you get when you point a server side load at a client path inside the allowed directory.

When you should not change secure_file_priv

If the load is a one-time task, move the file, do not touch the setting. If the job runs often, give it its own directory under the allowed one, with a script that places the file there and removes it afterwards. If the file contains sensitive data, remember that a file sitting on the database host's disk is a copy that you have to delete. And if what you wanted was to export, since INTO OUTFILE has the same restriction, the answer is the same directory, or better, a client side export with mysql -e "SELECT ..." > file.tsv that avoids server file access altogether. For CSV specifically, mysqldump --tab and the MySQL Shell export utilities are the options, and the PostgreSQL equivalent has the same server-or-client split.