Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

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

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement means the server limits file import and export to a set place, and your LOAD DATA, SELECT ... INTO OUTFILE or LOAD_FILE() named a file outside it. Read the secure_file_priv variable. Then use the directory it names, export from the client side, or change the variable in the option file, which needs a server restart.

What the error means

The MySQL error reference lists error 1290 as ER_OPTION_PREVENTS_STATEMENT, SQLSTATE HY000, with the message "The MySQL server is running with the %s option so it cannot execute this statement". Here the option is --secure-file-priv.

The manual describes the variable: "This variable is used to limit the effect of data import and export operations, such as those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. These operations are permitted only to users who have the FILE privilege."

It can be set three ways:

  • "If empty, the variable has no effect. This is not a secure setting."
  • "If set to the name of a directory, the server limits import and export operations to work only with files in that directory. The directory must exist; the server does not create it."
  • "If set to NULL, the server disables import and export operations."

The default "is platform specific and depends on the value of the INSTALL_LAYOUT CMake option". For STANDALONE it is empty. For DEB, RPM and SVR4 it is /var/lib/mysql-files. Otherwise it is mysql-files under the CMAKE_INSTALL_PREFIX value. So the default differs between builds, and a script that worked on a server with an empty default can fail on one built with DEB, RPM or SVR4. Read the value on your own server.

The variable cannot be changed while the server runs. The manual's property table lists it as global scope and Dynamic: No.

The two statements behave differently. For SELECT ... INTO OUTFILE, the manual says the file is "created on the server host", that "file_name cannot be an existing file", and that "If the secure_file_priv system variable is set to a nonempty directory name, the file to be written must be located in that directory." The file is owned by the operating system user running mysqld. For LOAD DATA without LOCAL, "the file must be located on the server host", and "If the variable value is a nonempty directory name, the file must be located in that directory."

Likely causes

Cause What you see How to confirm
File path is outside the directory secure_file_priv names Error 1290 on a path such as /tmp/report.csv SHOW VARIABLES LIKE 'secure_file_priv'; returns a directory, and your path is not inside it
Variable is NULL Every import and export statement is refused The value column shows NULL
Moved to a server with a different default A script that worked on the old server fails on the new one The new server's value is a directory such as /var/lib/mysql-files, the old one was empty
Using server-side LOAD DATA for a file on the client The file is on a laptop or application host, not the database host The path exists on the client only

Prove the cause before changing anything

All of these are read-only.

1. Read the variable.

SHOW VARIABLES LIKE 'secure_file_priv';

Good result for an export: a directory path, and your file name is inside it. Bad results: a different directory from the one you used, or NULL. An empty value means the variable has no effect, so it is not what limits the path.

2. Check the privilege. The manual says these operations are permitted only to users who have the FILE privilege, and that SHOW GRANTS shows "the privileges granted to the current user".

SHOW GRANTS;

Look for FILE in the list. Without it the account cannot use these statements at all, whatever the path.

3. Find which option files the server reads. The manual says "For mysqld, use --verbose and --help", and the help message "indicates which files it looks for and which option groups it recognizes". On Unix, the option files listed include /etc/my.cnf and /etc/mysql/my.cnf.

mysqld --verbose --help

4. Read the server error log at the last start. The manual says the server checks the value at start-up and "writes a warning to the error log if the value is insecure". A non-NULL value is insecure if it is empty, is the data directory or a subdirectory of it, or is "a directory that is accessible by all users".

Fix

Option 1: use the directory the server permits

If secure_file_priv names /var/lib/mysql-files, write and read there. No server setting changes. The manual says the file name "cannot be an existing file", so the statement never overwrites anything.

First check that the name you chose is free. ls -l is described in its manual page as "use a long listing format":

ls -l /var/lib/mysql-files/orders.txt

Good result: ls reports that the file does not exist. If it lists a file, choose another name and stop here: that file is not yours to remove.

Risk: the next statement writes a new file on the database host that holds every row it selects. Anyone who can read that directory can read the data, and the file stays there until someone removes it.

SELECT * FROM orders INTO OUTFILE '/var/lib/mysql-files/orders.txt';

The statement needs the FILE privilege and the operating system account that runs mysqld must be able to write to that directory. Without a FIELDS clause the output is plain text, so the file name ends in .txt.

To undo it: run this only if the statement reported success, because only then did the statement create the file. Copy out what you need first, then remove that one file:

rm /var/lib/mysql-files/orders.txt

Option 2: export from the client

If you only need the data on your own machine, the mysql client can write the result without the server writing a file. --execute will "Execute the statement and quit." and its default output format "is like that produced with --batch", which prints "results using tab as the column separator, with each row on a new line."

Choose a file name that does not exist yet: > replaces an existing file.

mysql -u app_user -p -D appdb -e "SELECT * FROM orders" > orders.tsv

