HummingbirdUK main logo

Coding solutions to business problems

About us

We use code to create solutions to business challenges, bottle-necks and headaches.

If you think your business has a problem that can be solved through code, we are happy to chat things through without any obligation.

Get in touch

You need SUPER privilege(s) in Magento 2

Home / Blog / You need SUPER privilege(s) in Magento 2

Written by Giles Bennett

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.

Author : Giles Bennett

About the author

Giles Bennett built his first website in 1996, and is old enough to miss Netscape Navigator. Initially a lawyer, he jumped ship to IT in 2008, and after 5 years as a freelancer, he founded HummingbirdUK in 2013. He can be reached by email at giles@hummingbirduk.com.