Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 4.7.30
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
-
Versioning Impact:Patch (backwards-compatible bug fixes)
-
Documentation Required?:None
-
Funding Source:Needs Funding
-
Verified?:No
Description
How to reproduce:
- open Search > Find contacts
- click button "Search"
- contacts is displayed as usual
- the error is reported in civilog
At MySQL >= 5.7 and sql_mode with ONLY_FULL_GROUP_BY there is a error in sql query for inserting into civicrm_prevnext_cache table during searching contacts.
The query contains too many columns in GROUP BY clause. Full query:
INSERT INTO civicrm_prevnext_cache (entity_table, entity_id1, entity_id2, cacheKey, data) SELECT DISTINCT 'civicrm_contact', contact_a.id, contact_a.id, 'civicrm search', contact_a.display_name FROM civicrm_contact contact_a LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1) WHERE (contact_a.is_deleted = 0) GROUP BY contact_a.id, IF(contact_a.contact_type = 'Individual', NULL, contact_a.organization_name), IF(contact_a.contact_type = 'Individual', contact_a.organization_name, NULL), civicrm_address.id, civicrm_phone.id, civicrm_email.id, civicrm_im.id, civicrm_worldregion.id ORDER BY `contact_a`.`sort_name` ASC, `contact_a`.`id` LIMIT 0, 500;
Those columns should not be added to this query:
- IF(contact_a.contact_type = 'Individual', NULL, contact_a.organization_name),
- IF(contact_a.contact_type = 'Individual', contact_a.organization_name, NULL),
- civicrm_address.id,
- civicrm_phone.id,
- civicrm_email.id,
- civicrm_im.id,
- civicrm_worldregion.id
I think that the problem is cased by this condition
!in_array($primaryKey, $groupBy)