How to resolve the “access denied; you need (at least one of) the SUPER privilege(s)” error on a Magento 2 database

We frequently find, when migrating stores from dev to live, or from one server to another, that we find ourselves coming across this error when importing a database into MySQL / MariaDB from a SQL dump :

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

It’s down to a series of statements within the SQL dump itself, and will definitely happen if the database user is different between the two platforms, and may happen if the platform to which the database is being imported has slightly tighter rules than the one from which it was exported.

The most common cause of the error is a line in the file which looks like this :

/*!50003 CREATE*/ /*!50017 DEFINER=`old_user`@`%`*/ /*!50003 TRIGGER trg_catalog_product_entity_after_insert AFTER INSERT ON catalog_product_entity FOR EACH ROW

This line (with variants) is present in multiple places through the SQL dump, and a simple find and replace to change the old_user to the new_user should be sufficient, although you may also wish to tighten it by replacing the % sign with localhost instead.

Finally, at the end of the file, there is a similar line :

/*!50013 DEFINER=`old_user`@`%` SQL SECURITY INVOKER */

and again, within this, you will wish to change it to reflect the define as new_user@localhost.

Once that’s done, you should be able to import without any further issues.