2

We have a similar error to this post, but with Joomla 5.9.2 and CiviCRM 5.12.0. Error happens when trying to merge two contacts with Postcode match. Backtrace is below. Any ideas what could be causing this error? Thanks.

    #0 site_root/administrator/components/com_civicrm/civicrm/CRM/Core/Error.php(232): CRM_Core_Error::backtrace("backTrace", TRUE)
    #1 site_root/administrator/components/com_civicrm/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
    #2 site_root/administrator/components/com_civicrm/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: no such field", -19, 16, (Array:2), "INSERT INTO dedupe (id1, id2, weight) SELECT t1.contact_id id1, t2.contact_id...")
    #3 site_root/administrator/components/com_civicrm/civicrm/packages/PEAR.php(575): DB_Error->__construct(-19, 16, (Array:2), "INSERT INTO dedupe (id1, id2, weight) SELECT t1.contact_id id1, t2.contact_id...")
    #4 site_root/administrator/components/com_civicrm/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -19, 16, (Array:2), "INSERT INTO dedupe (id1, id2, weight) SELECT t1.contact_id id1, t2.contact_id...", "DB_Error", TRUE)
    #5 site_root/administrator/components/com_civicrm/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
    #6 site_root/administrator/components/com_civicrm/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-19, NULL, NULL, "INSERT INTO dedupe (id1, id2, weight) SELECT t1.contact_id id1, t2.contact_id...", "1054 ** Unknown column 'Array' in 'on clause'")
    #7 site_root/administrator/components/com_civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
    #8 site_root/administrator/components/com_civicrm/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("INSERT INTO dedupe (id1, id2, weight) SELECT t1.contact_id id1, t2.contact_id...")
    #9 site_root/administrator/components/com_civicrm/civicrm/packages/DB/DataObject.php(2415): DB_common->query("INSERT INTO dedupe (id1, id2, weight) SELECT t1.contact_id id1, t2.contact_id...")
    #10 site_root/administrator/components/com_civicrm/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("INSERT INTO dedupe (id1, id2, weight) SELECT t1.contact_id id1, t2.contact_id...")
    #11 site_root/administrator/components/com_civicrm/civicrm/CRM/Core/DAO.php(438): DB_DataObject->query("INSERT INTO dedupe (id1, id2, weight) SELECT t1.contact_id id1, t2.contact_id...")
    #12 site_root/administrator/components/com_civicrm/civicrm/CRM/Dedupe/BAO/RuleGroup.php(281): CRM_Core_DAO->query("INSERT INTO dedupe (id1, id2, weight) SELECT t1.contact_id id1, t2.contact_id...")
    #13 site_root/administrator/components/com_civicrm/civicrm/CRM/Dedupe/Finder.php(81): CRM_Dedupe_BAO_RuleGroup->fillTable()
    #14 site_root/administrator/components/com_civicrm/civicrm/CRM/Core/BAO/PrevNextCache.php(414): CRM_Dedupe_Finder::dupes("2", (Array:0), TRUE, NULL)
    #15 site_root/administrator/components/com_civicrm/civicrm/CRM/Dedupe/Merger.php(1996): CRM_Core_BAO_PrevNextCache::refillCache("2", NULL, "merge_Organization_2_0_0_46533", (Array:0), TRUE, NULL)
    #16 site_root/administrator/components/com_civicrm/civicrm/CRM/Contact/Page/DedupeFind.php(185): CRM_Dedupe_Merger::getDuplicatePairs("2", NULL, TRUE, 0, NULL, "", FALSE, (Array:0), TRUE, NULL)
    #17 site_root/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php(309): CRM_Contact_Page_DedupeFind->run((Array:3), NULL)
    #18 site_root/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))
    #19 site_root/administrator/components/com_civicrm/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
    #20 site_root/administrator/components/com_civicrm/civicrm.php(121): CRM_Core_Invoke::invoke((Array:3))
    #21 site_root/administrator/components/com_civicrm/civicrm.php(40): civicrm_invoke()
    #22 site_root/libraries/src/Component/ComponentHelper.php(402): require_once("site_root/administrator/components/com_civic...")
    #23 site_root/libraries/src/Component/ComponentHelper.php(377): Joomla\CMS\Component\ComponentHelper::executeComponent("site_root/administrator/components/com_civic...")
    #24 site_root/libraries/src/Application/AdministratorApplication.php(101): Joomla\CMS\Component\ComponentHelper::renderComponent("com_civicrm")
    #25 site_root/libraries/src/Application/AdministratorApplication.php(159): Joomla\CMS\Application\AdministratorApplication->dispatch()
    #26 site_root/libraries/src/Application/CMSApplication.php(196): Joomla\CMS\Application\AdministratorApplication->doExecute()
    #27 site_root/administrator/index.php(51): Joomla\CMS\Application\CMSApplication->execute()
    #28 {main}

