This is a trouble shooting guide to deal with secure-file-priv database server global parameter in MySQL while loading data from datafiles (.csv, .txt) to the database.
- Aim of the guide
- Introduction
- Solution 1: For all OS Systems
- Solution 2: General solution for Windows OS (it can work also in other OS)
- Solution 3: For MacOS-like Systems
- Annex: MySQL 8.0 Official documentation - Server System Variables
While working with MySQL Server we may want to load datafiles (.csv, .txt, etc) into a database. In order to load data massively we can use LOAD DATA INFILE ... SQL statement.
Depending on the database server configuration, we may experience some trouble with the process of loading data due to the value of a database server global parameter called secure-file-priv. The purpose of the current guide is to help dealing with it.
A database parameter is a configuration setting of the database server which specifies some aspect about how it works. For instance there are some parameters regarding the use of ram memory concerning the manage of it, allocation, the size of the buffer, memory liberation, etc. Other parameters specify the amount of user concurrency, query concurrency, etc.
The parameters can be local or global. If local, they apply to the client's active session and may differ from client connection to client connection; a database server global parameter determines a database behaivor for all current and further connections.
Each parameter, local or global, is defined with a value. Global parameters come with a value by default when the database server is installed but they can be changed afterwars as desired. When we change the value for a global parameter, we need to reset the database server service (or at least the database server connection) in order to make it apply.
Local parameters come also with a default value that can be changed by the client using SQL commands in the command line or IDE. The change will apply inmediatly in the current database connection.
In detal, secure-file-priv determines the server's local path from where datafiles can be uploaded to the database.
Note
The specified pathfile is at the database server, not at the client side. For academic purposes, it is common to use the same computer with the role of database server and database client at the same time; but for many reasons, this shouldn't be like that for professional intentions.
Check official MySQL 8.0 Documentation for further information
To solve secure-file-priv configuration, there is an easy crossplatform solution (MacOS, Windows OS, Linux-like OS) which doesn't require to modify any database server configuration:
- Inside a connection of MySQL Workbench IDE editor, do:
/*
Set the global variable local_infile to 1 to be able to load files from the client machine.
Execute the following sentence:
*/
SET global local_infile = 1;-- See the directory path where to place the files to be loaded and --> place them there
SHOW VARIABLES LIKE "secure_file_priv";Example:
- LOAD DATA INFILE with full absolute path of the file. Use / instead of \ to specify the path folder separator (for all OS).
-- Example:
LOAD DATA INFILE '/var/lib/mysql-files/data.csv' INTO TABLE my_table
FIELDS TERMINATED BY ';';- Only for Unix-Like systems (MacOS and Linux):
"When you try to load the file, if secure_file_priv is empty you can choose every folder, that you workbench or mysql client has access to."
Source: https://stackoverflow.com/questions/65135919/secure-file-priv-null-in-mysql-workbench-mac
If receiving: Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
Try to chmod 777 the file to be loaded and to the containing folder and try the LOAD DATA INFILE again.
- Try to add the keyword LOCAL after LOAD DATA:
LOAD DATA LOCAL INFILE "text.txt" INTO TABLE my_table;- For the ones who have installed the MySQL database server without XAMPP (Tested for MySQL 8.* under Windows 10)
The my.ini file can be found in a different location:
C:\ProgramData\MySQL\MySQL Server 8.x\my.ini
A longer description can be found here: https://stackoverflow.com/a/20136523/1316649
The variable to be changed is called secure-file-priv and it must be unset inside the file as follows:
[mysqld]
...
secure-file-priv=
...
- If you installed the MySQL database server by using XAMPP, change the global variable configuration on my.cnf file (you will find it on the xampp installation path folder). To disable it you need to add secure-file-priv = "" under the mysqld config group.
Example:
[mysqld] secure-file-priv = ""
More info at:
https://stackoverflow.com/questions/37543177/mysql-making-secure-file-priv-option-to-null
https://stackoverflow.com/questions/32737478/how-should-i-resolve-secure-file-priv-in-mysql
https://stackoverflow.com/questions/34102562/mysql-error-1290-hy000-secure-file-priv-option
For those of you still in problems to load files you could also try this (particulary MacOS users):
- Make sure you have local_infile parameter set to ON in the database sever:
SHOW GLOBAL VARIABLES LIKE 'local_infile';
If it is set to 'OFF', you can set it to 'ON' using the MySQL terminal, the Workbench IDE or similar at the client side:
SET GLOBAL local_infile = 'ON';- Make sure you have local_infile also activated in your database client (Workbench IDE).
Go to Right click -> Edit Connection:
Go to Advanced tab and add local_infile config value as specified:
Note: In some systems could also work with local_infile = 1
-
Reset the connection (open and close Workbench IDE)
-
Work with LOCAL parameter in LOAD DATA command:
LOAD DATA LOCAL INFILE ...
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.
secure_file_priv may be set as follows:
- 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 value is platform specific and depends on the value of the INSTALL_LAYOUT CMake option, as shown in the following table. To specify the default secure_file_priv value explicitly if you are building from source, use the INSTALL_SECURE_FILE_PRIVDIR CMake option.
The server checks the value of secure_file_priv at startup and writes a warning to the error log if the value is insecure. A non-NULL value is considered insecure if it is empty, or the value is the data directory or a subdirectory of it, or a directory that is accessible by all users. If secure_file_priv is set to a nonexistent path, the server writes an error message to the error log and exits.