CODEWITHSUNDEEP
mysqlimportgoogle-cloud-sql
user1994663
user1994663

Reputation: 11

google cloud sql -- mysql import issue

I am trying to import a table in a newly created instance in google MySQL cloud but I get the following error message:

Error 1142: INSERT command denied to user 'cloudsqlimport'@'127.0.0.1' for table

When I try to grant the privileges to cloudsqlimport from cloud sql console:

mysql> UPDATE `mysql`.`user` SET `insert_priv` = 'Y' WHERE `User` = 'cloudsqlimport';
ERROR 1142 (42000): UPDATE command denied to user 'root'@'104.xxx.xx.xx' for table 'user'

mysql> UPDATE `mysql`.`user` SET `Grant_priv` = 'Y' WHERE `User` = 'root';
ERROR 1142 (42000): UPDATE command denied to user 'root'@'104.xxx.xx.xx' for table 'user'

Same issue when I try to grant the 'insert_priv' through MySQL workbench

How can I resolve this issue?

Upvotes: 1

Views: 647

Answers (2)

Enigo
Enigo

Reputation: 3895

I had a similar issue when importing a dump of mysql.user table. Even thought it is not exactly what your issue is, still it might be relevant, so let me share what a GCP engineer suggestion was. There are two options:

  1. Use now deprecated mysqlpump instead of mysqdump when getting a dump from a table with --users flag
  2. Use MariaDB version of mysqldump with the --system flag to create the dump including CREATE USER commands

A mysql.user table dump taken with one of those options can be successfully imported using gcloud sql import sql command.

Upvotes: 0

Nibrass H
Nibrass H

Reputation: 2497

This error might be due to one of the following causes:

1- Lack of permissions

2- Database or Table spelled wrongly.

3- User correctly spelled but the IP is not correct.

To see the permissions your user has, Please log into MySQL prompt with your user, and run the following command:

SHOW GRANTS;

This will show you the privileges (grants in MySQL) that the current user has. Please check if you have INSERT privileges for the given table.

Upvotes: 1

Related Questions