An ERROR: 1227 (42000) usually occurs when the DEFINER in your dump file doesn’t have the doadmin permission. DEFINER errors are triggered when MySQL attempts to create an object under a database user that doesn’t exist on the target database.
To resolve this error, you need to either remove the DEFINER from the dump file or replace it.
To remove the DEFINER from the dump file, run the following sed command from the Droplet terminal where the dump file is located. sed is a Unix utility that searches for and transforms text based on a pattern that you provide it.
sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i yourdumpfile.sql
This sed command filters through the dump file looking for text that matches the regular expression provided in the command. When sed encounters a match, it substitutes the matched line with nothing, effectively deleting the line in the file. The -i flag allows sed edit the file in place instead of creating and saving a new file with the changes.
To replace the DEFINER, run the command below to generate a new dump file using the --set-gtid-purged=OFF flag. This generates a dump file that contains a DEFINER with the correct permissions.
Before running this command, replace the <database_username>, <user_password>, <database_cluster_url>, and <database_name> placeholder values with your user and database values:
mysqldump --user=<database_username> --password=<user_password> --host=<database_cluster_url> -P 25060 --set-gtid-purged=OFF --databases <database_name> > dbexport.sql
This generates a file called dbexport.sql, which you can then use to restore the database by running the following command using the doadmin user:
mysql --user=doadmin --password=<user_password> --host=<database_cluster_url> -P 25060 < dbexport.sql