2

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.

3

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?

  • 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. – Karl Rudnick Feb 4 '17 at 6:25
  • 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. – Karl Rudnick Feb 4 '17 at 16:13
  • 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. – Karl Rudnick Feb 4 '17 at 16:23
  • 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? – Karl Rudnick Feb 4 '17 at 16:31
  • 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! – Karl Rudnick Feb 4 '17 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.