Super user error when importing mysqldump?

Question

I've done a mysqldump from our Drupal6/CiviCRM4 Civi DB and am trying to import it into a Drupal7/Civi4 dev install, but keep getting "ERROR 1227 (42000) at line 369: Access denied; you need the SUPER privilege for this operation"

From what I can tell, user has correct permissions as indicated via site's cpanel, all appears correct in the import command. I've not come upon this error before doing similar proceedures on other installs. Help please?

Answer 1

It could be one of a couple of things.

First, if you have MySQL version before 5.1.6, you'll need the SUPER permission no matter what: http://wiki.civicrm.org/confluence/display/CRMDOC/CiviCRM+MySQL+Permission+Requirements

However, since it has been running fine and you're just trying to import the dump file, I think it's probably that your dump file has functions where the definer is set as the database user for the old site (let's say "foo@localhost"). You aren't allowed to set the definer as someone other than yourself without the SUPER privilege. If the new site has a different user (maybe "bar@localhost"), you're acting as "bar@localost" trying to set a function defined by "foo@localhost".

In your dump, you'll probably see something along the lines of

CREATE DEFINER=`foo`@`localhost` FUNCTION ...

You can do one of two things:

• have both databases use the same database user account, or
• edit the dump file to replace the old user name with the new user name everywhere it occurs.

You should also be aware that "foo@localhost" and "foo@%" are separate users, even if they have the same name and password.

Answer 2

A Drupal/CiviCrm database uses triggers. The SUPER privilege is needed to recreate the trigger when you import the database. If you've restricted your standard user's privileges to those needed while running Civi, you can create a new MySQL user just for the import and grant that user ALL privileges on the database(s). After the import is complete, remove the user with elevated privileges.