Drush sql-sync and civicrm issue

Question

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?

Answer 1

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 2

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?

after you import Civicrm will rebuild triggers as needed.