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.

Upshot

Did you make any search about error? – Swinney

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.

Fire

@i am also getting same error , I exported dumb from first gen cloud sql and importing from to second gen cloud sql getting error ERROR 1227 (42000) at line 22: Access denied; you need (at least one of) the SUPER privilege(s) for this operation – Engen

Did you get your solution? – Janejanean

I f**ing succeeded. You must be creating table or creating database from client side, and encountered that error. And i think you were accessing mysql database. With my 4-hour research, there's no way you cannot do that and you are doing wrongly in that accessing 'mysql' database is indeed prohibitable. You should have your own database, not 'mysql' database. From console, create your database, and create table and then you are thru. Hope this comment helps someone, and please comment it if you are. You welcome! – Cyclorama

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.

Deli

If you're using a more recent version of mysqldump, it may also be necessary to add: --column-statistics=0 – Globoid

@MattBrowne Do you know from which version of mysqldump? – Zoo

Looks like mysqldump version 8+. BTW I added an answer here showing the specific commands I used: https://mcmap.net/q/728985/-google-cloud-sql-instance-super-privilege-error – Globoid

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!

Hautevienne

Thanks! Solved the problem! – Shipshape

Answer 4

i faced the 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.

Slog

Answer 5

Update: If using mysql-client v5.7, you might need to remove the --column-statistics=0 option to get it to work. And if you're on MySQL 8 or later, see https://cloud.google.com/sql/docs/mysql/import-export/import-export-sql for the latest command.

---

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.

Globoid

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.

Celik

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.

Centi

This was my situation as well. Boiling it down, you need to import into a database in which you have permission. – Infantile

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

Systematology

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 ..

Durkin