Answers.Body: <p>In stead of using drush for this, I use the "mysqldump --skip-triggers"command exactly for that reason:</p>
  
  <pre><code>mysqldump --skip-triggers -u db_user -pdb_password db &gt; export.sql
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-04-13T07:37:56.263Z
Answers.LastActivityDate: 2019-04-13T07:37:56.263Z
Answers.UserDisplayName: Webmaster COVS
Answers.Body: <p>Civicrm utilises sql triggers for a number of purposes - including but not limited to populating values into the log_civicrm_% tables - if you have logging turned on under Admin -> System Settings -> Misc</p>
  
  <p>You can't alter triggers. Easiest to dump the database then strip out triggers using the following perl </p>
  
  <pre><code>perl -pi -e 's#\/\*\!5001[7|3].*?`[^\*]*\*\/##g' /path/to/mysqldump.sql
  </code></pre>
  
  <p>by way of Jon G and Chris Burgess on <a href="https://civicrm.stackexchange.com/questions/619/why-do-i-get-access-denied-you-need-at-least-one-of-the-super-privileges-f">Why do I get &quot;Access denied; you need (at least one of) the SUPER privilege(s) for this operation&quot; when importing a SQL dump to CiviCRM?</a></p>
  
  <p>after you import Civicrm will rebuild triggers as needed.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-12-18T00:26:50.443Z
Answers.Text: Can I import just the triggers updated for the correct user, or do I have to completely rebuild my civi DB?
Answers.UserDisplayName: tanbog
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-12-18T18:51:43.747Z
Answers.Text: If you want you could attempt to get a list of all triggers then drop them one by one.  Something similar to the following might work. - I haven't tested this sorry. mysql --skip-column-names -B  -e "select trigger_schema, trigger_name from information_schema.triggers where trigger_schema ='database_name' limit 10" | awk '{printf("drop trigger if exists %s.%s;\n", $1, $2)}'
Answers.UserDisplayName: Luke Stewart
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-12-17T08:33:05.120Z
Answers.LastActivityDate: 2018-12-17T08:33:05.120Z
Answers.UserDisplayName: Luke Stewart
Body: <p>I wanted to quickly pull all the data (contacts, custom types, etc) from one Drupal CiviCRM install to another. </p>
  
  <p>I got a little too clever for myself and used <code>Drush sql-sync</code> to do it. It failed. As I was in a hurry and it seemed to be a permissions issue I re-ran the import as the DB root. This worked, and the site appears to work fine. However, <code>Drush sql-sync</code> now always fails when I try to pull from live to staging or to dev servers. </p>
  
  <p>After much digging I realised the original sql query included a number of triggers related to the user of the copied website, eg:</p>
  
  <pre><code> 1156 DELIMITER ;;
   1157 /*!50003 CREATE*/ /*!50017 DEFINER=`source_user`@`localhost`*/ /*!50003 TRIGGER civicrm_activity_before_insert before insert ON civicrm_activity FOR EACH ROW BEGIN  
   1158 SET NEW.created_date = CURRENT_TIMESTAMP;
   1159  END */;;
  </code></pre>
  
  <p>I assume I want to change these entries, is there a "safe" way to go about that?</p>
  
  <p>Is the presence of these bad for the site?</p>
  
  <p>The <code>source_user</code> has no grants on the new site.</p>
  
  <p>There are at least 60 or so references to the database user in civiCRM related triggers and definitions. </p>
  
  <p>As an aside, Why does civi need to do this when there is a settings file?</p>
  
Tags: <drupal><mysql><drush>
Title: Drush sql-sync and civicrm issue
ThreadText: # Drush sql-sync and civicrm issue
  
  (Asked by: username_0 on 2018-12-12)
  
  I wanted to quickly pull all the data (contacts, custom types, etc) from one Drupal CiviCRM install to another.
  
  I got a little too clever for myself and used `Drush sql-sync` to do it. It failed. As I was in a hurry and it seemed to be a permissions issue I re-ran the import as the DB root. This worked, and the site appears to work fine. However, `Drush sql-sync` now always fails when I try to pull from live to staging or to dev servers.
  
  After much digging I realised the original sql query included a number of triggers related to the user of the copied website, eg:
  
  ```
   1156 DELIMITER ;;
   1157 /*!50003 CREATE*/ /*!50017 DEFINER=`source_user`@`localhost`*/ /*!50003 TRIGGER civicrm_activity_before_insert before insert ON civicrm_activity FOR EACH ROW BEGIN  
   1158 SET NEW.created_date = CURRENT_TIMESTAMP;
   1159  END */;;
  
  ```
  
  I assume I want to change these entries, is there a "safe" way to go about that?
  
  Is the presence of these bad for the site?
  
  The `source_user` has no grants on the new site.
  
  There are at least 60 or so references to the database user in civiCRM related triggers and definitions.
  
  As an aside, Why does civi need to do this when there is a settings file?
  
  **Tags:** drupal, mysql, drush
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2019-04-13. Score: 3 | Accepted answer
  
  In stead of using drush for this, I use the "mysqldump --skip-triggers"command exactly for that reason:
  
  ```
  mysqldump --skip-triggers -u db_user -pdb_password db > export.sql
  
  ```
  
  
  ---
  
  ### Answer by username_2 on 2018-12-17. Score: 3
  
  Civicrm utilises sql triggers for a number of purposes - including but not limited to populating values into the log\_civicrm\_% tables - if you have logging turned on under Admin -> System Settings -> Misc
  
  You can't alter triggers. Easiest to dump the database then strip out triggers using the following perl
  
  ```
  perl -pi -e 's#\/\*\!5001[7|3].*?`[^\*]*\*\/##g' /path/to/mysqldump.sql
  
  ```
  
  by way of Jon G and Chris Burgess on [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?](https://civicrm.stackexchange.com/questions/619/why-do-i-get-access-denied-you-need-at-least-one-of-the-super-privileges-f)
  
  after you import Civicrm will rebuild triggers as needed.
  
  > **Comments:**
  > **username_0**: Can I import just the triggers updated for the correct user, or do I have to completely rebuild my civi DB?  
  > **username_2**: If you want you could attempt to get a list of all triggers then drop them one by one.  Something similar to the following might work. - I haven't tested this sorry. mysql --skip-column-names -B  -e "select trigger_schema, trigger_name from information_schema.triggers where trigger_schema ='database_name' limit 10" | awk '{printf("drop trigger if exists %s.%s;\n", $1, $2)}'  
  
  ---
  
  