1

I know this has been asked before, but in that case the problem was fixed without identifying the problem or the solution.

I'm trying to send mails using CiviMail, but when I try to select mailing group in the include group or exclude groups, an error "Loading failed" appears. The browser console shows a 500 error, which makes me think the problem may be with the server, not the CiviCRM installation, but I'm not sure what to try. The problem started happening a couple of months ago. I've tried different php versions, different browsers, restarting the server, rebuilding the cache. I can't see anything relevant in the system log or the CiviCRM log. Any suggestions what else I could try to find the problem? Thank you.

CiviCRM version 5.65.2 (but i think it began several versions ago). CMS is WP. Php 8.1 or 7.4.

13
  • Is the problem consistent or intermittent? Can you verify that the civi_mail extension (not just the component but also the extension) is enabled? Also verify that search_kit is enabled.
    – Coleman
    Commented Sep 25, 2023 at 11:47
  • Thanks. It's consistent. Yes, civi_mail extension and search_kit are both enabled. I don't know if it's relevant, but search_kit and flexmailer are both marked as Required, so can't be disabled.
    – Jern
    Commented Sep 25, 2023 at 11:54
  • Just noticed that there's a 500 error showing in the console when I open the mailing: POST domain.co.uk/wp-admin/admin.php?page=CiviCRM&q=civicrm/ajax/…
    – Jern
    Commented Sep 25, 2023 at 15:46
  • And an "Uncaught error (in promise) from /wp-admin/admin.php?…a/#/mailing/630:1
    – Jern
    Commented Sep 25, 2023 at 15:46
  • We had something similar - the autocomplete for the group search was trying to fetch the data with non-https hence the failure. Maybe worth looking into? Commented Sep 26, 2023 at 7:52

1 Answer 1

3

We had a similar type of issue on our mailing group list.

The CiviCRM log does not show any errors. while putting a debug statement in the API. It was showing. 

ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION'
https://dev.customerdomain.org/civicrm/ajax/api4/EntitySet%3Aautocomplete?params=%7B%22input%22%3A%22tes%22%2C%22page%22%3A1%2C%22formName%22%3A%22crmMailing.34%22%2C%22fieldName%22%3A%22Mailing.recipients_include%22%7D&_=1715625100790 500 (Internal Server Error)

SQL Query:

