Error 1290: secure_file_priv

Question

I tried loading data from a file but got ERROR 1290.

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

mysql> load data infile "C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\Book1"
into table sample fields terminated by ',';

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

I've aready tried the below link but didn't work.

MySQL ERROR 1290 (HY000) --secure-file-priv option

How can I load file data.

Does your `USER` have the `FILE` privilege?, `These operations are permitted only to users who have the FILE privilege.`, see, [5.1.7 Server System Variables :: secure_file_priv](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_secure_file_priv). — wchiquito, May 31 '18 at 04:27
Yes I've all privileges but still things not coming on track. — Agniveer, May 31 '18 at 07:00

score 3 · Accepted Answer · answered May 31 '18 at 22:03

Try:

File: Z:\Path\To\MySQL Server 8.0\Uploads\Books

1,"Book0"
2,"Book1"
3,"Book2"

MySQL Command-Line:

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

mysql> SHOW VARIABLES WHERE `Variable_name` = 'secure_file_priv';
+------------------+--------------------------------------+
| Variable_name    | Value                                |
+------------------+--------------------------------------+
| secure_file_priv | Z:\Path\To\MySQL Server 8.0\Uploads\ |
+------------------+--------------------------------------+
1 row in set (0.00 sec)

CREATE TABLE `sample` (
  `id` INT NOT NULL PRIMARY KEY,
  `value` CHAR(5) NOT NULL
);

mysql> DROP TABLE IF EXISTS  `sample`;
Query OK, 0 rows affected (0.34 sec)

mysql> CREATE TABLE IF NOT EXISTS `sample` (
    ->   `id` INT NOT NULL PRIMARY KEY,
    ->   `value` CHAR(5) NOT NULL
    -> );
Query OK, 0 rows affected (0.36 sec)

mysql> LOAD DATA INFILE 'Z:\\Path\\To\\MySQL Server 8.0\\Uploads\\Books'
    -> INTO TABLE `sample`
    ->   FIELDS TERMINATED BY ','
    ->   OPTIONALLY ENCLOSED BY '"'
    ->   LINES TERMINATED BY '\r\n';
Query OK, 3 rows affected (0.08 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT `id`, `value`
    -> FROM `sample`;
+----+-------+
| id | value |
+----+-------+
|  1 | Book0 |
|  2 | Book1 |
|  3 | Book2 |
+----+-------+
3 rows in set (0.15 sec)

Error 1290: secure_file_priv

1 Answers1