Magento 1.x – Fix Access denied error when importing an enterprise DB
If you are working with an enterprise DB, you can see the following error:
ERROR 1227 (42000) at line XXXX: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
To fix it, we will need to perform the following steps:
1grep -i 'definer=' /path/to/dump.sql | head -1
- You will get an output something like this:
1/*!50003 CREATE*/ /*!50017 DEFINER=`magento_user`@`localhost`*/ /*!50003 TRIGGER trg_catalog_category_entity_after_insert
- Get from the previous output, the user and the host.
- With this information, we are going to proceed to search, replace, and create a new dump (it will take a few seconds):
1sed -e 's/DEFINER=`magento_user`@`localhost`/DEFINER=`$NEW_USER`@`$NEW_HOST`/g' /path/to/dump.sql > /path/to/dump_new.sql
- After this, just import the newly generated file and you shouldn’t have any more issues.