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

What it means

SELECT … INTO OUTFILE, LOAD DATA INFILE and LOAD_FILE() read and write files on the database server, as the server’s own operating-system user. Because that’s a security risk, the secure_file_priv setting limits them:

Error 1290 means the path in your statement is outside the allowed directory, or file operations are off. Nothing was read or written.

secure_file_priv can only be set in the server’s configuration and takes effect at restart; no SET statement changes it.

Common causes

1. Exporting to a path of your choice, such as /tmp/orders.csv, on a server that only allows /var/lib/mysql-files/.
2. Expecting the file on your own computer. INTO OUTFILE writes on the server, so on a remote server or in Docker the file ends up there, not on your Mac.
3. A hosted database (RDS, Cloud SQL, Azure…), where you have no access to the server’s file system and file operations are blocked or unavailable.

How to fix it

Find the allowed directory and use it

SELECT @@secure_file_priv;

@@secure_file_priv
/var/lib/mysql-files/

SELECT * FROM orders INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

The file mustn’t exist yet (ERROR 1086: File … already exists), and you then copy it off the server, for example with docker cp <container>:/var/lib/mysql-files/orders.csv .. Both statements also need the FILE privilege; without it you get error 1227 asking for FILE instead.

Export through the client

To get the data onto your own machine, let the client write the file. The mysql client’s batch mode prints tab-separated rows:

mysql -h <host> -u <user> -p -B -e 'SELECT * FROM shop.orders' > orders.tsv

No server file access or FILE privilege is involved.

Import through the client: LOAD DATA LOCAL

LOAD DATA LOCAL INFILE reads the file from your machine and sends it to the server, so secure_file_priv doesn’t apply. It needs local_infile on at both ends: MySQL 8’s server has it off by default (MariaDB’s has it on), which gives a different error:

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

An administrator turns it on with SET GLOBAL local_infile = ON; the mysql client needs --local-infile=1.

Change the setting, on a server you run

In the configuration file, then restart:

[mysqld]
secure_file_priv = /var/lib/mysql-files

Use a directory that only the server’s user can write to, not the data directory or /tmp.

The same number, a different option: --read-only

The MySQL server is running with the --read-only option is also error 1290: the server is a replica, or a primary set read-only (during a failover, for instance). Writes have to go to the current primary.

Reproduce it

In a temporary MySQL 8.4.11 container (secure_file_priv /var/lib/mysql-files/, removed afterwards), as root:

SELECT * FROM orders INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',';
LOAD DATA INFILE '/tmp/orders.csv' INTO TABLE orders FIELDS TERMINATED BY ',';

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

Writing to /var/lib/mysql-files/orders.csv worked, a second time gave error 1086, and loading the file back worked. LOAD_FILE('/etc/hostname'), outside the directory, returned NULL without an error. On the shared MySQL 8.4 server, an account without FILE got Access denied; you need (at least one of) the FILE privilege(s) for any path, and LOAD DATA LOCAL got the 3948 above.

A temporary MariaDB 11.4.13 container started with --secure-file-priv=/tmp gave the same number, naming MariaDB, for a path in /var/tmp:

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

With read_only on, an INSERT by an account without administrative privileges gave ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement (and the same naming MariaDB on MariaDB).

In Inlet

Inlet exports rows to CSV, JSON, SQL INSERT statements or Excel (.xlsx) as files on your Mac, so the server never writes a file and secure_file_priv doesn’t come into it. When a statement fails, Inlet shows the error and links to this page.