Answers.Body: <p>We had a similar type of issue on our mailing group list.</p>
  <p>The CiviCRM log does not show any errors.
  while putting a debug statement in the API. It was showing. </p>
  <pre><code>ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION'
  </code></pre>
  <pre><code>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&amp;_=1715625100790 500 (Internal Server Error)
  </code></pre>
  <p>SQL Query:</p>
  <pre><code>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(&quot;groups_&quot;, `a`.`id`) AS `key`, IF(`a`.`is_hidden` , &quot;Search Results&quot; , `a`.`title`) AS `label`, `a`.`description` AS `description`, &quot;group&quot; AS `entity`, NULL AS `type`, IF(`a`.`saved_search_id` , &quot;fa-lightbulb-o&quot; , &quot;fa-group&quot;) 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` = &quot;Include&quot; AND `mailing_group`.`entity_table` = &quot;civicrm_group&quot; AND `a`.`is_hidden` = &quot;1&quot; AND `mailing_group`.`mailing_id` = &quot;38&quot; 
  LEFT JOIN `civicrm_saved_search` `saved_search_id_1` ON `a`.`saved_search_id` =  `saved_search_id_1`.`id`
  WHERE (`a`.`is_active` = &quot;1&quot;)
    AND (`a`.`group_type` LIKE &quot;%2%&quot;)
    AND ((`saved_search_id_1`.`expires_date` IS NULL
  OR `saved_search_id_1`.`expires_date` &gt; NOW()))
    AND ((`a`.`is_hidden` = &quot;0&quot; 
  OR `mailing_group`.`id` IS NOT NULL))
  ) UNION ALL (SELECT CONCAT(&quot;mailings_&quot;, `a`.`id`) AS `key`, `a`.`name` AS `name`, `a`.`subject` AS `subject`, &quot;mailing&quot; AS `entity`, IF(`a`.`is_archived` , &quot;Archived Mailing&quot; , IF(`a`.`is_completed` , &quot;Sent Mailing&quot; , &quot;Unsent Mailing&quot;)) AS `type`, IF(`a`.`is_archived` , &quot;fa-archive&quot; , IF(`a`.`is_completed` , &quot;fa-envelope&quot; , &quot;fa-file-o&quot;)) 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` != &quot;38&quot;)
    AND (`a`.`domain_id` = &quot;1&quot;)
  ) UNION ALL (SELECT CONCAT(&quot;groups_&quot;, `a`.`id`) AS `key`, IF(`a`.`is_hidden` , &quot;Search Results&quot; , `a`.`title`) AS `label`, `a`.`description` AS `description`, &quot;group&quot; AS `entity`, NULL AS `type`, IF(`a`.`saved_search_id` , &quot;fa-lightbulb-o&quot; , &quot;fa-group&quot;) 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` = &quot;Include&quot; AND `mailing_group`.`entity_table` = &quot;civicrm_group&quot; AND `a`.`is_hidden` = &quot;1&quot; AND `mailing_group`.`mailing_id` = &quot;38&quot; 
  LEFT JOIN `civicrm_saved_search` `saved_search_id_1` ON `a`.`saved_search_id` =  `saved_search_id_1`.`id`
  WHERE (`a`.`is_active` = &quot;1&quot;)
    AND (`a`.`group_type` LIKE &quot;%2%&quot;)
    AND ((`saved_search_id_1`.`expires_date` IS NULL
  OR `saved_search_id_1`.`expires_date` &gt; NOW()))
    AND ((`a`.`is_hidden` = &quot;0&quot; 
  OR `mailing_group`.`id` IS NOT NULL))
  ) UNION ALL (SELECT CONCAT(&quot;mailings_&quot;, `a`.`id`) AS `key`, `a`.`name` AS `name`, `a`.`subject` AS `subject`, &quot;mailing&quot; AS `entity`, IF(`a`.`is_archived` , &quot;Archived Mailing&quot; , IF(`a`.`is_completed` , &quot;Sent Mailing&quot; , &quot;Unsent Mailing&quot;)) AS `type`, IF(`a`.`is_archived` , &quot;fa-archive&quot; , IF(`a`.`is_completed` , &quot;fa-envelope&quot; , &quot;fa-file-o&quot;)) 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` != &quot;38&quot;)
    AND (`a`.`domain_id` = &quot;1&quot;)
  )) a
  ORDER BY `entity` ASC, `label` ASC
  LIMIT 16
  OFFSET 0
  </code></pre>
  <p>I looked at the <code>civicrm_group</code> table and saw that the collation for the <code>name </code>and <code>title</code> columns is <code>utf8mb4_unicode_ci</code> instead of <code>utf8mb4_general_ci</code> which most other columns are.
  There could be additional columns that are off from what most of them are.</p>
  <p>We need to make all the columns have the same collation.</p>
  <p>To fix this type of issue, fix civicrm <code>table</code>, <code>column</code> collation type.
  CHARACTER : <code>utf8mb4</code>
  COLLATE : <code>utf8mb4_unicode_ci</code></p>
  <p>using civicrm api</p>
  <pre><code>    $result = civicrm_api3('System', 'utf8conversion');
  </code></pre>
  <p>Example:</p>
  <pre><code>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.';
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-06-07T19:18:15.700Z
Answers.Text: This is the answer. Thanks Sunil. I used phpmyadmin (Operations - collation - change all tables + change all tables columns)
Answers.UserDisplayName: Jern
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-06-07T11:38:56.503Z
Answers.LastActivityDate: 2024-06-07T11:38:56.503Z
Answers.UserDisplayName: Sunil Pawar
Body: <p>I know this has been <a href="https://civicrm.stackexchange.com/questions/33070/unable-to-load-the-recipients-group-when-sending-mailing-with-civimail">asked before</a>, but in that case the problem was fixed without identifying the problem or the solution.</p>
  <p>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 &quot;Loading failed&quot; 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.</p>
  <p>CiviCRM version 5.65.2 (but i think it began several versions ago).
  CMS is WP. Php 8.1 or 7.4.</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-09-25T11:47:51.517Z
