Answers.Body: <p>The error suggests that on your MySQL database the <code>sql_mode</code> is set to <code>only_full_group_by</code> . This is a new feature of MySQL 5.7 and enabled by default. CiviCRM is able to handle this from version 4.7.10. </p>
  
  <p>More information is found at <a href="https://issues.civicrm.org/jira/browse/CRM-18439" rel="nofollow noreferrer">https://issues.civicrm.org/jira/browse/CRM-18439</a> .</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-11-19T17:18:39.613Z
Answers.LastActivityDate: 2017-11-19T17:18:39.613Z
Answers.UserDisplayName: Kainuk - CiviCooP
Body: <p>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.</p>
  
  <p>Anyone able to advise? tia</p>
  
  <p>Database Error Code: </p>
  
  <pre><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] =&gt; Array
          (
              [0] =&gt; CRM_Core_Error
              [1] =&gt; handle
          )
  
      [code] =&gt; -1
      [message] =&gt; DB Error: unknown error
      [mode] =&gt; 16
      [debug_info] =&gt; 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 &lt; t2.contact_id AND t1.email IS NOT NULL AND t1.email &lt;&gt; '' 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 &lt; t2.contact_id AND t1.email IS NOT NULL AND t1.email &lt;&gt; '' 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] =&gt; DB_Error
      [user_info] =&gt; 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 &lt; t2.contact_id AND t1.email IS NOT NULL AND t1.email &lt;&gt; '' 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 &lt; t2.contact_id AND t1.email IS NOT NULL AND t1.email &lt;&gt; '' 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] =&gt; [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 &lt; t2.contact_id AND t1.email IS NOT NULL AND t1.email &lt;&gt; '' 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 &lt; t2.contact_id AND t1.email IS NOT NULL AND t1.email &lt;&gt; '' 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]"]
  )
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-19T12:54:37.430Z
Comments.Text: 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.
Comments.UserDisplayName: Christia
Tags: <duplicate-contacts><mysql-5.7>
Title: Find and merge duplicate contacts error: "this is incompatible with sql_mode=only_full_group_by"
ThreadText: # Find and merge duplicate contacts error: "this is incompatible with sql_mode=only_full_group_by"
  
  (Asked by: username_0 on 2017-11-18)
  
  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]"]
  )
  
  **Tags:** duplicate-contacts, mysql-5.7
  
  > **Comments:**
  > **username_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.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2017-11-19. Score: 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> .
  
  
  ---
  
  