MySQL Forums
Forum List  »  Newbie

secure_file_priv interfering with my load data fantasies
Posted by: The Sanctioner
Date: July 15, 2017 06:50PM

Hello,

I'm trying to insert data into an empty InnoDB table and getting error 1290 (HY000) --secure-file-priv option option set (it is by default).

Here is the statement:

LOAD DATA INFILE 'C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\ABBREV.txt'
INTO TABLE ard_health_abbrev
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';


Here are the steps i verified:

mysql> show variables like "secure_file_priv";
+------------------+------------------------------------------------+
| Variable_name | Value |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\ |
+------------------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

C:\ProgramData\MySQL\MySQL Server 5.7\Uploads>dir
Volume in drive C has no label.
Volume Serial Number is 20E4-7C95

Directory of C:\ProgramData\MySQL\MySQL Server 5.7\Uploads

07/15/2017 07:31 PM <DIR> .
07/15/2017 07:31 PM <DIR> ..
07/15/2017 07:19 PM 2,497,262 ABBREV.txt
1 File(s) 2,497,262 bytes
2 Dir(s) 134,751,268,864 bytes free


Mysql is running as a service under user "Network Service". User "Network Service" has full permissions on both the Uploads folder and the file inside it. I did not have to set it. It was by default.

I have switched the forward slashes to backslashes as shown above, but the forward slashes didn't work either, then restarted mysql

I have commented out secure-file-priv in my.ini, restarted, then verified the mysql variable showed NULL under the SHOW VARIABLES command. Yes this is correct. the variable showed Null when i commented it out and i still got the error that the variable is set.

Windows 7
Mysql 5.7

I cannot get past this error. If you have any solutions, please help!

thanks,
-Sanc

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.