25

I'm running MySQL 5.7 on a Windows 10 machine. I've read through all the SO threads on this topic and still haven't figured out how to get my data to load and get past this error:

Error Code: 1290. The MySQL server is running with the --secure-file-priv 
option so it cannot execute this statement

I have 1) checked the settings to change them to be able to load from the directory in which I've saved my dataset, 2) opened up MySQL as administrator and checked the command line and have confirmed that the secure file does indeed point to my directory, 3) and confirmed in the init file that it's pointing to the correct directory containing my file. I tried changing the location of the dataset so it would be in a new folder and confirmed it had been moved there with the above methods, and it still did not work.

Any and all help would be welcome, thank you.

dataelephant
  • 563
  • 2
  • 7
  • 21
  • ini file can be overridden by command-line arguments. you need to do `show variables like '%secure%'` to see what the "live" setting is. – Marc B Jun 02 '16 at 15:33
  • I've done "SHOW VARIABLES LIKE 'secure_file_priv';" and it has returned to me the directory in which my dataset is saved. Doing that in the actual command line shows the same results. – dataelephant Jun 02 '16 at 15:36
  • Running your exact command returns: "require_secure_transport" : "off", "secure_auth" : "on", "secure_file_prive" : the directory where my file is saved. – dataelephant Jun 02 '16 at 15:38
  • Did you restart MySQL after the change in the .ini file? – wchiquito Jun 02 '16 at 16:26
  • 1
    Yes, I closed it and restarted the program. I also restarted my computer. All to no avail. – dataelephant Jun 02 '16 at 17:13

6 Answers6

15

I can't reproduce the problem.

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.13    |
+-----------+
1 row in set (0,00 sec)

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0,00 sec)

-- USE ...

mysql> LOAD DATA INFILE '/var/lib/mysql-files/myfile.csv'
    -> INTO TABLE `test_files`
    -> COLUMNS TERMINATED BY ',' ENCLOSED BY '\"'
    -> LINES TERMINATED BY '\n';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv
option so it cannot execute this statement

Change file: /etc/mysql/my.cnf

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

Restart MySQL. 

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0,00 sec)

mysql> LOAD DATA INFILE '/var/lib/mysql-files/myfile.csv'
    -> INTO TABLE `test_files`
    -> COLUMNS TERMINATED BY ',' ENCLOSED BY '\"'
    -> LINES TERMINATED BY '\n';
Query OK, 3 rows affected (0,00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

See 6.1.4 Server System Variables :: secure_file_priv

wchiquito
  • 16,177
  • 2
  • 34
  • 45
  • Thanks for the response, however I realized in the end it was an issue of back instead of forward slashes *facepalm.* I gave your answer a star and the checkmark for work :) – dataelephant Jun 03 '16 at 13:44
9

This works in MacOs Sierra 10.12.6:

use the command 

mysql --help | more

and look for a line where it is written: 

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf 
~/.my.cnf

In my case I tried to create a my.cnf in the home directory but it did not work. The only solution I found is to create the file in the folder etc with 

sudo vim /etc/my.cnf

and put inside it 

[mysqld_safe]
[mysqld]
secure-file-priv = ""

Then you can check that everything works with 

select @@GLOBAL.secure_file_priv;

inside mysql and check that the value is empty and different from NULL.

Finally save files in the directory /tmp and then move them in the directory you want. Alternatively (but possibly unsafe) use 

chmod 1777 dir

where dir is the name of the directory in which you are writing the files.

Galuoises
  • 2,630
  • 24
  • 30
6

On MACOSX add .my.cnf to your home directory with content: 

[mysqld_safe]
[mysqld]
secure_file_priv=""

https://github.com/Homebrew/homebrew-versions/issues/1552

ShQ
  • 756
  • 7
  • 10
3

Note that under Windows setting the 'secure_file_priv' to a different path or disabling it altogether by setting it to:

secure_file_priv=""

may not work if the MySQL service is running on a low-privilege account (default 5.7 installation). You can change that by selecting the "Local System account" in the Services under Properties -> Log On.

Morey
  • 549
  • 7
  • 10
  • 1
    Not sure why this was down-voted when it's both accurate and useful on Windows OS. Again, *IF* you choose to set secure_file_priv="" or to a different path on your Windows and find out it doesn't work, check the account privileges under which MySQL service is running. More than likely the default account under which MySQL is running doesn't have the required privs to write to some other directory – Morey Nov 29 '18 at 20:37
  • I'm downvoting because this is not accurate to me. `secure_file_priv=""` doesn't work both in MySQL console or regular Windows console. How to give this variable an empty value? I can't find any `my.cnf` file like on Linux – Jérôme MEVEL Nov 29 '19 at 13:07
  • @Jérôme MEVEL It worked for me as stated in option 2 at this link https://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql – Morey Jan 07 '20 at 21:53
  • Thank you Morey, the permissions was the issue for me! – Len White Oct 19 '22 at 16:00
0
  1. Check OS privileges on directory you are importing from.
  2. When trying to import your data via "CVS using LOAD DATA" select use local option.
MAS9000
  • 1
0

Try flipping the \ to / for example:

'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/'

user17324749
  • 1