8

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?

11

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
  • If your favorite text editor IS NOT vim, then the same can be done with perl from the command line. perl -pie 's/*\!50017.*localhost`*//g' – Jeremy Proffitt Apr 21 '15 at 9:24
  • If CiviCRM is generating VIEW also (l10n?), your dump may also have DEFINER comments with a 50013 prefix. – Chris Burgess May 11 '16 at 5:07
  • perl -pi -e 's#\/\*\!5001.*localhost`[^\*]*\*\/##g' – Chris Burgess Jun 10 '16 at 16:38
3

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.

  • 1
    Some text editors can struggle with giant .sql files. We've had good results with UltraEdit. – Andrew West May 21 '15 at 9:40
  • 1
    Good recommendation! BBEdit has also been able to handle the .sql files I've needed to modify. Coda isn't so happy with big files. – Eliot Mason May 22 '15 at 16:14
2

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
  • 1
    and we have another example to look at in the morning ;-) – petednz - fuzion May 11 '16 at 7:06
  • Enabling CiviCase creates two views. – Kainuk - CiviCooP Aug 24 '18 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.