In Magento Commerce Edition version 1.13 and above and in Magento 2, there exists definer clauses in Magento database exports when you export the database from the server it resides on.
These definer clauses are used in MySQL triggers and contain the username and host of the server the exist on. They do not pose a problem until you need to re-import the database to another server, where the username and the hostname may be different.
This throws up an error that looks like the following.
The user specified as the definer ('user'@'host') does not exist
The fix for this is simple. You can use the
sed command within your command line interface and remove any instances of the
DEFINER clause by replacing the string with an empty one.
sed -i 's/DEFINER=[^*]*\*/\*/g' data.sql
If you’re using a macOS operating system, the below command should work for you.
sed -i '' 's/DEFINER=[^*]*\*/\*/g' data.sql
When running the
sed command, if you receive the following error:
illegal byte sequence
Then you may have to run the below command before using