Google cloud sql instance super privilege error

Question

I am very new in Google app engine please help me to solve my problem

I have created one instance in Google cloud sql when I import SQL file then it shows me error like this.

ERROR 1227 (42000) at line 1088: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

How do I to add super privilege to my instance.

Answer 1

As stated at the Cloud SQL documentation:

The SUPER privilege is not supported.

You can take a look at this page that explains how to import data to a Cloud SQL instance.

Answer 2

I also faced the same issue. But the problem was in dumped sql database. When exporting the database use these flags

--hex-blob --skip-triggers --set-gtid-purged=OFF

Here is the complete documentation of how to do it (https://cloud.google.com/sql/docs/mysql/import-export/importing). Once the data is exported, it can be imported using command line, gcloud shell or there is an option of import in gcloud sql as well.

I used the import feature of gcloud sql console and it worked for me.

Answer 3

I ran into the the same error when backporting a gzipped dump (procured with mysqldump from a 5.1 version of MySQL) into a Google Cloud SQL instance of MySQL 5.6. The following statement in the sql file was the problem:

DEFINER=`username`@`%`

The solution that worked for me was removing all instances of it using sed :

cat db-2018-08-30.sql |  sed -e 's/DEFINER=`username`@`%`//g' > db-2018-08-30-CLEANED.sql

After removal the backport completed with no errors. Apparently SUPER privilege is needed, which isn't available in Google Cloud SQL, to run DEFINER.

Another reference: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Good luck!

Answer 4

i faced same issue you can try giving 'super permission' to user but isn't available in GCP cloud SQL.

The statement

DEFINER=username@`%

is an issue in your backup dump.

The solution that you can work around is to remove all the entry from sql dump file and import data from GCP console.

cat DUMP_FILE_NAME.sql | sed -e 's/DEFINER=<username>@%//g' > NEW-CLEANED-DUMP.sql

After removing the entry from dump and completing successfully you can try reimporting.

Answer 5

For the use case of copying between databases within the same instance, it seems the only way to do this is using mysqldump, which you have to pass some special flags so that it works without SUPER privileges. This is how I copied from one database to another:

DB_HOST=...  # set to 127.0.0.1 if using cloud-sql proxy
DB_USER=...
DB_PASSWORD=...
SOURCE_DB=...
DESTINATION_DB=...

mysqldump --hex-blob --skip-triggers --set-gtid-purged=OFF --column-statistics=0 -h $DB_HOST -u $DB_USER -p"$DB_PASSWORD" $SOURCE_DB \
  | mysql -h $DB_HOST -u $DB_USER -p"$DB_PASSWORD" $DESTINATION_DB

Or if you just want to dump to a local file and do something else with it later:

mysqldump --hex-blob --skip-triggers --set-gtid-purged=OFF --column-statistics=0 -h $DB_HOST -u $DB_USER -p"$DB_PASSWORD" $SOURCE_DB \
  > $SOURCE_DB.sql

See https://cloud.google.com/sql/docs/mysql/import-export/exporting#export-mysqldump for more info.

Answer 6

It's about the exporting of data. When you export from the console, it exports the whole Instance, not just the schema, which requires the SUPER privilege for the project in which it was created. To export data to another project, simply export by targeting the schema/s in the advanced option. If you run into could not find storage or object, save the exported schema to your local, then upload to your other project's storage, then select it from there.

Answer 7

In case somebody is searching for this in 2018 (at least august) the solution is:

1. Create a database. You can do this from UI, just go to Database menu and click "Create a database".
2. After you clicked "import" and selected your sql_dump (previously saved in a bucket), press "Show advanced options" and select your Db (not that advanced, are they?!). Otherwise, the default is the system mysql which, of course can not support import.

Happy importing.

Answer 8

I solved this by creating a new database and in the SQL instance. (Default database is sys for mysql).

Steps(Non-cli version):
1) In GCP > SQL > Databases , create a new database e.g newdb
2) In your sql script, add: Use newdb;

Hope that helps someone

Answer 9

SUPER privilege is exclusively reserved for GCP

For you question, you need to import data into a YOUR database in which you have permission ..