Answers.Body: <p>Jon G's answer is correct.  </p>
  
  <p>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=<code>name</code>name@localhost portion, replace it with nothing,  and leave the rest of the line in place.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-21T09:40:51.127Z
Answers.Text: Some text editors can struggle with giant .sql files. We've had good results with UltraEdit.
Answers.UserDisplayName: Andrew West
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-22T16:14:00.180Z
Answers.Text: 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.
Answers.UserDisplayName: Eliot Mason
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-12T16:27:47.567Z
Answers.LastActivityDate: 2015-05-12T16:27:47.567Z
Answers.UserDisplayName: Eliot Mason
Answers.Body: <p>The simplest route I believe is to generate DB dumps which omit these triggers by using mysqldump's <strong>--skip-triggers</strong> switch</p>
  
  <pre><code>mysqldump --skip-triggers NAMEOFDB &gt; NAMEOFDB.sql
  </code></pre>
  
  <p>This outputs a version of the DB dump without triggers, which CiviCRM will regenerate once loaded onto the next site.</p>
  
  <p>If you're using another interface to mysqldump, eg phpmyadmin or sequel pro, these tools may offer a way to achieve the same functionality.</p>
  
  <p>EDIT: Some CiviCRM sites (those using localization?) may additionally have SQL VIEWs which also include <code>DEFINER</code> comments. In MySQL 5.7.8+ you can use <code>--skip-definer</code> which should remove this additional "feature". If you are on less than MySQL 5.7.8,</p>
  
  <pre><code>cat example.sql | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' &gt; new.sql
  </code></pre>
  
  <p>EDIT 2: Now I'm just blowing away all the MySQList SQL comments with</p>
  
  <pre><code>perl -pi -e 's#\/\*\!5001.*localhost`[^\*]*\*\/##g' example.sql
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-05-11T07:06:32.340Z
Answers.Text: and we have another example to look at in the morning ;-)
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-24T16:27:18.953Z
Answers.Text: Enabling CiviCase creates two views.
Answers.UserDisplayName: Kainuk - CiviCooP
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-21T03:19:51.373Z
Answers.LastActivityDate: 2016-06-16T04:34:22.280Z
Answers.LastEditDate: 2016-06-16T04:34:22.280Z
Answers.UserDisplayName: Chris Burgess
Answers.Body: <p>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:</p>
  
  <pre><code>/*!50003 CREATE*/ /*!50017 DEFINER=`newpress`@`localhost`*/ /*!50003 TRIGGER civicrm_address_after_insert after insert ON civicrm_address FOR EACH ROW BEGIN
  </code></pre>
  
  <p>The issue is the <code>DEFINER</code> 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.</p>
  
  <p>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, <a href="https://dev.mysql.com/doc/refman/5.0/en/create-trigger.html" rel="noreferrer">MySQL will default</a> to <code>DEFINER = CURRENT_USER</code>, which in this case is correct.</p>
  
  <p>You can fix this with a search-and-replace in your favorite text editor.  If that text editor is <code>vim</code>, you can press <code>:</code> and paste this command in to remove all the definer clauses:</p>
  
  <pre><code>%s:/\*\!50017.*localhost`\*/::
  </code></pre>
  
  <p><strong>EDIT:</strong> Chris' regex below is good.  I improved it slightly here:</p>
  
  <pre><code>perl -pi -e 's#\/\*\!5001[7|3].*?`[^\*]*\*\/##g' /path/to/mysqldump.sql
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-04-21T09:24:49.500Z
Answers.Text: 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'
Answers.UserDisplayName: Jeremy Proffitt
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-05-11T05:07:22.643Z
Answers.Text: If CiviCRM is generating VIEW also (l10n?), your dump may also have `DEFINER` comments with a `50013` prefix.
Answers.UserDisplayName: Chris Burgess
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-10T16:38:50.020Z
Answers.Text: `perl -pi -e 's#\/\*\!5001.*localhost\`[^\*]*\*\/##g'`
Answers.UserDisplayName: Chris Burgess
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-04-16T18:38:19.547Z
Answers.LastActivityDate: 2016-08-04T06:50:40.277Z
Answers.LastEditDate: 2016-08-04T06:50:40.277Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>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:</p>
  
  <pre><code>[ERROR in query 7] Access denied; you need (at least one of) the SUPER privilege(s) for this operation
  </code></pre>
  
  <p>How do I fix this?</p>
  
Tags: <sql><backups>
Title: 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?
ThreadText: # 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?
  
  (Asked by: username_0 on 2015-04-16)
  
  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?
  
  **Tags:** sql, backups
  
  
  ---
  
  ## 3 answers
  
  ### Answer by username_0 on 2015-04-16. Score: 14 | Accepted answer
  
  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
  
  > **Comments:**
  > **username_6**: 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'  
  > **username_3**: If CiviCRM is generating VIEW also (l10n?), your dump may also have `DEFINER` comments with a `50013` prefix.  
  > **username_3**: `perl -pi -e 's#\/\*\!5001.*localhost\`[^\*]*\*\/##g'`  
  
  ---
  
  ### Answer by username_1 on 2015-05-12. Score: 5
  
  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=`name`name@localhost portion, replace it with nothing, and leave the rest of the line in place.
  
  > **Comments:**
  > **username_2**: Some text editors can struggle with giant .sql files. We've had good results with UltraEdit.  
  > **username_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.  
  
  ---
  
  ### Answer by username_3 on 2015-05-21. Score: 5
  
  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
  
  > **Comments:**
  > **username_4**: and we have another example to look at in the morning ;-)  
  > **username_5**: Enabling CiviCase creates two views.  
  
  ---
  
  