/
Remove 'DEFINER' entries from database dump file
Got feedback or spotted a mistake?

Leave a comment at the end of this page or email contact@krishagni.com

Remove 'DEFINER' entries from database dump file

Reason

If we are importing the database dump which has DEFINER entries it causes errors while importing the database because DEFINER contains the database user and hostname of the database server. It has to match with the database server and database user who is importing it. If it does not match then it shows errors like "Error 1227 (42000): Access denied; you need (at least one of ) the SUPER privilege(s) for this operation"

Steps to remove DEFINER entries:

Unzip database dump file.

Change directory to the database dump file. Unzip the database dump.

unzip OPENSPECIMEN_26-03-2019.zip  or gunzip OPENSPECIMEN_26-03-2019.SQL.gz

Remove 'DEFINER' entries from the database dump.

sed -e 's/DEFINER=[^ ]* / /' OPENSPECIMEN_26-03-2019.SQL > OPENSPECIMEN_26-03-2019-NEW.SQL

To check all definer entries are removed open the newly generated dump file and search for DEFINER keyword into it.

If no entry is found for DEFINER, import newly generated database dump into the database.




Related content

How to restore OpenSpecimen from older backup?
How to restore OpenSpecimen from older backup?
More like this
Export database from one tablespace and import into another
Export database from one tablespace and import into another
More like this
Database Backup & Restore
Database Backup & Restore
Read with this
How to resolve permissible-values.xml changeset failed.
How to resolve permissible-values.xml changeset failed.
More like this
Database backup script
Database backup script
Read with this
Database Configuration
Database Configuration
More like this
Got feedback or spotted a mistake?

Leave a comment at the end of this page or email contact@krishagni.com