For import in the other direction, the manual says LOAD DATA LOCAL reads a file on the client: "you do not need the FILE privilege, and the file can be located in any directory the client program can access". It works only if both server and client permit it, and the local_infile variable defaults to OFF. The manual lists security issues with LOCAL, including that "clients should not connect to untrusted servers". Treat enabling it as a separate decision.

Option 3: change secure_file_priv

Use this when the permitted directory is not workable, for example a backup job that must write to a mounted volume. The variable can only be set at start-up, so the change is made in an option file and needs a restart.

First find the option file the server reads. The manual says the help message of mysqld --verbose --help "indicates which files it looks for and which option groups it recognizes", so the list names places the server looks, not files that exist. Choose a file from the list, which may be /etc/my.cnf or /etc/mysql/my.cnf, and use it wherever <option_file> appears below:

mysqld --verbose --help

Then look for an existing secure_file_priv line, in the shell. The manual says "If multiple instances of a given option are found, the last instance takes precedence", so a line in a file the server reads later overrides yours:

grep -RnE 'secure[-_]file[-_]priv' /etc/my.cnf /etc/mysql/

grep -R reads "all files under each directory, recursively" and, unlike -r, follows symbolic links; -n prefixes each line with its line number. The pattern accepts a dash or an underscore, because the manual says "within variable names, dashes and underscores may be used interchangeably". Also search any file or directory named by an !include or !includedir line. If a file holds the line, use that file as <option_file> below. If no file holds one, keep the file you chose as <option_file>.

Then check whether the file you chose exists, and save it if it does:

ls -l <option_file>

Write down what it reported: either it listed the file, or it said the file does not exist. The back-out below depends on this answer.

If ls listed the file, save a copy:

cp -p <option_file> <option_file>.2026-11-22

If ls said the file does not exist, there is no file to save. In either case, print and keep the current value, in a mysql session:

SHOW VARIABLES LIKE 'secure_file_priv';

Disruptive step: the next two actions edit the option file and restart MySQL, which disconnects every session and stops the service for the duration of the restart. If the directory named does not exist, the manual says "the server writes an error message to the error log and exits", so a typing mistake leaves MySQL down. Use a directory that already exists and that the operating system user running mysqld can write to; do not create one as part of this fix. Do not set the value empty: the manual calls that "not a secure setting". Choose a directory that is not the data directory or a subdirectory of it.

In the [mysqld] group of the option file, add or change one line. The manual's format is opt_name=value with the group in square brackets:

[mysqld]
secure_file_priv=/srv/mysql-exports

Then restart. The manual shows systemctl restart mysqld, and says to use mysql rather than mysqld as the service name on Debian-based and SLES systems:

systemctl restart mysqld

Run SHOW VARIABLES LIKE 'secure_file_priv'; again and confirm the new directory.

To back out: go by what the first ls -l <option_file> reported, which you wrote down. Use the service name from the restart above.

Use this only if the first ls -l <option_file> listed the file. First prove the saved copy exists:

ls -l <option_file>.2026-11-22

If it lists the saved copy, put it back under its original name and restart:

cp -p <option_file>.2026-11-22 <option_file>
systemctl restart mysqld

If the saved copy is not listed, stop: do not remove anything, because the file existed before your change. Edit the file and delete the secure_file_priv line you added, then restart.

Use this only if the first ls -l <option_file> said the file does not exist. In that case you created the file, and you may remove that one file, then restart:

rm <option_file>
systemctl restart mysqld

Then run SHOW VARIABLES LIKE 'secure_file_priv'; and compare it with the output you saved.

Example (illustrative): On db01, MySQL 8.4 was installed from an RPM package, so secure_file_priv is /var/lib/mysql-files. A nightly report script runs SELECT * FROM orders INTO OUTFILE '/tmp/orders.txt' and fails with ERROR 1290 (HY000). The same script worked on db00, whose value is empty. SHOW VARIABLES LIKE 'secure_file_priv'; on db01 shows the directory, and /tmp/orders.txt is outside it. The team changes the script to write /var/lib/mysql-files/orders.txt and copies the file out afterwards. No server change, no restart.

Confirm the fix

  1. The SELECT ... INTO OUTFILE or LOAD DATA statement now completes without error 1290.
  2. The exported file exists in the permitted directory, owned by the operating system user that runs mysqld.
  3. If you changed the option file, SHOW VARIABLES LIKE 'secure_file_priv'; shows the new value after the restart, and the error log shows no warning about it.

What to watch afterwards

  • Scripts with hard-coded paths such as /tmp fail again after a move to a packaged install or an upgrade that changes the setting.
  • Keep the permitted directory out of web-served locations and the data directory.
  • Check who holds the FILE privilege. The manual warns that FILE "can be abused to read into a database table any files that the MySQL server can read on the server host".
  • Remove exported files you no longer need, because the server will not overwrite an existing file.

Risks and limits

  • These commands have not been run or lab-tested.
  • Package defaults vary by installation layout; read the variable on your own server instead of assuming the default.
  • A restart interrupts every connection. Plan it for a maintenance window.
  • MariaDB has its own manual and is not covered.

Sources