Error Code: 2068 While Loading Csv File into Mysql
I Am Using Amazon Rds Mysql for Data Storage. I Am Able to Load Data by Specifying the Column Values, However, When I Try to Load the Data from My Local...
I am using Amazon RDS MySql for data storage. I am able to load data by specifying the column values, however, when I try to load the data from my local machine to MySql, it fails with Error: 2068.
LOAD DATA LOCAL INFILE '/Users/priya/Documents/Atlas/data/The_Atlas_0820.csv' INTO TABLE schedule
FIELDS TERMINATED by ','
ENCLOSED by '"'
LINES TERMINATED by '\n' IGNORE 1 LINES (Carrier1, FlightNo1, DupCar1,DupCar2, DupCar3,
DupCar4,DupCar5, DupCar6,DupCar7, DupCar8,DepAirport, ArrAirport);
Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
I also checked SHOW GLOBAL VARIABLES LIKE 'local_infile'; and it is set to "ON".
If i use Terminal to open a connection with MySql and execute the command there, it works ok.
$ ./mysql -h <hostname> -P 3306 --local_infile=1 -u <username> -p
But how to make it work in MySql Workbench.
3 Answers
It seems that there is a bug in MySQL Workbench for LOAD DATA LOCAL INFILE, check if the workaround in this link works for you:
MySQL Workbench 8.0 restricts usage of LOAD DATA LOCAL INFILE
or there is another solution that seems to work here:
The solution that worked for me is from the workarounds shared in the link: Workbench 8.0.12 no longer allows LOAD DATA LOCAL INFILE as shared by @Zacca.
Steps for Mac:
- Create a my.cnf file with the following statements at the path: /etc. I created my.cnf file using root user.
[client]
port = 3306
[mysqld]
port = 3306
secure_file_priv=''
local-infile = 1
Set the my.cnf file as the default configuration file in MySql Workbench. Click the Wrench icon next to Instance. Under configuration file, enter the path to my.cnf file: /etc/my.cnf.
Restart the MySQL server workbench.
Try the following statements in MySQL Workbench: SHOW VARIABLES LIKE "local_infile"; //Should be ON SHOW VARIABLES LIKE "secure_file_priv"; //Should have no values (not NULL but blank)
Load the data.
LOAD DATA LOCAL INFILE '<path>/file.csv' INTO TABLE <tablename>
FIELDS TERMINATED by ','
ENCLOSED by '"'
LINES TERMINATED by '\n' IGNORE 1 LINES;
Using LOCAL keyword, loading is successful. However, without LOCAL keyword, I get access error.
Error Code: 1045. Access denied for user 'admin'@'%' (using password: YES)
After beating my head around this for an hour, I switched back to MySQL Workbench 6.3. Bliss.
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
