Answers.Body: <p>@mattwire helped me on chat.civicrm.org to identify the problem.</p>
  <p>It was caused by the <a href="https://www.drupal.org/project/utf8mb4_convert" rel="nofollow noreferrer">drupal script</a> I used to convert drupal tables (to the wrong collation), and that script included the tables from civicrm which were listed in the drupal settings.php</p>
  <p>I've rerun the civicrm api conversion and the drupal script properly and now everything works.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-11-19T13:22:03.040Z
Answers.LastActivityDate: 2020-11-19T13:49:31.223Z
Answers.LastEditDate: 2020-11-19T13:49:31.223Z
Answers.UserDisplayName: Marko
Body: <p>I've recently run utf8mb conversion (through API explorer) on civi 5.28. Everything seemed OK, but now whenever I try to access events dashboard I get <strong>Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT)</strong> db error. And indeed when run the sql query to count columns by collations I get:</p>
  <pre><code>|2 | utf8mb4_bin|
  |159 | utf8mb4_general_ci|
  |692 | utf8mb4_unicode_ci|
  |24 | utf8_general_ci|
  |4 | utf8_unicode_ci|
  </code></pre>
  <p>The following query</p>
  <pre><code>show variables like 'collat%';
  </code></pre>
  <p>gives me</p>
  <pre><code>collation_connection    utf8mb4_unicode_ci
  collation_database  utf8mb4_unicode_ci
  collation_server    latin1_swedish_ci
  </code></pre>
  <p>Why are some columns utf8mb4_general_ci and others utf8mb4_unicode_ci? How to I resolve this?</p>
  
Tags: <database><php><mysql><core>
Title: utfmb4 conversion gone wrong - Some columns in the databse have utf8mb4_general_ci others utf8mb4_unicode_ci causing and Illegal mix of collations
ThreadText: # utfmb4 conversion gone wrong - Some columns in the databse have utf8mb4_general_ci others utf8mb4_unicode_ci causing and Illegal mix of collations
  
  (Asked by: username_0 on 2020-11-19)
  
  I've recently run utf8mb conversion (through API explorer) on civi 5.28. Everything seemed OK, but now whenever I try to access events dashboard I get **Illegal mix of collations (utf8mb4\_general\_ci,IMPLICIT) and (utf8mb4\_unicode\_ci,IMPLICIT)** db error. And indeed when run the sql query to count columns by collations I get:
  
  ```
  |2 | utf8mb4_bin|
  |159 | utf8mb4_general_ci|
  |692 | utf8mb4_unicode_ci|
  |24 | utf8_general_ci|
  |4 | utf8_unicode_ci|
  
  ```
  
  The following query
  
  ```
  show variables like 'collat%';
  
  ```
  
  gives me
  
  ```
  collation_connection    utf8mb4_unicode_ci
  collation_database  utf8mb4_unicode_ci
  collation_server    latin1_swedish_ci
  
  ```
  
  Why are some columns utf8mb4\_general\_ci and others utf8mb4\_unicode\_ci? How to I resolve this?
  
  **Tags:** database, php, mysql, core
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2020-11-19. Score: 1 | Accepted answer
  
  @mattwire helped me on chat.civicrm.org to identify the problem.
  
  It was caused by the [drupal script](https://www.drupal.org/project/utf8mb4_convert) I used to convert drupal tables (to the wrong collation), and that script included the tables from civicrm which were listed in the drupal settings.php
  
  I've rerun the civicrm api conversion and the drupal script properly and now everything works.
  
  
  ---
  
  