Comments.Text: 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.
Comments.UserDisplayName: Coleman
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-09-25T11:54:30.580Z
Comments.Text: 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.
Comments.UserDisplayName: Jern
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-09-25T15:46:16.717Z
Comments.Text: Just noticed that there's a 500 error showing in the console when I open the mailing: POST https://domain.co.uk/wp-admin/admin.php?page=CiviCRM&q=civicrm/ajax/api4/EntitySet/autocomplete
Comments.UserDisplayName: Jern
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-09-25T15:46:56.850Z
Comments.Text: And an "Uncaught error (in promise) from /wp-admin/admin.php?…a/#/mailing/630:1
Comments.UserDisplayName: Jern
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-09-26T07:52:31.017Z
Comments.Text: 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?
Comments.UserDisplayName: RebeccaTregenna
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-09-26T11:24:22.260Z
Comments.Text: Thank you @RebeccaTregenna. Can you remember what the fix was? I tried disabling ModSecurity but that didn't help.
Comments.UserDisplayName: Jern
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-09-26T12:12:58.887Z
Comments.Text: If I've interpreted it correctly, the browser console indicates a syntax error at the following line in jquery.min.js
  
  return T.querySelector(":has(*,:jqfake)")
  
  code: 12
  message: "Failed to execute 'querySelector' on 'Document': ':has(*,:jqfake)' is not a valid selector."
  name: "SyntaxError"
