Access denied for user 'root'@'%' (using password: YES) with google cloud MYSQL Instance

user_5971975 0
Viewed 2177

I have created a google cloud MYSQL Instance and trying to load a CSV file into MYSQL using below query.

LOAD DATA  INFILE 'D:/test.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(title,@expired_date,amount)
SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');

But getting below error.

Error Code: 1045. Access denied for user 'root'@'%' (using password: YES)

google-cloud-sql google-cloud-platform mysql-workbench mysql
1 Answers

I think there might be 1 or 2 problems at play here:

  1. Authentication issues as esqew remarked
  2. Privilege issues related to the query you try to execute

Authentication issues

Before we go the next step, I assume that you have been able to carry out SELECT queries with your root user on this CloudSQL server. If not, then it seems there is an authentication issue, and it would be best to check whether you are in possession of the correct 'root' user password. To change the password[1]:

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.
  2. Click the instance to open its Overview page.
  3. Select Users from the Navigation menu.
  4. Find the root user with a Host value of % (any host) and select Change password from the more actions menu
  5. Provide a strong password that you can remember and click Ok.

Privilege issues

By default the permissions of root user are limited on CloudSQL. This means the that the root user lacks the FILE and SUPER privileges you would have on most other servers[2]. In order to carry out the LOAD DATA INFILE query mentioned above, there are two important points to consider:

  1. mysql makes the assumption that the file is located on the server[3, the point starting with "If LOCAL is not specified]." Since the cloudSQL servers run on Linux, a path starting with D:\ does not make much sense to the server.
  2. LOAD DATA INFILE requires the FILE permission that CloudSQL does not provide to the root user. Hence, you will not have permission to carry out this query[4].

To prevent the permission issue, and carry out loading the data from the local machine as intended, you are allowed to use the LOAD DATA LOCAL INFILE statement on CloudSQL[5], and use the local path you specified above (also see [3]).

[1] https://cloud.google.com/sql/docs/mysql/create-manage-users#user-root

[2] https://cloud.google.com/sql/docs/mysql/users#root-user

[3] https://dev.mysql.com/doc/refman/5.7/en/load-data.html#load-data-input-file

[4] https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_file

[5] https://cloud.google.com/sql/docs/mysql/import-export/importing

user_102434740
Related