Changing the trigger DEFINER in Magento database dump

If you’re about to work with Magento EE 1.13+ & have the database dump on your hands, you might want to check & change the trigger DEFINER statements of it. It will save you from quite a few annoying errors (such as “The configuration could not be saved” in Magento admin).

First you need to find out the trigger DEFINER’s user and host. We need to know these two in order to successfully replace them with new user and host values. I usually do it via cli with this simple grep command:

grep DEFINER db-dump.sql

This command looks for lines containing DEFINER string and prints them out. So, if the DEFINER is present in your Magento database dump, grep command will print out something like this (and a few more lines):

/*!50003 CREATE*/ /*!50017 DEFINER=`olduser`@`oldhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_insert
/*!50003 CREATE*/ /*!50017 DEFINER=`olduser`@`oldhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_update
/*!50003 CREATE*/ /*!50017 DEFINER=`olduser`@`oldhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_delete

Now when the DEFINER’s user and host are found (in this case user is olduser and host is oldhost), you can use sed command to replace them with the new ones (I am using newuser as user and newhost as host). Do it like this:

sed -i 's/DEFINER=`olduser`@`oldhost`/DEFINER=`newuser`@`newhost`/g' db-dump.sql

And that’s it - the trigger DEFINER is updated across the Magento database dump and you can now safely import and use it. If you want to doublecheck if values are indeed changed, just use the same grep command we used to lookup DEFINER’s user and host. Cheers!