Illegal mix of collations

Question

Using Joomla 3.8.6, CiviCRM 4.7.31, php 7.0.28, mysql 5.6.39

What should the database collation be for the CiviCRM tables? Mine are all utf8_general_ci. None of the tables in the database are utf8_unicode_ci, but I'm getting the illegal mix error below. Not sure when it started (may have been after upgrade to 4.7.31), but clicking on "Reports listing" or one of the report collections like "Contribution Reports" generates the following error:

"1267 ** Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='"

The error seems to come from CRM/Report/Page/InstanceList.php. See traceback below.

Edit: I now realise that the whole Report menu is not as it should be. It looks like this:

whereas it should look more like this:

Could this be related to the upgrade issue CRM-12136?

If so, how can I correct the Report menu? Thanks.

[debug_info] => 
    SELECT inst.id, inst.title, inst.report_id, inst.description,  inst.owner_id, v.label, v.grouping, v.name as class_name,
    CASE
      WHEN comp.name IS NOT NULL THEN SUBSTRING(comp.name, 5)
      WHEN v.grouping IS NOT NULL THEN v.grouping
      ELSE 'Contact'
      END as compName
      FROM civicrm_option_group g
      LEFT JOIN civicrm_option_value v
             ON v.option_group_id = g.id AND
                g.name  = 'report_template'
      LEFT JOIN civicrm_report_instance inst
             ON v.value = inst.report_id
      LEFT JOIN civicrm_component comp
             ON v.component_id = comp.id

      WHERE v.is_active = 1  AND v.component_id = 2 
            AND inst.domain_id = 1
      ORDER BY  v.weight ASC, inst.title ASC [nativecode=1267 ** Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=']
[type] => DB_Error
[user_info] => 
    SELECT inst.id, inst.title, inst.report_id, inst.description,  inst.owner_id, v.label, v.grouping, v.name as class_name,
    CASE
      WHEN comp.name IS NOT NULL THEN SUBSTRING(comp.name, 5)
      WHEN v.grouping IS NOT NULL THEN v.grouping
      ELSE 'Contact'
      END as compName
      FROM civicrm_option_group g
      LEFT JOIN civicrm_option_value v
             ON v.option_group_id = g.id AND
                g.name  = 'report_template'
      LEFT JOIN civicrm_report_instance inst
             ON v.value = inst.report_id
      LEFT JOIN civicrm_component comp
             ON v.component_id = comp.id

      WHERE v.is_active = 1  AND v.component_id = 2 
            AND inst.domain_id = 1
      ORDER BY  v.weight ASC, inst.title ASC [nativecode=1267 ** Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=']
[to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="
    SELECT inst.id, inst.title, inst.report_id, inst.description,  inst.owner_id, v.label, v.grouping, v.name as class_name,
    CASE
      WHEN comp.name IS NOT NULL THEN SUBSTRING(comp.name, 5)
      WHEN v.grouping IS NOT NULL THEN v.grouping
      ELSE 'Contact'
      END as compName
      FROM civicrm_option_group g
      LEFT JOIN civicrm_option_value v
             ON v.option_group_id = g.id AND
                g.name  = 'report_template'
      LEFT JOIN civicrm_report_instance inst
             ON v.value = inst.report_id
      LEFT JOIN civicrm_component comp
             ON v.component_id = comp.id

      WHERE v.is_active = 1  AND v.component_id = 2 
            AND inst.domain_id = 1
      ORDER BY  v.weight ASC, inst.title ASC [nativecode=1267 ** Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=']"]

Answer 1

First check the default collation. If it's different it may be causing implicit conversion:

show variables like 'collat%';

If that's not it then note that individual columns can have their own collation. It seems unlikely to be the problem here given that particular reports query, but check the individual column definitions, e.g.

show create table civicrm_report_instance;