CloudSQL for MySQL - privilge issue when importing

[virinchi-t]

Hi there!

When I am trying to import a .sql file which is stored in GCS , I am getting attached message from console related to super privileges, even import is failing like below 

gcloud sql import sql XXXXXXXa gs://beta-db-dumps/sssc_0224.sql \                                                  1 ↵  10008  14:42:11
  --database=mysql \
  --project=XXXXXXXa
Data from [gs://beta-db-dumps/sssc_0224.sql] will be imported to [XXXXXXXaXXXXXXXa-instance].

Do you want to continue (Y/n)?  Y

Importing data into Cloud SQL instance...failed.
ERROR: (gcloud.sql.import.sql) [ERROR_RDBMS] error:
exit status 1
stderr:
ERROR 1227 (42000) at line 14: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

[ms4446]

Your SQL Script is failing because your .sql file (specifically at line 14) contains commands requiring SUPER privileges, which are restricted in Cloud SQL for security and stability.

To fix this:

1. Edit your .sql file (sssc_0224.sql):
   • Examine line 14. It likely contains a statement like DEFINER=..., SET @@GLOBAL..., or other commands needing high privileges.
   • Remove or modify these problematic statements. For DEFINER clauses, you can often remove them entirely or change them to DEFINER=CURRENT_USER.
2. Re-upload the modified file to GCS.
3. Retry the import.

[virinchi-t]

Hi 
Thank you for the response, It is huge file nearly 100GB , what if I need those definers as well in the import?

OR to exempt
is there a way to export database without them in first place

[ms4446]

Understood. Trying to edit a large SQL Dump script can be impractical. The best approach is to address this during the export process.

Use mysqldump with the --skip-definer flag. This prevents DEFINER clauses from being included in the dump file for routines, views, triggers, and events.

Optimized mysqldump Command:

mysqldump \
  --host=your-source-host \
  --user=your-user \
  --password=your-password \
  --databases your_db \
  --skip-definer \
  --set-gtid-purged=OFF \
  --routines \
  --triggers \
  --events \
  --single-transaction \
  --no-tablespaces \
  --column-statistics=0 \
  > your_dump.sql

If your mysqldump version lacks --skip-definer, you can try to pipe the output through sed. 

# Example sed command (test and adjust as needed):
mysqldump [other_options_here] ... | sed -E 's/\sDEFINER=`[^`]+`@`[^`]+`//g' | sed -E 's/\sSQL SECURITY DEFINER//g' > cleaned_dump.sql