Answers.Body: <p>First check the default collation. If it's different it may be causing implicit conversion:</p>
  
  <pre><code>show variables like 'collat%';
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>show create table civicrm_report_instance;
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-02T10:10:46.140Z
Answers.Text: 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.
Answers.UserDisplayName: Jern
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-01-20T13:50:40.060Z
Answers.Text: I noticed that a lot of other tables had columns with specific collation set. Might this be a problem? Should I go through all tables and reset them? If, so is there a better way than doing it manually? (I'll ask this as a separate question ;-))
Answers.UserDisplayName: thoni56
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-01T20:26:34.893Z
Answers.LastActivityDate: 2018-04-01T20:26:34.893Z
Answers.UserDisplayName: Demerit
Body: <p>Using Joomla 3.8.6, CiviCRM 4.7.31, php 7.0.28, mysql 5.6.39</p>
  
  <p>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:</p>
  
  <p>"1267 ** Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='"</p>
  
  <p>The error seems to come from CRM/Report/Page/InstanceList.php.
  See traceback below.</p>
  
  <p>Edit: I now realise that the whole Report menu is not as it should be. It looks like this:</p>
  
  <p><a href="https://i.sstatic.net/G1XmI.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/G1XmI.png" alt="enter image description here"></a></p>
  
  <p>whereas it should look more like this:</p>
  
  <p><a href="https://i.sstatic.net/2OqjG.png" rel="nofollow noreferrer"><img src="https://i.sstatic.net/2OqjG.png" alt="enter image description here"></a></p>
  
  <p>Could this be related to the upgrade issue <a href="https://issues.civicrm.org/jira/browse/CRM-12136" rel="nofollow noreferrer">CRM-12136</a>?</p>
  
  <p>If so, how can I correct the Report menu? Thanks.</p>
  
  <pre><code>[debug_info] =&gt; 
      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] =&gt; DB_Error
  [user_info] =&gt; 
      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] =&gt; [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 '=']"]
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-01-20T13:48:18.390Z
Comments.Text: We had this exact DB_Error also for CiviCRM 5.37.2 on Joomla so this is probably something that gets messed up in some upgrade of Civi or MySQL/MariaDB.
Comments.UserDisplayName: thoni56
Tags: <civireport><database><reports><menu>
Title: Illegal mix of collations
ThreadText: # Illegal mix of collations
  
  (Asked by: username_0 on 2018-03-30)
  
  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 '=']"]
  
  **Tags:** civireport, database, reports, menu
  
  > **Comments:**
  > **username_1**: We had this exact DB_Error also for CiviCRM 5.37.2 on Joomla so this is probably something that gets messed up in some upgrade of Civi or MySQL/MariaDB.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2018-04-01. Score: 2 | Accepted answer
  
  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;
  
  > **Comments:**
  > **username_0**: 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.  
  > **username_1**: I noticed that a lot of other tables had columns with specific collation set. Might this be a problem? Should I go through all tables and reset them? If, so is there a better way than doing it manually? (I'll ask this as a separate question ;-))  
  
  ---
  
  