Dedupe using postcode match - DB error - Array

Question

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, Commented Apr 9, 2019 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, Commented Apr 9, 2019 at 14:52
What is URN? Is that a custom field? Something multivalued maybe? — Demerit, Commented Apr 9, 2019 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, Commented Apr 9, 2019 at 15:19
Are you able to debug around line 177 - find out what is generating "Array"? — Demerit, Commented Apr 9, 2019 at 15:29

Demerit · Accepted Answer · 2019-04-09 20:01:38Z

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

edited Apr 9, 2019 at 20:01

answered Apr 9, 2019 at 17:18

Demerit

24.7k2 gold badges12 silver badges34 bronze badges

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

Add a comment |

Stack Exchange Network

Dedupe using postcode match - DB error - Array

1 Answer 1

Your Answer

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

Linked

Hot Network Questions

Dedupe using postcode match - DB error - Array

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

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

Linked

Related

Hot Network Questions

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