MySQL – Resolving Error 1290 After Fixing my.ini and FILE Permissions

MySQL

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.csv"
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 help.

https://stackoverflow.com/questions/34102562/mysql-error-1290-hy000-secure-file-priv-option

I also have FILE privilege for the current_user.

my.ini file

That's how Book1.csv looks

1,A,A1
2,B,A2
3,C,A3
4,A,A4
5,B,A5
6,C,A6
7,A,A7
8,B,A8
9,C,A9
10,A,A10

I've spent hours. Pls help me out.

Best Answer

In windows, you need to give double slash "\" in the path.

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

You can give reverse slash "/" as below

   load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Book1.csv" into table sample fields terminated by ',';

Give it a try.

Related Question