utfmb4 conversion gone wrong - Some columns in the databse have utf8mb4_general_ci others utf8mb4_unicode_ci causing and Illegal mix of collations

Question

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?

Answer 1

@mattwire helped me on chat.civicrm.org to identify the problem.

It was caused by the drupal script 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.