Definer Clauses in Magento Database Exports

4th October 2017

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

export LANG=C