Error on export with "merge all with same address"
We're getting a traceback on this system with drupal 7 / civi 5.28.2. I'm not able to reproduce it on dmaster so I'm not sure if it's a version issue, certain configuration, or related to the specific data this site has. The steps below were previously working (from what I've been told) on an earlier version of civi.
- Advanced search and just select contact type as Individual.
- Select all records and choose action Export contacts.
- Select merge option as "merge all contacts with the same address", and hit continue.
Result: "DB Error: syntax error" error page. I get similar results with the other (household) merge option and with a field mapping rather than primary fields.
Any thoughts? Have included the (cleaned up) query below from the civi log, and the traceback.
Thanks, Martin
QUERY
SELECT contact_a.id as contact_id, [long_list_of_other_fields]
FROM civicrm_contact contact_a
LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )
LEFT JOIN civicrm_country ON ( civicrm_address.country_id = civicrm_country.id )
LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)
LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)
LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1)
LEFT JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id AND civicrm_openid.is_primary = 1 )
LEFT JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id
LEFT JOIN civicrm_group_contact_cache ON contact_a.id = civicrm_group_contact_cache.contact_id
LEFT JOIN civicrm_entity_tag ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND civicrm_entity_tag.entity_id = contact_a.id )
LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contact' AND contact_a.id = civicrm_note.entity_id )
LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id
LEFT JOIN civicrm_tag ON civicrm_entity_tag.tag_id = civicrm_tag.id
LEFT JOIN civicrm_website ON contact_a.id = civicrm_website.contact_id
INNER JOIN civicrm_relationship crel ON crel.contact_id_b = contact_a.id AND crel.relationship_type_id =
WHERE contact_a.is_deleted = 0
AND crel.contact_id_a IN ( 1,2,3,[etc..] )
AND (crel.is_active = 1 AND ( crel.end_date is NULL OR crel.end_date >= 20201005 ) )
GROUP BY crel.contact_id_a
TRACEBACK
Oct 05 15:41:29 [debug] $backTrace = #0 /home/abc/public_html/sites/all/modules/civicrm/CRM/Core/Error.php(205): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /home/abc/public_html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::handle(Object(DB_Error))
#2 /home/abc/public_html/sites/all/modules/civicrm/packages/DB.php(997): PEAR_Error->__construct("DB Error: syntax error", -2, 16, (Array:2), "SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#3 /home/abc/public_html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-2, 16, (Array:2), "SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#4 /home/abc/public_html/sites/all/modules/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -2, 16, (Array:2), "SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...", "DB_Error", TRUE)
#5 /home/abc/public_html/sites/all/modules/civicrm/packages/DB/common.php(1925): PEAR->__call("raiseError", (Array:7))
#6 /home/abc/public_html/sites/all/modules/civicrm/packages/DB/mysqli.php(936): DB_common->raiseError(-2, NULL, NULL, "SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...", "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
#7 /home/abc/public_html/sites/all/modules/civicrm/packages/DB/mysqli.php(406): DB_mysqli->mysqliRaiseError()
#8 /home/abc/public_html/sites/all/modules/civicrm/packages/DB/common.php(1231): DB_mysqli->simpleQuery("SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#9 /home/abc/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(2696): DB_common->query("SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#10 /home/abc/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(1829): DB_DataObject->_query("SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#11 /home/abc/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(439): DB_DataObject->query("SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#12 /home/abc/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(1528): CRM_Core_DAO->query("SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...", TRUE)
#13 /home/abc/public_html/sites/all/modules/civicrm/CRM/Export/BAO/Export.php(471): CRM_Core_DAO::executeQuery("SELECT contact_a.id as contact_id, contact_a.contact_type as `contact_type`, ...")
#14 /home/abc/public_html/sites/all/modules/civicrm/CRM/Export/BAO/Export.php(131): CRM_Export_BAO_Export::buildRelatedContactArray(TRUE, (Array:1768), Object(CRM_Export_BAO_ExportProcessor), NULL)
#15 /home/abc/public_html/sites/all/modules/civicrm/CRM/Export/Form/Select.php(388): CRM_Export_BAO_Export::exportComponents(TRUE, (Array:1768), (Array:5), "`sort_name` asc", NULL, NULL, 1, " contact_a.id IN ( 836,655,654,2393,2,837,838,839,2455,351,841,840,2503,656,3...", NULL, 1, 0, (Array:11), "AND")
#16 /home/abc/public_html/sites/all/modules/civicrm/CRM/Core/Form.php(504): CRM_Export_Form_Select->postProcess()
#17 /home/abc/public_html/sites/all/modules/civicrm/CRM/Core/StateMachine.php(144): CRM_Core_Form->mainProcess()
#18 /home/abc/public_html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php(45): CRM_Core_StateMachine->perform(Object(CRM_Export_Form_Select), "next", "Next")
#19 /home/abc/public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Export_Form_Select), "next")
#20 /home/abc/public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Export_Form_Select), "next")
#21 /home/abc/public_html/sites/all/modules/civicrm/CRM/Core/Controller.php(347): HTML_QuickForm_Page->handle("next")
#22 /home/abc/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(312): CRM_Core_Controller->run((Array:4), (Array:0))
#23 /home/abc/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(68): CRM_Core_Invoke::runItem((Array:13))
#24 /home/abc/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(36): CRM_Core_Invoke::_invoke((Array:4))
#25 /home/abc/public_html/sites/all/modules/civicrm/drupal/civicrm.module(454): CRM_Core_Invoke::invoke((Array:4))
#26 /home/abc/public_html/includes/menu.inc(527): civicrm_invoke("contact", "search", "advanced")
#27 /home/abc/public_html/index.php(21): menu_execute_active_handler()
#28 {main}