Answers.Body: <p>Try <code>SHOW TRIGGERS;</code> Also when you say you searched the database, are you searching a mysqldump? Triggers would be in a dump by default so you should see them there. How are you are mirroring the database?</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-04T06:25:08.767Z
Answers.Text: I did the search of all tables in phpMyAdmin on the windows side. The mirror was from a nightly mysqldump backup that we make. I do confess that I don't have mysql root privileges on our hosting account, which means I don't get everything. I'll try using a dump from our hosting service, which is done differently and should contain everything. Wondering if I should change hosting as it bothers me I don't have full MySQL privileges on the databases that I create.
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-04T16:13:13.043Z
Answers.Text: I just ran mysql > show triggers; as root on my windows version of mysql where I have full control. Yes, ALL the triggers show sdcbc@173.236.128.0/255.255.128.0'. I confess to not having much MySQL experience, just reading the man page and getting by with being able to export, drop all tables, and import. Is there a better way to run the export and/or do some operation on the import so that those triggers are valid after import? Thanks for your help.
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-04T16:23:13.643Z
Answers.Text: I searched for "trigger" in the mysqldump file from nightly backups and only found "{EXPERIMENTAL} If the MySQL user does not have permission to adminster triggers, then you must create the triggers outside CiviCRM. No support is provided for this configuration." I confess that I also don't quite know what that means.
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-04T16:31:02.720Z
Answers.Text: I found blocks like this in the mysql dump file. "DELIMITER ;;
  /*!50003 CREATE*/ /*!50017 DEFINER=`sdcbc`@`173.236.128.0/255.255.128.0`*/ /*!50003 TRIGGER civicrm_contact_before_insert before insert ON civicrm_contact FOR EACH ROW BEGIN  
  SET NEW.created_date = CURRENT_TIMESTAMP;
   END */;;" Do I just need to go in and edit those somehow before I import? Can I just change the definer to `sdcbc`@`127.0.0.1`*/  ? Do I need the port number = 8080 in this case to avoid port 80 conflicts with my windows system?
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-04T21:27:15.147Z
Answers.Text: OK. I edited all the DEFINER blocks in the mysqldump sql file to DEFINER=`sdcbc`@`localhost` and it now works on my local instance, too!!! I've read much of the CiviCRM documentation and some posts in this civicrm.stack.exchange regarding moving the database to different servers, and I don't think I found a reference to editing the triggers for correct operation. Did I miss that or is this something we should add to the documentation? And MANY THANKS - I definitely learned a thing or two along the way!
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-04T23:49:21.153Z
Answers.Text: Good job sticking with it. The experimental part seems like a preference that was added (see https://raw.githubusercontent.com/civicrm/civicrm-core/648631cd94799e87fe2347487d465b1a7256aa57/settings/Core.setting.php) but would require you to manually create the triggers, which isn't what you want. There is a reference to triggers at https://wiki.civicrm.org/confluence/display/CRMDOC/Moving+an+Existing+Installation+to+a+New+Server+or+Location - you visit  http://wordpress_site/wp-admin/admin.php?page=CiviCRM&q=civicrm/menu/rebuild&reset=1&triggerRebuild=1, but editing the dump works too.
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-05T06:57:28.540Z
Answers.Text: Thanks. I missed the line in the docs about rebuilding the triggers. I'll do that in the future rather than risk fat-fingering an edit to the sql directly. Always helps to RTFM.. "read the f...ing manual" ;)
Answers.UserDisplayName: Karl Rudnick
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-04T01:35:49.407Z
Answers.LastActivityDate: 2017-02-04T01:35:49.407Z
Answers.UserDisplayName: Demerit
Body: <p>I have a local windows "mirror" using XAMPP of my live Wordpress 4.7.2 + CiviCRM 4.7.14 setup. I wanted to use the windows version to test exactly how deduping worked using Contacts > "Find and Merge Duplicate Contacts". I tried a supervised merge of just one contact (name and address dedupe rule) to see exactly how it would work. I got a DB error when I pressed "Merge and View Results." Complete error shown in ConfigAndLog is:</p>
  
  <pre><code>Feb 03 12:56:20  [info] $Fatal Error Details = Array
  </code></pre>
  
  <p>(
      [callback] => Array
          (
              [0] => CRM_Core_Error
              [1] => handle
          )</p>
  
  <pre><code>[code] =&gt; -1
  [message] =&gt; DB Error: unknown error
  [mode] =&gt; 16
  [debug_info] =&gt; UPDATE IGNORE civicrm_value_community_planning_area_5 SET entity_id = 1702 WHERE entity_id = 3238 [nativecode=1449 ** The user specified as a definer ('sdcbc'@'173.236.128.0/255.255.128.0') does not exist]
  [type] =&gt; DB_Error
  [user_info] =&gt; UPDATE IGNORE civicrm_value_community_planning_area_5 SET entity_id = 1702 WHERE entity_id = 3238 [nativecode=1449 ** The user specified as a definer ('sdcbc'@'173.236.128.0/255.255.128.0') does not exist]
  [to_string] =&gt; [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="UPDATE IGNORE civicrm_value_community_planning_area_5 SET entity_id = 1702 WHERE entity_id = 3238 [nativecode=1449 ** The user specified as a definer ('sdcbc'@'173.236.128.0/255.255.128.0') does not exist]"]
  </code></pre>
  
  <p>)</p>
  
  <p>The error seems to be that the MySQL user 'sdcbc' does not exist. The odd thing is the IP address associated to the MySQL user 'sdcbc'. In my local setup, this should be 'sdcbc'@'localhost' or at least it should show 127.0.0.1 instead of 173.236.128.0 with a mask. Not coincidentally, I suppose, the IP address for our live MySQL server is 173.236.135.189. How is that hard-coded address getting into my local version? All the mysql pointers in my local civicrm.settings.php point to sdcbc@localhost. Whenever I update my mirror (live .> local), I'm also careful to clear cache (delete templates_c/ folder ConfigAndLog/Config.IDS.ini) and  and update paths, etc via i.    <a href="http://sdbikecoalition.localhost:8080/wp-admin/admin.php?page=CiviCRM&amp;q=civicrm/admin/setting/updateConfigBackend&amp;reset=1" rel="nofollow noreferrer">http://sdbikecoalition.localhost:8080/wp-admin/admin.php?page=CiviCRM&amp;q=civicrm/admin/setting/updateConfigBackend&amp;reset=1</a> and iii.  <a href="http://sdbikecoalition.localhost:8080/wp-admin/admin.php?page=CiviCRM&amp;q=civicrm/menu/rebuild&amp;reset=1" rel="nofollow noreferrer">http://sdbikecoalition.localhost:8080/wp-admin/admin.php?page=CiviCRM&amp;q=civicrm/menu/rebuild&amp;reset=1</a>. Note also, that the table that is generating an error is a custom field, 'civicrm_value_community_planning_area_5', if that is also a clue.</p>
  
  <p>As a postscript, I ran the merge on this one contact on our LIVE instance and it worked just fine. Somehow, some setting or file is retaining the 173.236 from our live MySQL database. I completely searched our MySQL database for the string '173.236' and could not find it anywhere. This seems to be an issue with trying to mirror one site to another.</p>
  
Tags: <wordpress><custom-fields><database>
Title: DB Error: IP address hard-wired with MySQL user?
ThreadText: # DB Error: IP address hard-wired with MySQL user?
  
  (Asked by: username_0 on 2017-02-03)
  
  I have a local windows "mirror" using XAMPP of my live Wordpress 4.7.2 + CiviCRM 4.7.14 setup. I wanted to use the windows version to test exactly how deduping worked using Contacts > "Find and Merge Duplicate Contacts". I tried a supervised merge of just one contact (name and address dedupe rule) to see exactly how it would work. I got a DB error when I pressed "Merge and View Results." Complete error shown in ConfigAndLog is:
  Feb 03 12:56:20  [info] $Fatal Error Details = Array
  
  (
      [callback] => Array
          (
              [0] => CRM_Core_Error
              [1] => handle
          )
  [code] => -1
  [message] => DB Error: unknown error
  [mode] => 16
  [debug_info] => UPDATE IGNORE civicrm_value_community_planning_area_5 SET entity_id = 1702 WHERE entity_id = 3238 [nativecode=1449 ** The user specified as a definer ('sdcbc'@'173.236.128.0/255.255.128.0') does not exist]
  [type] => DB_Error
  [user_info] => UPDATE IGNORE civicrm_value_community_planning_area_5 SET entity_id = 1702 WHERE entity_id = 3238 [nativecode=1449 ** The user specified as a definer ('sdcbc'@'173.236.128.0/255.255.128.0') does not exist]
  [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="UPDATE IGNORE civicrm_value_community_planning_area_5 SET entity_id = 1702 WHERE entity_id = 3238 [nativecode=1449 ** The user specified as a definer ('sdcbc'@'173.236.128.0/255.255.128.0') does not exist]"]
  
  )
  The error seems to be that the MySQL user 'sdcbc' does not exist. The odd thing is the IP address associated to the MySQL user 'sdcbc'. In my local setup, this should be 'sdcbc'@'localhost' or at least it should show 127.0.0.1 instead of 173.236.128.0 with a mask. Not coincidentally, I suppose, the IP address for our live MySQL server is 173.236.135.189. How is that hard-coded address getting into my local version? All the mysql pointers in my local civicrm.settings.php point to sdcbc@localhost. Whenever I update my mirror (live .> local), I'm also careful to clear cache (delete templates_c/ folder ConfigAndLog/Config.IDS.ini) and  and update paths, etc via i.    http://sdbikecoalition.localhost:8080/wp-admin/admin.php?page=CiviCRM&q=civicrm/admin/setting/updateConfigBackend&reset=1 and iii.  http://sdbikecoalition.localhost:8080/wp-admin/admin.php?page=CiviCRM&q=civicrm/menu/rebuild&reset=1. Note also, that the table that is generating an error is a custom field, 'civicrm_value_community_planning_area_5', if that is also a clue.
  As a postscript, I ran the merge on this one contact on our LIVE instance and it worked just fine. Somehow, some setting or file is retaining the 173.236 from our live MySQL database. I completely searched our MySQL database for the string '173.236' and could not find it anywhere. This seems to be an issue with trying to mirror one site to another.
  
  **Tags:** wordpress, custom-fields, database
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-02-04. Score: 3 | Accepted answer
  
  Try `SHOW TRIGGERS;` Also when you say you searched the database, are you searching a mysqldump? Triggers would be in a dump by default so you should see them there. How are you are mirroring the database?
  
  > **Comments:**
  > **username_0**: I did the search of all tables in phpMyAdmin on the windows side. The mirror was from a nightly mysqldump backup that we make. I do confess that I don't have mysql root privileges on our hosting account, which means I don't get everything. I'll try using a dump from our hosting service, which is done differently and should contain everything. Wondering if I should change hosting as it bothers me I don't have full MySQL privileges on the databases that I create.  
  > **username_0**: I just ran mysql > show triggers; as root on my windows version of mysql where I have full control. Yes, ALL the triggers show sdcbc@173.236.128.0/255.255.128.0'. I confess to not having much MySQL experience, just reading the man page and getting by with being able to export, drop all tables, and import. Is there a better way to run the export and/or do some operation on the import so that those triggers are valid after import? Thanks for your help.  
  > **username_0**: I searched for "trigger" in the mysqldump file from nightly backups and only found "{EXPERIMENTAL} If the MySQL user does not have permission to adminster triggers, then you must create the triggers outside CiviCRM. No support is provided for this configuration." I confess that I also don't quite know what that means.  
  > **username_0**: I found blocks like this in the mysql dump file. "DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`sdcbc`@`173.236.128.0/255.255.128.0`*/ /*!50003 TRIGGER civicrm_contact_before_insert before insert ON civicrm_contact FOR EACH ROW BEGIN   SET NEW.created_date = CURRENT_TIMESTAMP;  END */;;" Do I just need to go in and edit those somehow before I import? Can I just change the definer to `sdcbc`@`127.0.0.1`*/  ? Do I need the port number = 8080 in this case to avoid port 80 conflicts with my windows system?  
  > **username_0**: OK. I edited all the DEFINER blocks in the mysqldump sql file to DEFINER=`sdcbc`@`localhost` and it now works on my local instance, too!!! I've read much of the CiviCRM documentation and some posts in this civicrm.stack.exchange regarding moving the database to different servers, and I don't think I found a reference to editing the triggers for correct operation. Did I miss that or is this something we should add to the documentation? And MANY THANKS - I definitely learned a thing or two along the way!  
  > **username_1**: Good job sticking with it. The experimental part seems like a preference that was added (see https://raw.githubusercontent.com/civicrm/civicrm-core/648631cd94799e87fe2347487d465b1a7256aa57/settings/Core.setting.php) but would require you to manually create the triggers, which isn't what you want. There is a reference to triggers at https://wiki.civicrm.org/confluence/display/CRMDOC/Moving+an+Existing+Installation+to+a+New+Server+or+Location - you visit  http://wordpress_site/wp-admin/admin.php?page=CiviCRM&q=civicrm/menu/rebuild&reset=1&triggerRebuild=1, but editing the dump works too.  
  > **username_0**: Thanks. I missed the line in the docs about rebuilding the triggers. I'll do that in the future rather than risk fat-fingering an edit to the sql directly. Always helps to RTFM.. "read the f...ing manual" ;)  
  
  ---
  
  