Why do I get "Access denied; you need (at least one of) the SUPER privilege(s) for this operation" when importing a SQL dump to CiviCRM?

Question

When attempting to import a database dump from CiviCRM on a computer other than the one I dumped it from, I often get an error that looks like:

[ERROR in query 7] Access denied; you need (at least one of) the SUPER privilege(s) for this operation

How do I fix this?

Answer 1

CiviCRM defines MySQL triggers for certain tasks. When you use phpMyAdmin, mysqldump or similar to export your database to a text file, it exports your triggers as well. The format is something like this:

/*!50003 CREATE*/ /*!50017 DEFINER=`newpress`@`localhost`*/ /*!50003 TRIGGER civicrm_address_after_insert after insert ON civicrm_address FOR EACH ROW BEGIN

The issue is the DEFINER clause. This specifies the user whose permissions are used when the trigger runs. When you import a database, it defines your triggers, but you can NOT define a trigger to run as someone other than yourself. So if the MySQL username has changed, you must fix your triggers.

You can either a) change the name of the MySQL user listed as the DEFINER, or b) remove the DEFINER portion of the clause altogether. If you don't specify a user, MySQL will default to DEFINER = CURRENT_USER, which in this case is correct.

You can fix this with a search-and-replace in your favorite text editor. If that text editor is vim, you can press : and paste this command in to remove all the definer clauses:

%s:/\*\!50017.*localhost`\*/::

EDIT: Chris' regex below is good. I improved it slightly here:

perl -pi -e 's#\/\*\!5001[7|3].*?`[^\*]*\*\/##g' /path/to/mysqldump.sql

Answer 2

Jon G's answer is correct.

However, If you're not in the world of shell editors and perl, you can also open the dump file in a text editor (not a word processor) and do a search and replace. You can just search for DEFINER=namename@localhost portion, replace it with nothing, and leave the rest of the line in place.

Answer 3

The simplest route I believe is to generate DB dumps which omit these triggers by using mysqldump's --skip-triggers switch

mysqldump --skip-triggers NAMEOFDB > NAMEOFDB.sql

This outputs a version of the DB dump without triggers, which CiviCRM will regenerate once loaded onto the next site.

If you're using another interface to mysqldump, eg phpmyadmin or sequel pro, these tools may offer a way to achieve the same functionality.

EDIT: Some CiviCRM sites (those using localization?) may additionally have SQL VIEWs which also include DEFINER comments. In MySQL 5.7.8+ you can use --skip-definer which should remove this additional "feature". If you are on less than MySQL 5.7.8,

cat example.sql | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > new.sql

EDIT 2: Now I'm just blowing away all the MySQList SQL comments with

perl -pi -e 's#\/\*\!5001.*localhost`[^\*]*\*\/##g' example.sql