1

When I try to use "Contacts > find and merge duplicate contacts" (using the default rule) I get an error. Something to do with "subunion.weight" see error trace below.

Anyone able to advise? tia

Database Error Code: 

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'subunion.weight' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, 1055
Additional 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 id1, id2, weight FROM (SELECT t1.contact_id id1, t2.contact_id id2, 10 weight FROM civicrm_email t1 JOIN civicrm_email t2 USING (email) WHERE t1.contact_id < t2.contact_id AND t1.email IS NOT NULL AND t1.email <> '' AND t1.contact_id IN (111,970,2435,2545,2626,2692,2703,2750,2765,3035,3132,3159,3312,3407,3452,3597,3850,3865,3879,3889,4026,4470,4607,4626,4627,4885,5531,5795,5807,6073,6406,6415,7202,7423,7572,7583,7749,7819,7945,8076,8768,8787,8867,8877,9051,9913,9968,10096,10138,10283,10292,10348,10358,10377,10505,10524,10526,10550,10634,10775,10806,11216,11340,12540,12780,12806,12860,12923,13366,13413,13437,13526,13539,13544,13641,13715,13727,14000,14002,14451,14754,14766,14776,14812,14833,15137,15364,16149,16150,16263,16349,16362,16366,16402,16462,16615,16705,16729,16731,16785,16818,16950,16985,17080,17128,17341,17434)
        UNION SELECT t1.contact_id id1, t2.contact_id id2, 10 weight FROM civicrm_email t1 JOIN civicrm_email t2 USING (email) WHERE t1.contact_id < t2.contact_id AND t1.email IS NOT NULL AND t1.email <> '' AND  t2.contact_id IN (111,970,2435,2545,2626,2692,2703,2750,2765,3035,3132,3159,3312,3407,3452,3597,3850,3865,3879,3889,4026,4470,4607,4626,4627,4885,5531,5795,5807,6073,6406,6415,7202,7423,7572,7583,7749,7819,7945,8076,8768,8787,8867,8877,9051,9913,9968,10096,10138,10283,10292,10348,10358,10377,10505,10524,10526,10550,10634,10775,10806,11216,11340,12540,12780,12806,12860,12923,13366,13413,13437,13526,13539,13544,13641,13715,13727,14000,14002,14451,14754,14766,14776,14812,14833,15137,15364,16149,16150,16263,16349,16362,16366,16402,16462,16615,16705,16729,16731,16785,16818,16950,16985,17080,17128,17341,17434)) subunion GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1055 ** Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'subunion.weight' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]
    [type] => DB_Error
    [user_info] => INSERT INTO dedupe (id1, id2, weight) SELECT id1, id2, weight FROM (SELECT t1.contact_id id1, t2.contact_id id2, 10 weight FROM civicrm_email t1 JOIN civicrm_email t2 USING (email) WHERE t1.contact_id < t2.contact_id AND t1.email IS NOT NULL AND t1.email <> '' AND t1.contact_id IN (111,970,2435,2545,2626,2692,2703,2750,2765,3035,3132,3159,3312,3407,3452,3597,3850,3865,3879,3889,4026,4470,4607,4626,4627,4885,5531,5795,5807,6073,6406,6415,7202,7423,7572,7583,7749,7819,7945,8076,8768,8787,8867,8877,9051,9913,9968,10096,10138,10283,10292,10348,10358,10377,10505,10524,10526,10550,10634,10775,10806,11216,11340,12540,12780,12806,12860,12923,13366,13413,13437,13526,13539,13544,13641,13715,13727,14000,14002,14451,14754,14766,14776,14812,14833,15137,15364,16149,16150,16263,16349,16362,16366,16402,16462,16615,16705,16729,16731,16785,16818,16950,16985,17080,17128,17341,17434)
        UNION SELECT t1.contact_id id1, t2.contact_id id2, 10 weight FROM civicrm_email t1 JOIN civicrm_email t2 USING (email) WHERE t1.contact_id < t2.contact_id AND t1.email IS NOT NULL AND t1.email <> '' AND  t2.contact_id IN (111,970,2435,2545,2626,2692,2703,2750,2765,3035,3132,3159,3312,3407,3452,3597,3850,3865,3879,3889,4026,4470,4607,4626,4627,4885,5531,5795,5807,6073,6406,6415,7202,7423,7572,7583,7749,7819,7945,8076,8768,8787,8867,8877,9051,9913,9968,10096,10138,10283,10292,10348,10358,10377,10505,10524,10526,10550,10634,10775,10806,11216,11340,12540,12780,12806,12860,12923,13366,13413,13437,13526,13539,13544,13641,13715,13727,14000,14002,14451,14754,14766,14776,14812,14833,15137,15364,16149,16150,16263,16349,16362,16366,16402,16462,16615,16705,16729,16731,16785,16818,16950,16985,17080,17128,17341,17434)) subunion GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1055 ** Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'subunion.weight' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]
    [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 id1, id2, weight FROM (SELECT t1.contact_id id1, t2.contact_id id2, 10 weight FROM civicrm_email t1 JOIN civicrm_email t2 USING (email) WHERE t1.contact_id < t2.contact_id AND t1.email IS NOT NULL AND t1.email <> '' AND t1.contact_id IN (111,970,2435,2545,2626,2692,2703,2750,2765,3035,3132,3159,3312,3407,3452,3597,3850,3865,3879,3889,4026,4470,4607,4626,4627,4885,5531,5795,5807,6073,6406,6415,7202,7423,7572,7583,7749,7819,7945,8076,8768,8787,8867,8877,9051,9913,9968,10096,10138,10283,10292,10348,10358,10377,10505,10524,10526,10550,10634,10775,10806,11216,11340,12540,12780,12806,12860,12923,13366,13413,13437,13526,13539,13544,13641,13715,13727,14000,14002,14451,14754,14766,14776,14812,14833,15137,15364,16149,16150,16263,16349,16362,16366,16402,16462,16615,16705,16729,16731,16785,16818,16950,16985,17080,17128,17341,17434)
        UNION SELECT t1.contact_id id1, t2.contact_id id2, 10 weight FROM civicrm_email t1 JOIN civicrm_email t2 USING (email) WHERE t1.contact_id < t2.contact_id AND t1.email IS NOT NULL AND t1.email <> '' AND  t2.contact_id IN (111,970,2435,2545,2626,2692,2703,2750,2765,3035,3132,3159,3312,3407,3452,3597,3850,3865,3879,3889,4026,4470,4607,4626,4627,4885,5531,5795,5807,6073,6406,6415,7202,7423,7572,7583,7749,7819,7945,8076,8768,8787,8867,8877,9051,9913,9968,10096,10138,10283,10292,10348,10358,10377,10505,10524,10526,10550,10634,10775,10806,11216,11340,12540,12780,12806,12860,12923,13366,13413,13437,13526,13539,13544,13641,13715,13727,14000,14002,14451,14754,14766,14776,14812,14833,15137,15364,16149,16150,16263,16349,16362,16366,16402,16462,16615,16705,16729,16731,16785,16818,16950,16985,17080,17128,17341,17434)) subunion GROUP BY id1, id2 ON DUPLICATE KEY UPDATE weight = weight + VALUES(weight) [nativecode=1055 ** Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'subunion.weight' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by]"]
)
Improve this question
1
  • What version of CiviCRM are you using and on what CMS? Do you get this error when attempting to merge any duplicate contacts or only specific ones? Is this the first time you have used this feature? When did it start happening? Thanks. Edit your question with the answer to these questions.
    – Christia
    Nov 19, 2017 at 12:54

1 Answer 1

Reset to default
1

The error suggests that on your MySQL database the sql_mode is set to only_full_group_by . This is a new feature of MySQL 5.7 and enabled by default. CiviCRM is able to handle this from version 4.7.10.

More information is found at https://issues.civicrm.org/jira/browse/CRM-18439 .

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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