SELECT `key` AS `key`, `label` AS `label`, `description` AS `description`, `type` AS `type`, `icon` AS `icon`, `date` AS `date`, (`is_hidden` = 1) AS `locked`, `key` AS `key`, `label` AS `label`, `description` AS `description`, `type` AS `type`, `icon` AS `icon`, `date` AS `date`, (`is_hidden` = 1) AS `locked`
FROM ((SELECT CONCAT("groups_", `a`.`id`) AS `key`, IF(`a`.`is_hidden` , "Search Results" , `a`.`title`) AS `label`, `a`.`description` AS `description`, "group" AS `entity`, NULL AS `type`, IF(`a`.`saved_search_id` , "fa-lightbulb-o" , "fa-group") AS `icon`, DATE(`saved_search_id_1`.`created_date`) AS `date`, `a`.`is_hidden` AS `is_hidden`, 0 AS `_api_set_index`
FROM civicrm_group a
LEFT JOIN (`civicrm_mailing_group` `mailing_group`) ON ((`mailing_group`.`entity_table` = 'civicrm_group' OR `mailing_group`.`entity_table` = 'civicrm_mailing')) AND `a`.`id` = `mailing_group`.`entity_id` AND `mailing_group`.`group_type` = "Include" AND `mailing_group`.`entity_table` = "civicrm_group" AND `a`.`is_hidden` = "1" AND `mailing_group`.`mailing_id` = "38" 
LEFT JOIN `civicrm_saved_search` `saved_search_id_1` ON `a`.`saved_search_id` =  `saved_search_id_1`.`id`
WHERE (`a`.`is_active` = "1")
  AND (`a`.`group_type` LIKE "%2%")
  AND ((`saved_search_id_1`.`expires_date` IS NULL
OR `saved_search_id_1`.`expires_date` > NOW()))
  AND ((`a`.`is_hidden` = "0" 
OR `mailing_group`.`id` IS NOT NULL))
) UNION ALL (SELECT CONCAT("mailings_", `a`.`id`) AS `key`, `a`.`name` AS `name`, `a`.`subject` AS `subject`, "mailing" AS `entity`, IF(`a`.`is_archived` , "Archived Mailing" , IF(`a`.`is_completed` , "Sent Mailing" , "Unsent Mailing")) AS `type`, IF(`a`.`is_archived` , "fa-archive" , IF(`a`.`is_completed` , "fa-envelope" , "fa-file-o")) AS `icon`, COALESCE(DATE(`a`.`scheduled_date`), DATE(`a`.`created_date`)) AS `COALESCE:scheduled_date_created_date`, 0 AS `0`, 1 AS `_api_set_index`
FROM civicrm_mailing a
WHERE (`a`.`id` != "38")
  AND (`a`.`domain_id` = "1")
) UNION ALL (SELECT CONCAT("groups_", `a`.`id`) AS `key`, IF(`a`.`is_hidden` , "Search Results" , `a`.`title`) AS `label`, `a`.`description` AS `description`, "group" AS `entity`, NULL AS `type`, IF(`a`.`saved_search_id` , "fa-lightbulb-o" , "fa-group") AS `icon`, DATE(`saved_search_id_1`.`created_date`) AS `date`, `a`.`is_hidden` AS `is_hidden`, 0 AS `_api_set_index`
FROM civicrm_group a
LEFT JOIN (`civicrm_mailing_group` `mailing_group`) ON ((`mailing_group`.`entity_table` = 'civicrm_group' OR `mailing_group`.`entity_table` = 'civicrm_mailing')) AND `a`.`id` = `mailing_group`.`entity_id` AND `mailing_group`.`group_type` = "Include" AND `mailing_group`.`entity_table` = "civicrm_group" AND `a`.`is_hidden` = "1" AND `mailing_group`.`mailing_id` = "38" 
LEFT JOIN `civicrm_saved_search` `saved_search_id_1` ON `a`.`saved_search_id` =  `saved_search_id_1`.`id`
WHERE (`a`.`is_active` = "1")
  AND (`a`.`group_type` LIKE "%2%")
  AND ((`saved_search_id_1`.`expires_date` IS NULL
OR `saved_search_id_1`.`expires_date` > NOW()))
  AND ((`a`.`is_hidden` = "0" 
OR `mailing_group`.`id` IS NOT NULL))
) UNION ALL (SELECT CONCAT("mailings_", `a`.`id`) AS `key`, `a`.`name` AS `name`, `a`.`subject` AS `subject`, "mailing" AS `entity`, IF(`a`.`is_archived` , "Archived Mailing" , IF(`a`.`is_completed` , "Sent Mailing" , "Unsent Mailing")) AS `type`, IF(`a`.`is_archived` , "fa-archive" , IF(`a`.`is_completed` , "fa-envelope" , "fa-file-o")) AS `icon`, COALESCE(DATE(`a`.`scheduled_date`), DATE(`a`.`created_date`)) AS `COALESCE:scheduled_date_created_date`, 0 AS `0`, 1 AS `_api_set_index`
FROM civicrm_mailing a
WHERE (`a`.`id` != "38")
  AND (`a`.`domain_id` = "1")
)) a
ORDER BY `entity` ASC, `label` ASC
LIMIT 16
OFFSET 0

I looked at the civicrm_group table and saw that the collation for the name and title columns is utf8mb4_unicode_ci instead of utf8mb4_general_ci which most other columns are. There could be additional columns that are off from what most of them are.

We need to make all the columns have the same collation.

To fix this type of issue, fix civicrm table, column collation type. CHARACTER : utf8mb4 COLLATE : utf8mb4_unicode_ci

using civicrm api

    $result = civicrm_api3('System', 'utf8conversion');

Example:

ALTER TABLE `civicrm_group` CHANGE `name` `name` VARCHAR(255) 
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Internal name of Group.'; 
  
ALTER TABLE `civicrm_group` CHANGE `title` `title` VARCHAR(255) 
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Alternative public title for this Group.';
1
  • 1
    This is the answer. Thanks Sunil. I used phpmyadmin (Operations - collation - change all tables + change all tables columns)
    – Jern
    Commented Jun 7 at 19:18

Your Answer

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

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