3

Windows 2007 MySQL 5.7

Receiving error :

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

So I assumed that it was simply a privilege error for the directory that I had stored my DB.

So I ran:

SHOW VARIABLES LIKE "secure-file-priv";

and returned:

Empty set (0.00 sec)

so I searched for "my.ini" my.ini is located in C:\ProgramData\MySQL\MySQL Server 5.7

MySQL is installed in C:\Program Files (x86)

I made a copy of my Links.csv into the folder location of my.ini and the error still returned.

Script:

mysql> LOAD DATA INFILE 'Links.csv' INTO TABLE Links;

  • Check [6.1.5 Server System Variables::secure_file_priv](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv). – wchiquito Feb 22 '17 at 14:44
  • I actually already had looked through that document, which is what brought me here. It's not intuitive and I'm new to MySQL(database creation in general). I don't under stand how to set up the command. If it helps I am running 5.7.14 –  Feb 22 '17 at 17:53
  • See [http://stackoverflow.com/a/37614254/1316440](http://stackoverflow.com/a/37614254/1316440). – wchiquito Feb 22 '17 at 18:45
  • I attempted to edit the --secure-file-priv path with: secure_file_priv="Path\To\File"; which returned a syntax error I have attempted to disable --secure-file-priv with: secure_file_priv=""; which also returns syntax error. I also attempted to manually edit my.ini by changing the secure-file-priv path,deleting the path and leaving just the quotes and by commenting it out the secure-file-path line. –  Feb 22 '17 at 20:52

1 Answers1

1

I'm using Windows 10.

Check:

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.7.17-log |
+------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+--------------------------------+
| Variable_name    | Value                          |
+------------------+--------------------------------+
| secure_file_priv | V:\PATH\TO\MySQL Server\Files\ |
+------------------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT `VARIABLE_VALUE`
    -> FROM `performance_schema`.`global_variables`
    -> WHERE `VARIABLE_NAME` = 'secure_file_priv';
+--------------------------------+
| VARIABLE_VALUE                 |
+--------------------------------+
| V:\PATH\TO\MySQL Server\Files\ |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT @@GLOBAL.secure_file_priv;
+--------------------------------+
| @@GLOBAL.secure_file_priv      |
+--------------------------------+
| V:\PATH\TO\MySQL Server\Files\ |
+--------------------------------+
1 row in set (0.00 sec)

If you need to change the path, you must do it in the my.ini file:

# Secure File Priv.
secure-file-priv="V:/NEW/PATH/TO/MySQL Server/Files"

then restart MySQL: (in my case):

V:\>net stop MySQL

V:\>net start MySQL
wchiquito
  • 16,177
  • 2
  • 34
  • 45
  • no dice: SHOW VARIABLES LIKE 'secure_file_priv'; secure_file_priv | C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\ in the my.ini document located at : C:\ProgramData\MySQL\MySQL Server 5.7 changed: secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads" to: secure-file-priv="C:/App/MySQL/MySQL Server 5.7/database" restarted MySQL then: SHOW VARIABLES LIKE 'secure_file_priv'; secure_file_priv | C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\ No Change –  Feb 23 '17 at 15:28
  • @Rthomas529: It seems to be not reading the correct my.ini file. You can post the result of (in my case): `V:\>sc qc MySQL`. See [SC](https://technet.microsoft.com/en-us/library/bb490995.aspx). – wchiquito Feb 23 '17 at 18:06
  • You are correct. sc qc MySQL57 shows "C:\App\MySQL\MySQL Server 5.7\my.ini" MySQL57 while mysql> SHOW VARIABLES LIKE 'secure_file_priv'; shows "C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\" but I can't change the "secure_file_priv" path. So how do I point MySQL57 to the correct path? –  Feb 24 '17 at 17:18
  • @Rthomas529: You have to modify the `secure_file_priv` variable in the `C:\App\MySQL\MySQL Server 5.7\my.ini` file and restart MySQL. – wchiquito Feb 24 '17 at 17:54
  • after changing the file path for C:\App\MySQL\MySQL Server 5.7\my.ini to C:\App\MySQL\MySQL Server 5.7\ MySQL57 was unable to start up. Commenting out the secure-file-priv setting enabled me to start MySQL57 and could see the expected NULL from SHOW VARIABLES LIKE 'secure_file_priv'; but still receiving error "ERROR 1290 (HY000)" –  Feb 24 '17 at 19:46
  • @Rthomas529: `If set to NULL, the server disables import and export operations. This value is permitted as of MySQL 5.7.6.`. See [6.1.5 Server System Variables::secure_file_priv](https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv). – wchiquito Feb 24 '17 at 19:59
  • while it is permitted, it is not the desired effect. I am attempting to configure mysql to import my csv into my DB. –  Feb 24 '17 at 20:08
  • @Rthomas529: Does the directory you are trying to configure exist? Remember: `If secure_file_priv is set to a nonexistent path, the server writes an error message to the error log and exits.`. – wchiquito Feb 24 '17 at 20:16
  • Your original steps were correct. I edited the answer to reflect using "sc qc MySQL57" to determine the true path win services is using for my.ini. A manual correction w/i this my.ini file to the "C:/App/MySQL/MySQL Server 5.7/" path rectified the discrepancy. –  Feb 24 '17 at 20:42