1

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:

enter image description here

whereas it should look more like this:

enter image description here

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 '=']"]
0

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;
  • 1
    That's really helpful, thank you. I checked all the tables mentioned in the backtrace, and all four of the tables contained columns with collations different from the database default, so I used phpMyAdmin to remove the collations from these columns. This solved the error. – Jern Apr 2 '18 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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