Error in log (formatting's a bit wonky - should all be one block):

[info] $Fatal Error Details = Array

( [callback] => Array ( [0] => CRM_Core_Error 1 => handle )

[code] => -19
[message] => DB Error: no such field
[mode] => 16
[debug_info] => INSERT INTO dedupe (id1, id2, weight) SELECT t1.contact_id id1, t2.contact_id id2, 10 weight FROM civicrm_address t1 INNER JOIN civicrm_address t2 ON (t1.postal_code IS NOT NULL AND t2.postal_code IS NOT NULL AND t1.postal_code = t2.postal_code AND t1.postal_code <> '' AND t2.postal_code <> '' AND Array) WHERE t1.contact_id < t2.contact_id GROUP BY id1, id2, weight ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1054 ** Unknown column 'Array' in 'on clause']
[type] => DB_Error
[user_info] => INSERT INTO dedupe (id1, id2, weight) SELECT t1.contact_id id1, t2.contact_id id2, 10 weight FROM civicrm_address t1 INNER JOIN civicrm_address t2 ON (t1.postal_code IS NOT NULL AND t2.postal_code IS NOT NULL AND t1.postal_code = t2.postal_code AND t1.postal_code <> '' AND t2.postal_code <> '' AND Array) WHERE t1.contact_id < t2.contact_id GROUP BY id1, id2, weight ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1054 ** Unknown column 'Array' in 'on clause']
[to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO dedupe (id1, id2, weight) SELECT t1.contact_id id1, t2.contact_id id2, 10 weight FROM civicrm_address t1 INNER JOIN civicrm_address t2 ON (t1.postal_code IS NOT NULL AND t2.postal_code IS NOT NULL AND t1.postal_code = t2.postal_code AND t1.postal_code <> '' AND t2.postal_code <> '' AND Array) WHERE t1.contact_id < t2.contact_id GROUP BY id1, id2, weight ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1054 ** Unknown column 'Array' in 'on clause']"]

)

  • The problem is the part that says "1054 ** Unknown column 'Array' in 'on clause'". Are you able to put in some debugging around line 281 in CRM/Dedupe/BAO/RuleGroup.php to see what the full query is? Something is being evaluated as an array instead of a string. – Demerit Apr 9 at 13:58
  • Query looks like this: INSERT INTO dedupe (id1, id2, weight) SELECT t1.contact_id id1, t2.contact_id id2, 10 weight FROM civicrm_address t1 INNER JOIN civicrm_address t2 ON (t1.postal_code IS NOT NULL AND t2.postal_code IS NOT NULL AND t1.postal_code = t2.postal_code AND t1.postal_code <> '' AND t2.postal_code <> '' AND Array) WHERE t1.contact_id < t2.contact_id GROUP BY id1, id2, weight ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) – Jern Apr 9 at 14:52
  • What is URN? Is that a custom field? Something multivalued maybe? – Demerit Apr 9 at 15:07
  • I don't think URN is relevant. We get the error if we just dedupe with postal codes. Then $tableQueries looks like this: Array([civicrm_address.postal_code.10] => SELECT t1.contact_id id1, t2.contact_id id2, 10 weight FROM civicrm_address t1 INNER JOIN civicrm_address t2 ON (t1.postal_code IS NOT NULL AND t2.postal_code IS NOT NULL AND t1.postal_code = t2.postal_code AND t1.postal_code <> '' AND t2.postal_code <> '' AND Array) WHERE t1.contact_id < t2.contact_id) – Jern Apr 9 at 15:19
  • Are you able to debug around line 177 - find out what is generating "Array"? – Demerit Apr 9 at 15:29
2

The problem is here: https://github.com/civicrm/civicrm-core/blob/master/CRM/Dedupe/BAO/Rule.php#L110

Instead of

$innerJoinClauses[] = ['t1.location_type_id = t2.location_type_id'];

it should be

$innerJoinClauses[] = 't1.location_type_id = t2.location_type_id';

EDIT: https://github.com/civicrm/civicrm-core/pull/14013

  • Yes, that's done it. Brilliant. Thanks! – Jern Apr 9 at 19:18

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.