Merging Contacts causes DB Error: Already Exists - conflict on setting table
Overview
After upgrading my civicrm from 4.7.31 to 5.27.0 I found that merging contacts breaks. It no longer merges the contacts but shows DB Error: Already Exist as shown in the snapshot.
Reproduction steps
- Click on Contacts -> New Individual.
- Entered First Name and Last Name and clicked Save.
- Follow same steps and create one more contact which is a duplicate.
- Check if both contacts have a navigation entry in the civicrm_setting table
- Click on Search -> Find Contacts.
- Search by the name and locate the duplicates
- Click on merge contact
- Click merge
- Got an error "DB error".
Current behaviour
Backtrace from ConfigLogs
Aug 06 04:12:23 [error] $Fatal Error Details = Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => handle
) [code] => -5
[message] => DB Error: already exists
[mode] => 16
[debug_info] => UPDATE civicrm_setting SET contact_id = 244336 WHERE contact_id = 243714 [nativecode=1062 ** Duplicate entry '1-244336-navigation' for key 'index_domain_contact_name']
[type] => DB_Error
[user_info] => UPDATE civicrm_setting SET contact_id = 244336 WHERE contact_id = 243714 [nativecode=1062 ** Duplicate entry '1-244336-navigation' for key 'index_domain_contact_name']
[to_string] => [db_error: message="DB Error: already exists" code=-5 mode=callback callback=CRM_Core_Error::handle prefix="" info="UPDATE civicrm_setting SET contact_id = 244336 WHERE contact_id = 243714 [nativecode=1062 ** Duplicate entry '1-244336-navigation' for key 'index_domain_contact_name']"]
)
Aug 06 04:12:23 [debug] $backTrace = #0 /var/www/html/sites/all/modules/civicrm/CRM/Core/Error.php(205): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/html/sites/all/modules/civicrm/packages/DB.php(998): PEAR_Error->__construct("DB Error: already exists", -5, 16, (Array:2), "UPDATE civicrm_setting SET contact_id = 244336 WHERE contact_id = 243714 [nat...")
#3 /var/www/html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-5, 16, (Array:2), "UPDATE civicrm_setting SET contact_id = 244336 WHERE contact_id = 243714 [nat...")
#4 /var/www/html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -5, 16, (Array:2), "UPDATE civicrm_setting SET contact_id = 244336 WHERE contact_id = 243714 [nat...", "DB_Error", TRUE)
#5 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1925): PEAR->__call("raiseError", (Array:7))
#6 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(935): DB_common->raiseError(-5, NULL, NULL, "UPDATE civicrm_setting SET contact_id = 244336 WHERE contact_id = 243714 [nat...", "1062 ** Duplicate entry '1-244336-navigation' for key 'index_domain_contact_n...")
#7 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(405): DB_mysqli->mysqliRaiseError()
#8 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1231): DB_mysqli->simpleQuery("UPDATE civicrm_setting SET contact_id = 244336 WHERE contact_id = 243714")
#9 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(2696): DB_common->query("UPDATE civicrm_setting SET contact_id = 244336 WHERE contact_id = 243714")
#10 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(1829): DB_DataObject->_query("UPDATE civicrm_setting SET contact_id = 244336 WHERE contact_id = 243714")
#11 /var/www/html/sites/all/modules/civicrm/CRM/Core/DAO.php(421): DB_DataObject->query("UPDATE civicrm_setting SET contact_id = 244336 WHERE contact_id = 243714")
#12 /var/www/html/sites/all/modules/civicrm/CRM/Core/DAO.php(1473): CRM_Core_DAO->query("UPDATE civicrm_setting SET contact_id = 244336 WHERE contact_id = 243714", TRUE)
#13 /var/www/html/sites/all/modules/civicrm/CRM/Dedupe/Merger.php(563): CRM_Core_DAO::executeQuery("UPDATE civicrm_setting SET contact_id = 244336 WHERE contact_id = 243714", (Array:0), TRUE, NULL, TRUE)
#14 /var/www/html/sites/all/modules/civicrm/CRM/Dedupe/Merger.php(1316): CRM_Dedupe_Merger::moveContactBelongings(Object(CRM_Dedupe_MergeHandler), (Array:12), (Array:0))
#15 /var/www/html/sites/all/modules/civicrm/CRM/Contact/Form/Merge.php(308): CRM_Dedupe_Merger::moveAllBelongings(244336, 243714, (Array:74))
#16 /var/www/html/sites/all/modules/civicrm/CRM/Core/Form.php(484): CRM_Contact_Form_Merge->postProcess()
#17 /var/www/html/sites/all/modules/civicrm/CRM/Core/StateMachine.php(144): CRM_Core_Form->mainProcess()
#18 /var/www/html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php(45): CRM_Core_StateMachine->perform(Object(CRM_Contact_Form_Merge), "next", "Next")
#19 /var/www/html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Contact_Form_Merge), "next")
#20 /var/www/html/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Merge), "next")
#21 /var/www/html/sites/all/modules/civicrm/CRM/Core/Controller.php(335): HTML_QuickForm_Page->handle("next")
#22 /var/www/html/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(98): CRM_Core_Controller->run()
#23 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(285): CRM_Utils_Wrapper->run("CRM_Contact_Form_Merge", "Merge Contact", (Array:0))
#24 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(68): CRM_Core_Invoke::runItem((Array:13))
#25 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(36): CRM_Core_Invoke::_invoke((Array:3))
#26 /var/www/html/sites/all/modules/civicrm/drupal/civicrm.module(454): CRM_Core_Invoke::invoke((Array:3))
#27 /var/www/html/includes/menu.inc(527): civicrm_invoke("contact", "merge")
#28 /var/www/html/index.php(21): menu_execute_active_handler()
#29 {main}
Expected behaviour
Contact should be merged
Environment information
- Browser: Tried on Firefox and chrome both
- CiviCRM: Tried with 5.27.0
- PHP: Tested with 7.2
- CMS: Drupal 7.32
- Database: MySQL 5.7.7
- Web Server: Apache 2.4
Comments
Issue seems to be related to https://github.com/civicrm/civicrm-core/commit/bbc11d1920687d2e88c0fd24697032fd9a1b673f as reverting this change fixes this issue.