secure_file_priv interfering with my load data fantasies
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