Network error when paging contacts search pages or modifying rows per page
A network error appears when searching through pages of contacts or changing the amount of rows per page.
To recreate, search for contacts where more than page of results will be returned and click through to subsequent pages. I've found the same error occurs when I try to change the amount of results to be displayed per page.
I've narrowed the error down to a SQL error that is sourced from the CRM_Contact_Form_Search_Advance::handle().
I've attached a screen grab of the callstack all the way up to where it's about to execute the query.
MySQL returns this error message:
Expression #37 (closed) of SELECT list is not in GROUP BY clause and contains nonaggregated column 'civicrm.civicrm_address.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Here is the generated SQL query which contains the syntax error. It's worth noting that I'm using MySQL Ver 14.14 Distrib 5.7.24.
SELECT contact_a.id as contact_id, contact_a.contact_type as
contact_type, contact_a.contact_sub_type as
contact_sub_type, contact_a.sort_name as
sort_name, contact_a.display_name as
display_name, contact_a.do_not_email as
do_not_email, contact_a.do_not_phone as
do_not_phone, contact_a.do_not_mail as
do_not_mail, contact_a.do_not_sms as
do_not_sms, contact_a.do_not_trade as
do_not_trade, contact_a.is_opt_out as
is_opt_out, contact_a.legal_identifier as
legal_identifier, contact_a.external_identifier as
external_identifier, contact_a.nick_name as
nick_name, contact_a.legal_name as
legal_name, contact_a.image_URL as
image_URL, contact_a.preferred_communication_method as
preferred_communication_method, contact_a.preferred_language as
preferred_language, contact_a.preferred_mail_format as
preferred_mail_format, contact_a.first_name as
first_name, contact_a.middle_name as
middle_name, contact_a.last_name as
last_name, contact_a.prefix_id as
prefix_id, contact_a.suffix_id as
suffix_id, contact_a.formal_title as
formal_title, contact_a.communication_style_id as
communication_style_id, contact_a.job_title as
job_title, contact_a.gender_id as
gender_id, contact_a.birth_date as
birth_date, contact_a.is_deceased as
is_deceased, contact_a.deceased_date as
deceased_date, contact_a.household_name as
household_name, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name, contact_a.sic_code as
sic_code, contact_a.is_deleted as
contact_is_deleted, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer, civicrm_address.id as address_id, civicrm_address.street_address as
street_address, civicrm_address.supplemental_address_1 as
supplemental_address_1, civicrm_address.supplemental_address_2 as
supplemental_address_2, civicrm_address.supplemental_address_3 as
supplemental_address_3, civicrm_address.city as
city, civicrm_address.postal_code_suffix as
postal_code_suffix, civicrm_address.postal_code as
postal_code, civicrm_address.geo_code_1 as
geo_code_1, civicrm_address.geo_code_2 as
geo_code_2, civicrm_address.state_province_id as state_province_id, civicrm_address.country_id as country_id, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as
phone, civicrm_email.id as email_id, civicrm_email.email as
email, civicrm_email.on_hold as
on_hold, civicrm_im.id as im_id, civicrm_im.provider_id as provider_id, civicrm_im.name as
im, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as
world_region, (CASE WHEN contact_a.id = 20504 THEN 0 WHEN contact_a.id = 59412 THEN 1 WHEN contact_a.id = 35293 THEN 2 WHEN contact_a.id = 23486 THEN 3 WHEN contact_a.id = 15853 THEN 4 WHEN contact_a.id = 15637 THEN 5 WHEN contact_a.id = 13515 THEN 6 WHEN contact_a.id = 22624 THEN 7 WHEN contact_a.id = 29033 THEN 8 WHEN contact_a.id = 19796 THEN 9 WHEN contact_a.id = 15758 THEN 10 WHEN contact_a.id = 32061 THEN 11 WHEN contact_a.id = 20846 THEN 12 WHEN contact_a.id = 57645 THEN 13 WHEN contact_a.id = 13327 THEN 14 WHEN contact_a.id = 18350 THEN 15 WHEN contact_a.id = 13255 THEN 16 WHEN contact_a.id = 36333 THEN 17 WHEN contact_a.id = 32663 THEN 18 WHEN contact_a.id = 37926 THEN 19 WHEN contact_a.id = 20374 THEN 20 WHEN contact_a.id = 20209 THEN 21 WHEN contact_a.id = 58818 THEN 22 WHEN contact_a.id = 26836 THEN 23 WHEN contact_a.id = 16214 THEN 24 WHEN contact_a.id = 23812 THEN 25 WHEN contact_a.id = 19723 THEN 26 WHEN contact_a.id = 36548 THEN 27 WHEN contact_a.id = 13959 THEN 28 WHEN contact_a.id = 32108 THEN 29 WHEN contact_a.id = 41284 THEN 30 WHEN contact_a.id = 7466 THEN 31 WHEN contact_a.id = 47599 THEN 32 WHEN contact_a.id = 33269 THEN 33 WHEN contact_a.id = 70215 THEN 34 WHEN contact_a.id = 25438 THEN 35 WHEN contact_a.id = 39222 THEN 36 WHEN contact_a.id = 34185 THEN 37 WHEN contact_a.id = 47361 THEN 38 WHEN contact_a.id = 57611 THEN 39 WHEN contact_a.id = 60887 THEN 40 WHEN contact_a.id = 16999 THEN 41 WHEN contact_a.id = 36564 THEN 42 WHEN contact_a.id = 16807 THEN 43 WHEN contact_a.id = 42913 THEN 44 WHEN contact_a.id = 11872 THEN 45 WHEN contact_a.id = 13708 THEN 46 WHEN contact_a.id = 54936 THEN 47 WHEN contact_a.id = 67687 THEN 48 WHEN contact_a.id = 37070 THEN 49 END ) AS _wgt 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_worldregion ON civicrm_country.region_id = civicrm_worldregion.id WHERE ( ( ( ( contact_a.sort_name LIKE '%george%' ) OR ( contact_a.nick_name LIKE '%george%' ) OR ( civicrm_email.email LIKE '%george%' ) ) ) ) AND (contact_a.is_deleted = 0) AND contact_a.id IN (20504,59412,35293,23486,15853,15637,13515,22624,29033,19796,15758,32061,20846,57645,13327,18350,13255,36333,32663,37926,20374,20209,58818,26836,16214,23812,19723,36548,13959,32108,41284,7466,47599,33269,70215,25438,39222,34185,47361,57611,60887,16999,36564,16807,42913,11872,13708,54936,67687,37070) GROUP BY contact_a.id ORDER BY _wgt