Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

LOAD DATA INFILE or INTO OUTFILE blocked by --secure-file-priv

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

Short answer

The server only reads and writes files inside the folder set in secure_file_priv. The value cannot be changed while MySQL runs. Run SHOW VARIABLES LIKE 'secure_file_priv', put your file in that folder, or load the file from the client side with LOAD DATA LOCAL INFILE.

  • Applies to MySQL 8.4, Linux package install
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print the value. A folder name means files must live there; NULL means import and export are switched off; empty means no restriction.

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

    1. 1The variable shows a path such as /var/lib/mysql-files/.The file is outside the folder named by secure_file_privFix
    2. 2SHOW VARIABLES prints NULL for secure_file_priv.secure_file_priv is NULL, so server-side files are disabledFix
    3. 3SET GLOBAL secure_file_priv fails, or the new value does not apply.You want a different folder and must change the settingFix

What does the error look like?

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

Cause 1 of 3

The file is outside the folder named by secure_file_priv

1Check

With a non-empty directory name, the server limits import and export to that directory. The default on DEB and RPM packages is /var/lib/mysql-files.

SHOW VARIABLES LIKE 'secure_file_priv';

2Fix

Copy the file into that folder so the server can read it, and use the full path. For an export, write into the same folder.

-- shell first: sudo cp data.csv /var/lib/mysql-files/
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
  INTO TABLE my_table
  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;

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

3Verify

The statement completes; the first load reports the number of rows.

SELECT COUNT(*) FROM my_table;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

secure_file_priv is NULL, so server-side files are disabled

1Check

NULL disables import and export operations completely, so no folder will work.

SHOW VARIABLES LIKE 'secure_file_priv';

2Fix

Use the client-side form, LOAD DATA LOCAL INFILE. The client reads the file and sends it, so no FILE privilege is needed and the file can be in any folder the client can read. It needs local_infile enabled on both server and client.

-- local_infile must be enabled on the server and in the client
LOAD DATA LOCAL INFILE '/home/me/data.csv'
  INTO TABLE my_table
  FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;

3Verify

Rows are loaded from the client machine.

SELECT COUNT(*) FROM my_table;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

You want a different folder and must change the setting

1Check

The variable is not dynamic: it can only be set at startup (command line --secure-file-priv=dir_name, or the option file). The directory must exist; the server does not create it, and a nonexistent path makes the server log an error and exit.

SHOW VARIABLES LIKE 'secure_file_priv';

2Fix

Create the folder, then set the option under [mysqld] in the option file and restart the server.

# my.cnf, in [mysqld]
secure-file-priv=/srv/mysql-import

3Verify

After the restart the variable shows the new path.

SHOW VARIABLES LIKE 'secure_file_priv';

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Keep import and export files in one agreed folder that matches secure_file_priv.
  • Do not set secure_file_priv to empty on a server others can reach; the manual calls that not secure.
  • Prefer LOAD DATA LOCAL or a client-side export (mysql -e "SELECT ..." > file) when the file lives on another machine.

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.