Importing and Exporting

Avoiding MySQL definer error

If you need to transport a database between environments you my run into the following error.

ERROR 1227 (42000) at line 2006: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

It is caused by the definer being set to the wrong user.

To fix it, pipe the dump through sed like so (replacing the users accordingly):

mysqldump --single-transaction database | sed -e 's#`root`#`user`#g' > database.sql

If you are still facing problems magento recommend removing the definer all together.