ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

[Stonewater]

I have created an instance/database/tables and now want to load some data (.csv file) into a table.  I get the error above when I try.  If I remove the "LOCAL" option, I get a different message:

ERROR 1045 (28000): Access denied for user 'myconnected userID'@'%' (using password: YES)

I've tried all sort of things and just can't figure this out.  I don't want (really can't do to the the nature of the raw input data) to use the 'import' as part of the Google Cloud SQL gui dashboard.

I've tried putting the csv file in the connected users home directory (/home/username/datadirectory/name.csv) and set the file and directory permissions to allow read/write access.  That didn't help.

I set up a Cloud storage bucket, put the csv file there and changed the SQL LOAD command to reference the 'gs path' ... no luck.

I've tried some updates of mysql.user for to update File-priv = Y but that fails, UPDATE command denied.  I tried the update as root as well as my userID.  

UPDATE mysql.user SET File_priv = 'Y' WHERE Host = 'localhost' AND User = 'userID';

There has to be a way to allow for a csv file to be accessed and LOADED into a table.

Help!

[robertcarlos]

Hi @Stonewater 

Welcome to Google Cloud Community!

ERROR 1045 (28000): Access denied for user 'myconnected userID'@'%' (using password: YES)

---

Regarding this error, this has something to do with either the password of your myconnected userID or privilege issues. You need to change its password based on this Stack Overflow link. 

You can also follow this documentation on setting the password for the default user account 

1. In the Google Cloud console, go to the Cloud SQL Instances page.
2. To open the Overview page of an instance, click the instance name.
3. Select Users from the SQL navigation menu.
4. Find the root user and then select Change password from the more actions menu (three dots). Consider the listed stipulations for the password, which are derived from the password policy set for the instance.
5. Provide a strong password that you can remember and click OK.

Once the password has been changed, LOAD DATA INFILE will now have the permission to carry out the query.

[Stonewater]

I agree that this problem is somehow associated with privileges, either
associated to the access of the bucket/file that I want to LOAD AND/OR
privileges within the database instance.


>From what I’ve tried, I’m thinking it’s more likely related to the
inability for the database user account to access the file in the bucket …
but I can’t figure out what the problem is and if it’s related to the
bucket, the privileges assigned to the user or something else.


First the bucket

Bucket is called ‘stonewaterdata’ and ‘Requestor Pays: off”

I have access to that bucket from root and my own id (pasta)


I can upload/download files to this bucket when I use the SQL console
IMPORT and EXPORT gui while connected to the database as my id (pasta) … so
I know I can read/write to the bucket.


However if I try to execute this LOAD from the terminal when connected to
my database can’t.


I've tried from both my user (pasta) that I believe I have properly set up
and from root.


NOTE: the file designation below (one commented out). I’ve located the
file in both the bucket and tried using the gs://stonewater…. Bucket
designation and the file is also in my /home/pasta directory (permissions
on the directory and file we changed (chmod 777 ….. ) to allow maximum
access given the troubles I’ve been having . Neither work.


I've also been able to "copy" the file from the bucket to my /home
directory ... all done just to verify access to the bucket/file.


LOAD DATA LOCAL INFILE

*/* 'gs://stonewaterdata/Jobs_Report_1_17_2023.csv' */*

*'/home/pasta/jobs_report_1_17_2023.csv'*

INTO TABLE contracts.Jobs_Report

FIELDS TERMINATED BY ','

ENCLOSED BY '"' LINES TERMINATED BY '\n'

IGNORE 1 ROWS

(Current_milestone, Job_Name, Contact_Name, Contact_Email, Phone_Number,

Location_Address, Lead_Source, Contract_Total, Primary_Salesperson,

@Lead_Date, @Prospect_Date, @Approved_Date, @Completed_Date,
@Invoiced_Date, @Closed_Date, Job_Name_Url,

@Street, @City, @State, @Zip, @Country)

SET

/* from left go right stop at the first comma for street */

Street = SUBSTRING_INDEX(location_address,',', 1),

/* from left go right stop at the second comma then go from that comma
back one comma for city */

City = SUBSTRING_INDEX(SUBSTRING_INDEX(location_address, ',', 2),',',-1),

/* from right go left stop at the first comma then go two space right for
state */

State = SUBSTRING_INDEX(SUBSTRING_INDEX(location_address,',', -1),' ', 2),

/* from right go left to the second space then go one space right for zip
*/

ZIP = SUBSTRING_INDEX(SUBSTRING_INDEX(location_address,' ', -2), ' ', 1),

/* from right go left to the first space for country */

Country = SUBSTRING_INDEX(location_address,' ', -1),

Lead_Date = str_to_date(@Lead_Date, "%c/%d/%y") ,

Prospect_Date = str_to_date(@Prospect_Date, "%c/%d/%y") ,

Approved_Date = str_to_date(@Approved_Date, "%c/%d/%y"),

Completed_Date = str_to_date(@Completed_Date, "%c/%d/%y"),

Invoiced_Date = str_to_date(@Invoiced_Date, "%c/%d/%y"),

Closed_Date = str_to_date(@Closed_Date, "%c/%d/%y")

;


I’ve tried both using LOCAL and removing LOCAL from the LOAD DATA statement
as I believe I read you don’t support the LOAD command without LOCAL.


I’ve checked GRANTS and permissions and privileges in the database for both
my id and root and as best as I can tell they are all set appropriately …
but it is possible I’ve missed something.


I’m lost and ready to drop Google Cloud and SQL for this project if I can’t
make progress soon.


What can you recommend?

--
IMPORTANT: The contents of this email and any attachments are confidential.
They are intended for the named recipient(s) only. If you have received
this email by mistake, please notify the sender immediately and do not
disclose the contents to anyone or make copies thereof.