0

First I tried to import csv file with mm-dd-yyyy date format, which MySQL didnt like. I was asked to use load data from the command prompt.

So I typed this command below with this error:

load data infile 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\Square Items Sales History.csv' into table squarereport fields terminated by ',' ignore 1 lines (@saleDate,saleTime,category,Item,ItemQty,ItemSubName,GrossSales,Discounts,NetSales,customerID,customerName,HourOfTheDay,EventType,SaleCount) set saleDate = STR_TO_DATE(@saleDate, '%m/%d/%Y');

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

Now I opened my.ini and added this

[mysqld] secure-file-priv = ""

Then restarted the server.

Strangely Im still seeing this:

SHOW VARIABLES LIKE "secure_file_priv"

# Variable_name, Value
'secure_file_priv', 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\'

Defeated, I tried using the local option.

mysql> set global local_infile = true;
Query OK, 0 rows affected (0.00 sec)

mysql> load data local infile  'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\Square Items Sales History.csv' into table squarereport fields terminated by ',' ignore 1 lines (@saleDate,saleTime,category,Item,ItemQty,ItemSubName,GrossSales,Discounts,NetSales,customerID,customerName,HourOfTheDay,EventType,SaleCount) set saleDate = STR_TO_DATE(@saleDate, '%m/%d/%Y');
ERROR 1148 (42000): The used command is not allowed with this MySQL version

All I wanted was to import that CSV file. Yes, I already tried to create a "string" variable for the date and import but the darn Workbench was loading sooooo slow!!!

Balaji V
  • 69
  • 10
  • set the parameter to a real folder and then place your data in that folder like `secure_file_priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"` – RiggsFolly Aug 19 '19 at 12:34
  • Also check you are amending the `my.ini` file that is actually being read by MySQL. – RiggsFolly Aug 19 '19 at 12:40
  • There are 2 issues here: 1) for which you have provided a response 2) the 1148 error. Im not sure if this is duplicate. Could you please share that link?. Also Im placing the file in that folder and still getting the same error. – Balaji V Aug 20 '19 at 14:01

0 Answers0