Comments.UserDisplayName: Jern
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-09-26T13:51:56.460Z
Comments.Text: OK, I've had to give up. I've run out of ideas for debugging. I can fiddle the mailings by adding the inclusions and exclusions directly in the mailing_group table. If anyone can suggest any more debugging ideas, that would be great.
Comments.UserDisplayName: Jern
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-10-31T16:09:23.070Z
Comments.Text: @RebeccaTregenna - we are having exactly the problem you mention: the autocomplete is trying to fetch data with http. I've tried everything I can think of to resolve it, and the only thing that works so far is switching from nginx to apache... but that breaks a few other things. Do you recall how you resolved it?
Comments.UserDisplayName: zip
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-01T11:27:09.590Z
Comments.Text: @zip_000 we added some settings in the Drupal settings.php file to support reverse proxy in case that helps (not sure what CMS you are on)
Comments.UserDisplayName: RebeccaTregenna
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-01T15:29:16.900Z
Comments.Text: @RebeccaTregenna - thanks! Also on Drupal. I was able to get things working correctly under Nginx by adding an additional header. It was a lot of trial and error to get there though.
Comments.UserDisplayName: zip
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-03T08:40:45.113Z
Comments.Text: @zip_000 Congratulations. What was the additional header which did the trick? Can you give any pointers? Thanks.
Comments.UserDisplayName: Jern
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2023-11-03T13:36:52.887Z
Comments.Text: @Jern - in the http section of nginx.conf I added: `map $http_x_forwarded_proto $fastcgi_param_https_variable {
      default '$scheme';
      https 'on';
  }` and in the server - location section of the site configuration file, I added `fastcgi_param HTTPS $fastcgi_param_https_variable;` This was a throwing everything at the wall to see what sticks situation though, so I can't say what it was exactly. The last piece though was adding the $scheme to the map section.
Comments.UserDisplayName: zip
Tags: <civimail><groups>
Title: "Loading Failed" when selecting recipient group in Civimail mailing
ThreadText: # "Loading Failed" when selecting recipient group in Civimail mailing
  
  (Asked by: username_0 on 2023-09-25)
  
  I know this has been [asked before](https://civicrm.stackexchange.com/questions/33070/unable-to-load-the-recipients-group-when-sending-mailing-with-civimail), 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.
  
  **Tags:** civimail, groups
  
  > **Comments:**
  > **username_1**: 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.  
  > **username_0**: 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.  
  > **username_0**: Just noticed that there's a 500 error showing in the console when I open the mailing: POST https://domain.co.uk/wp-admin/admin.php?page=CiviCRM&q=civicrm/ajax/api4/EntitySet/autocomplete  
  > **username_0**: And an "Uncaught error (in promise) from /wp-admin/admin.php?…a/#/mailing/630:1  
  > **username_2**: 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?  
  > **username_0**: Thank you @username_2. Can you remember what the fix was? I tried disabling ModSecurity but that didn't help.  
  > **username_0**: If I've interpreted it correctly, the browser console indicates a syntax error at the following line in jquery.min.js  return T.querySelector(":has(*,:jqfake)")  code: 12 message: "Failed to execute 'querySelector' on 'Document': ':has(*,:jqfake)' is not a valid selector." name: "SyntaxError"  
  > **username_0**: OK, I've had to give up. I've run out of ideas for debugging. I can fiddle the mailings by adding the inclusions and exclusions directly in the mailing_group table. If anyone can suggest any more debugging ideas, that would be great.  
  > **username_3**: @username_2 - we are having exactly the problem you mention: the autocomplete is trying to fetch data with http. I've tried everything I can think of to resolve it, and the only thing that works so far is switching from nginx to apache... but that breaks a few other things. Do you recall how you resolved it?  
  > **username_2**: @username_3_000 we added some settings in the Drupal settings.php file to support reverse proxy in case that helps (not sure what CMS you are on)  
  > **username_3**: @username_2 - thanks! Also on Drupal. I was able to get things working correctly under Nginx by adding an additional header. It was a lot of trial and error to get there though.  
  > **username_0**: @username_3_000 Congratulations. What was the additional header which did the trick? Can you give any pointers? Thanks.  
  > **username_3**: @username_0 - in the http section of nginx.conf I added: `map $http_x_forwarded_proto $fastcgi_param_https_variable {     default '$scheme';     https 'on'; }` and in the server - location section of the site configuration file, I added `fastcgi_param HTTPS $fastcgi_param_https_variable;` This was a throwing everything at the wall to see what sticks situation though, so I can't say what it was exactly. The last piece though was adding the $scheme to the map section.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_4 on 2024-06-07. Score: 3 | Accepted answer
  
  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.';
  
  > **Comments:**
  > **username_0**: This is the answer. Thanks Sunil. I used phpmyadmin (Operations - collation - change all tables + change all tables columns)  
  
  ---
  
  