Details
-
Type:
Bug
-
Status: Done/Fixed
-
Priority:
Critical
-
Resolution: Fixed/Completed
-
Affects Version/s: 4.7.13, 4.7.14, 4.7.18, 4.7.20
-
Fix Version/s: 4.7.25
-
Component/s: Core CiviCRM, Dedupe
-
Labels:
-
Versioning Impact:Patch (backwards-compatible bug fixes)
-
Documentation Required?:None
-
Funding Source:Contributed Code
-
Verified?:Yes
Description
When a Find and Merge Duplicates rule has more than one field, and the rule is used to dedupe a group (rather than all contacts), CiviCRM comes to a grinding database error stop with a Can't reopen table: 'dedupe_copy' error.
To recreate:
- Create a "Find and Merge Duplicates" rule with two or more fields. You may give any weight that will result in matches.
- Choose "Use Rule".
- The original poster claims it does not matter if a group is used or not, but at least some tests without a group succeed without an error. It may be that if you lack access to view or edit all contacts, not using a group produces an error. Regardless, using a group will always produce an error.
See Jamie McClelland's comments below: it appears to be the union introduced in CRM-19069 that attempts to use the `dedupe_copy` temp table on both sides of the union. The union is of queries finding if the "t1" contact is in the group and if the "t2" contact is in the group.
Original poster's error:
Nov 07 12:27:38 [info] $Fatal Error Details = Array ( [callback] => Array ( [0] => CRM_Core_Error [1] => handle ) [code] => -1 [message] => DB Error: unknown error [mode] => 16 [debug_info] => INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 1 weight FROM civicrm_contact t1 JOIN civicrm_contact t2 USING (first_name) JOIN dedupe_copy ON dedupe_copy.id1 = t1.id AND dedupe_copy.id2 = t2.id WHERE t1.contact_type = 'Individual' AND t2.contact_type = 'Individual' AND t1.id < t2.id AND t1.first_name IS NOT NULL AND t1.first_name <> '' AND t1.id IN (3624,5991) UNION SELECT t1.id id1, t2.id id2, 1 weight FROM civicrm_contact t1 JOIN civicrm_contact t2 USING (first_name) JOIN dedupe_copy ON dedupe_copy.id1 = t1.id AND dedupe_copy.id2 = t2.id WHERE t1.contact_type = 'Individual' AND t2.contact_type = 'Individual' AND t1.id < t2.id AND t1.first_name IS NOT NULL AND t1.first_name <> '' AND t2.id IN (3624,5991) GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1137 ** Can't reopen table: 'dedupe_copy'] [type] => DB_Error [user_info] => INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 1 weight FROM civicrm_contact t1 JOIN civicrm_contact t2 USING (first_name) JOIN dedupe_copy ON dedupe_copy.id1 = t1.id AND dedupe_copy.id2 = t2.id WHERE t1.contact_type = 'Individual' AND t2.contact_type = 'Individual' AND t1.id < t2.id AND t1.first_name IS NOT NULL AND t1.first_name <> '' AND t1.id IN (3624,5991) UNION SELECT t1.id id1, t2.id id2, 1 weight FROM civicrm_contact t1 JOIN civicrm_contact t2 USING (first_name) JOIN dedupe_copy ON dedupe_copy.id1 = t1.id AND dedupe_copy.id2 = t2.id WHERE t1.contact_type = 'Individual' AND t2.contact_type = 'Individual' AND t1.id < t2.id AND t1.first_name IS NOT NULL AND t1.first_name <> '' AND t2.id IN (3624,5991) GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1137 ** Can't reopen table: 'dedupe_copy'] [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 1 weight FROM civicrm_contact t1 JOIN civicrm_contact t2 USING (first_name) JOIN dedupe_copy ON dedupe_copy.id1 = t1.id AND dedupe_copy.id2 = t2.id WHERE t1.contact_type = 'Individual' AND t2.contact_type = 'Individual' AND t1.id < t2.id AND t1.first_name IS NOT NULL AND t1.first_name <> '' AND t1.id IN (3624,5991) UNION SELECT t1.id id1, t2.id id2, 1 weight FROM civicrm_contact t1 JOIN civicrm_contact t2 USING (first_name) JOIN dedupe_copy ON dedupe_copy.id1 = t1.id AND dedupe_copy.id2 = t2.id WHERE t1.contact_type = 'Individual' AND t2.contact_type = 'Individual' AND t1.id < t2.id AND t1.first_name IS NOT NULL AND t1.first_name <> '' AND t2.id IN (3624,5991) GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1137 ** Can't reopen table: 'dedupe_copy']"] ) Nov 07 12:27:38 [info] $backTrace = #0 /dir/to/wp/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(229): CRM_Core_Error::backtrace("backTrace", TRUE) #1 [internal function](): CRM_Core_Error::handle(Object(DB_Error)) #2 /dir/to/wp/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(921): call_user_func((Array:2), Object(DB_Error)) #3 /dir/to/wp/wp-content/plugins/civicrm/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 1 weight F...") #4 /dir/to/wp/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 1 weight F...") #5 [internal function](): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, NULL, NULL, "INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 1 weight F...", "DB_Error", TRUE) #6 /dir/to/wp/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(224): call_user_func_array((Array:2), (Array:8)) #7 /dir/to/wp/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7)) #8 /dir/to/wp/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -1, NULL, NULL, "INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 1 weight F...", "DB_Error", TRUE) #9 /dir/to/wp/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, NULL, "1137 ** Can't reopen table: 'dedupe_copy'") #10 /dir/to/wp/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError() #11 /dir/to/wp/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 1 weight F...") #12 /dir/to/wp/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2438): DB_common->query("INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 1 weight F...") #13 /dir/to/wp/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1627): DB_DataObject->_query("INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 1 weight F...") #14 /dir/to/wp/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(349): DB_DataObject->query("INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 1 weight F...") #15 /dir/to/wp/wp-content/plugins/civicrm/civicrm/CRM/Dedupe/BAO/RuleGroup.php(246): CRM_Core_DAO->query("INSERT INTO dedupe (id1, id2, weight) SELECT t1.id id1, t2.id id2, 1 weight F...") #16 /dir/to/wp/wp-content/plugins/civicrm/civicrm/CRM/Dedupe/Finder.php(80): CRM_Dedupe_BAO_RuleGroup->fillTable() #17 /dir/to/wp/wp-content/plugins/civicrm/civicrm/CRM/Dedupe/Finder.php(177): CRM_Dedupe_Finder::dupes("19", (Array:2)) #18 /dir/to/wp/wp-content/plugins/civicrm/civicrm/CRM/Contact/Page/DedupeFind.php(153): CRM_Dedupe_Finder::dupesInGroup("19", "104", NULL) #19 /dir/to/wp/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(312): CRM_Contact_Page_DedupeFind->run((Array:3), NULL) #20 /dir/to/wp/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(86): CRM_Core_Invoke::runItem((Array:13)) #21 /dir/to/wp/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(54): CRM_Core_Invoke::_invoke((Array:3)) #22 /dir/to/wp/wp-content/plugins/civicrm/civicrm.php(1227): CRM_Core_Invoke::invoke((Array:3)) #23 [internal function](): CiviCRM_For_WordPress->invoke("") #24 /dir/to/wp/wp-includes/plugin.php(524): call_user_func_array((Array:2), (Array:1)) #25 /dir/to/wp/wp-admin/admin.php(222): do_action("toplevel_page_CiviCRM") #26 \{main}
Original poster's specs:
- CiviCRM 4.7.13
- Wordpress 4.6.1
- MySQL 5.1.73
- CentOS 6.8
- Apache 2.2.15
- PHP 5.4.40-4.el6 @centos-sclo-rh
Also confirmed in 4.7.14, 4.7.17, 4.7.18, and 4.7.20-RC.