I got the following fatal error in the "Find Participants" page (Events > Find Participants), when I click on the Search button :
My website is using WordPress version 4.9.5 with PHP 7.0 and MySQL 5.7. This problem seems to occur in the 5.0.0 , 5.0.1 and 5.0.2 versions of CiviCRM. I've never seen this problem before version 5.0.0.
The SQL code in the error log :
SELECT
GROUP_CONCAT(DISTINCT contact_a.id) as contact_id,
GROUP_CONCAT(DISTINCT contact_a.contact_type) as `contact_type`,
GROUP_CONCAT(DISTINCT contact_a.contact_sub_type) as `contact_sub_type`,
GROUP_CONCAT(DISTINCT contact_a.sort_name) as `sort_name`,
GROUP_CONCAT(DISTINCT contact_a.display_name) as `display_name`,
GROUP_CONCAT(DISTINCT civicrm_event.id) as event_id,
GROUP_CONCAT(DISTINCT civicrm_event.title) as event_title,
GROUP_CONCAT(DISTINCT civicrm_event.start_date) as event_start_date,
GROUP_CONCAT(DISTINCT civicrm_event.end_date) as event_end_date,
GROUP_CONCAT(DISTINCT civicrm_event.default_role_id) as `default_role_id`,
civicrm_participant.id as participant_id,
GROUP_CONCAT(DISTINCT civicrm_participant.fee_level) as participant_fee_level,
GROUP_CONCAT(DISTINCT civicrm_participant.fee_amount) as participant_fee_amount,
GROUP_CONCAT(DISTINCT civicrm_participant.fee_currency) as participant_fee_currency,
GROUP_CONCAT(DISTINCT event_type.label) as event_type,
GROUP_CONCAT(DISTINCT civicrm_participant.status_id) as participant_status_id,
GROUP_CONCAT(DISTINCT participant_status.label) as participant_status,
GROUP_CONCAT(DISTINCT civicrm_participant.role_id) as participant_role_id,
GROUP_CONCAT(DISTINCT civicrm_participant.role_id) as participant_role,
GROUP_CONCAT(DISTINCT civicrm_participant.register_date) as participant_register_date,
GROUP_CONCAT(DISTINCT civicrm_participant.source) as participant_source,
GROUP_CONCAT(DISTINCT civicrm_note.note) as participant_note,
GROUP_CONCAT(DISTINCT civicrm_participant.is_pay_later) as participant_is_pay_later,
GROUP_CONCAT(DISTINCT civicrm_participant.is_test) as participant_is_test,
GROUP_CONCAT(DISTINCT civicrm_participant.registered_by_id) as participant_registered_by_id,
GROUP_CONCAT(DISTINCT discount_name.title) as participant_discount_name,
GROUP_CONCAT(DISTINCT civicrm_participant.campaign_id) as participant_campaign_id
FROM
civicrm_contact contact_a
LEFT JOIN civicrm_participant
ON civicrm_participant.contact_id = contact_a.id
LEFT JOIN civicrm_event
ON civicrm_participant.event_id = civicrm_event.id
LEFT JOIN civicrm_option_group option_group_event_type
ON (option_group_event_type.name = 'event_type')
LEFT JOIN civicrm_option_value event_type
ON (civicrm_event.event_type_id = event_type.value
AND option_group_event_type.id = event_type.option_group_id )
LEFT JOIN civicrm_discount discount
ON ( civicrm_participant.discount_id = discount.id )
LEFT JOIN civicrm_option_group discount_name
ON ( discount_name.id = discount.price_set_id )
LEFT JOIN civicrm_note
ON ( civicrm_note.entity_table = 'civicrm_participant'
AND civicrm_participant.id = civicrm_note.entity_id )
LEFT JOIN civicrm_option_group option_group_participant_role
ON (option_group_participant_role.name = 'participant_role')
LEFT JOIN civicrm_option_value participant_role
ON ((civicrm_participant.role_id = participant_role.value
OR SUBSTRING_INDEX(role_id,'', 1) = participant_role.value)
AND option_group_participant_role.id = participant_role.option_group_id )
LEFT JOIN civicrm_participant_status_type participant_status
ON (civicrm_participant.status_id = participant_status.id)
WHERE ( civicrm_participant.is_test = 0 )
AND (contact_a.is_deleted = 0)
GROUP BY civicrm_participant.id,
participant_register_date desc,
contact_a.id
ORDER BY `participant_register_date` desc,
`contact_a`.`id`
LIMIT 0, 50
The error :
[nativecode=1056 ** Can't group on 'participant_register_date']
I tested this code in phpmyadmin , and it works when I replace participant_register_date
in the GROUP BY
by civicrm_participant.register_date
but I don't know which file I need to change in civicrm to do the fix...
Thank you in advance for your help!